Solved: Text Import - cell format issue

[Glaswegian]

Hi

I'm using Chip Pearsons' ImportBigTextFiles routine to import a file with around 100K lines. The import works fine and I format some columns, but I'm having a bit of an issue with one particular column.

The data is in this format

750.93
0000000009821.22-
24.50
0000000000018.50-

These values are pound (£) amounts. The numbers with a minus sign to the right are negative pound values. They should simply be shown as
-9821.22
-18.50

However, even when I format the columns (H and I) in the import code, these values do not change. The minus sign seems to be causing the problem.

I'm not sure what the best solution is - a formula, or perhaps something I need to add to my code?

This is the code I've added to the end of Chip's routine

For Each WS In ThisWorkbook.Worksheets
With WS
.Columns.AutoFit
.Columns("N").NumberFormat = "dd/mm/yyyy;@"
.Columns("L").NumberFormat = "0"
.Columns("AP").NumberFormat = "0"
.Columns("AO").NumberFormat = "0"
.Columns("H:I").NumberFormat = "0.00;[Red]0.00"
End With
Next WS


Thanks for any help.

[mdmackillop]

Something like

Sub FixMinus()
Dim Cel As Range
For Each Cel In Selection
If Right(Cel, 1) = "-" Then Cel = -Val(Cel)
Next
End Sub

[Glaswegian]

Hi Malcolm

Hope you are well.

Thanks - that's just perfect.