European formatting number error

[Mr2017]

Hi

I have a spreadsheet where data for three columns from a certain row is in "European" format.

So, $5.50 (fiver dollars and fifty cents), is down as $5,50 i.e with a comma instead of a full stop.

This is causing errors with some calculations, as Excel can't handle 5,50/100, for example.

I've tried to change it to number format (by right-clicking then choosing number format and two decimals) but that hasn't worked! The format was a custom format before that.

Has anyone else experienced this kind of problem before? And if so, do you know of a solution?

Thanks in advance!

 

[pgc01]

Hi

Delete the $ with find/replace and then select 1 column and try Text-to-columns. In the third panel you have an option to specify the separators when recognizing numbers.

Another option is just to use replace. Select the columns and
Replace $ with nothing
Replace "." with nothing (the dot is the thousands separator in that format)
Replace "," with "."

 

[Mr2017]

Thanks pgc01.

I used a variation of your second suggestion which solved the problem for me (=NUMBERVALUE(BF4,",",".")

Thanks again for responding to the query!