Replacing comma with nothing replaces both dots and commas??

[rthell]

Hello,

I have a problem which looks like a bug to me but it must've been experienced before. In Sweden we use commas as decimal separator. In my sheet I have a column which has numbers such as

'14.900,00 - [fourteen thousand nine hundred] - appears as 14.900,00
29,15 - [twenty-nine point fifteen] - appears as 29,15

I want to make the numbers consistent. So I'm using a simple replace code to replace all ".". However, when doing this, both commas and dots are replaced! This turns
'14.900 into 14 900
29,15 into 2 915,00

wtf!

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Are the 'numbers' that you're trying to replace real numbers or just text that happens to look like a number. Test with =ISNUMBER(A1)

If they are numbers have you tried temporarily changing your regional settings so that commas are the thousands separator and the dot the decimal. Then run your macro.

[rthell]

Hi,
No the 14.900 is not a number (because of the dot). If I could just replace the dot with [nothing], it would turn into a number (14 900,00). When using CTRL+F/replace, it works like a wonder. When using VBA, it replaces both dots and commas.

[rthell]

bump. anyone?

[xlbiznes]

Hi,

this line

Please Login or Register  to view this content.

should be like this

Please Login or Register  to view this content.