Macro to convert Data to true values

[howard]

I have values in Col L-see data below that was imported from a PDF file uusing a program to convert these into Excel

I need a macro that will convert these values into proper values that can be usedc for calculations for eg the value in L16 56.244,40- should be -56,244.40. The value in L21 38.361,00 should read as 38,361.00

Your assistance in this regard will be most appreciated

 

[JackDanIce]

Instead of a macro, you could select the whole column and

1) Replace every '.' with nothing (so 56.410,00 becomes 56410,00) then
2) Replace every ',' with a '.' (so 56410,00 becomes 56410.00)

If you want a macro then:

Sub Convert()
With Range("L:L")
    .Replace what:=".", replacement:=""
    .Replace what:=",", replacement:="."
End With
End Sub

 

[howard]

Thanks for the help. The values convert correctly, except the negatives, which is shown with a minus after the value for eg 56254.25-. I need the minus sign to precede the value for eg -56254.25

It would be appreciated if you would add this to your code

Thanks

Howard

 

[ChrisUK]

I've added the numbers to the first two rows of a sheet and run the macro against them, you might need to tweak this code run blitz a whole column of data

Sub dataconvert()

For x = 1 To 2
MyStr$ = Cells(x, 1)
MyStr$ = Replace(MyStr$, ".", "@")
MyStr$ = Replace(MyStr$, ",", ".")
MyStr$ = Replace(MyStr$, "@", ",")
If InStr(1, MyStr$, "-") Then
MyStr$ = Replace(MyStr$, "-", "")
Cells(x, 1) = MyStr$ * -1
Else
Cells(x, 1) = MyStr$
End If
Next

End Sub

 

[howard]

Hi Chris

Thanks for the code, much appreciated

Regards

Howard