Export number formatting

[Sweedler]

Hello

I have an export where a column of "numbers" shows up like this: 105 895,9
According to excel the column is formatted as "accounting", but I can not use the number in any way .. ie Excel thinks it is text, or some kind of non-number.
I need a way to transform this into a usable number, ideally looking like this: 105895.9

The transformation needs to happen in VBA, as it needs to be a part of a string of things that a macro does.

 

[Georgiboy]

You could try replacing the space and , as below:

Sub test()
    Range("A:A").Replace " ", ""
    Range("A:A").Replace ",", "."
End Sub

 

[Sweedler]

You could try replacing the space and , as below:

Sub test()
    Range("A:A").Replace " ", ""
    Range("A:A").Replace ",", "."
End Sub

I tried that, it did not work. I had the comma replaced with a period in already and working. But the space replacement did not work. And it is still not seen as a true number.

 

[Georgiboy]

Try copying the space from one of the numbers and replace the space in the code with the one you copied. It could be that the space is not a true space. It could also be that the space is part of the formatting?

 

[Sweedler]

Try copying the space from one of the numbers and replace the space in the code with the one you copied. It could be that the space is not a true space. It could also be that the space is part of the formatting?

That sorted it out. Hadn't thought of that one. Thank you so much.

 

[Georgiboy]

You're welcome, thanks for the feedback.