Excel - change "," to "."

[Mollethewizard]

If I have a Range ("A1:A10&quot with values with a comma separator (,) in the numbers for example (123,56).

Is there a way to change the commas (,) to dots (.) instead?

Have a nice day
Mollethewizard

 

[Stretchwickster]

Try this:

  Range("A1:A10").Replace What:=",", Replacement:="."

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Mollethewizard]

It don't work. Any other suggestions?

Have a nice day
Mollethewizard

 

[Stretchwickster]

It works for me! Is it producing an error? Or it's just not working? Also, can you give me more information on where/how you are executing the code?

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Stretchwickster]

Try doing it manually to see if it works. Select the range, press <CTRL-H> to access the Replace dialog then put the &quot;,&quot; in the Find What box and the &quot;.&quot; in the Replace With box (ensure that &quot;Find entire cells only&quot; is unchecked). Then click &quot;Replace All&quot; and see what happens. This is equivalent to the code I specified.

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Mollethewizard]

Forgot that one can record a macro to test.

The code comes out like this:

Range(&quot;A1:A10&quot.Select
Selection.Replace What:=&quot;,&quot;, Replacement:=&quot;.&quot;, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Thanks for directing me in the right direction!

Have a nice day
Mollethewizard

 

[bryanbayfield]

I'm guessing that this is a decimal point formatting problem.

Does examing Format> Cells> Customise not help?

Perhaps it's a regional settings prob - ie requires a fix ouitside of Excel.

 

[TonyJollans]

Hi Mollethewizard,

I don't think you can do it in any straightforward way just for a range of cells. Excel (globally) uses the setting from the Windows Regional Settings (in the Control Panel).

For purely display purposes you could do several things with the string as text, but it would no longer be a number.

Enjoy,
Tony

 

[Mollethewizard]

Why would I change commas to dots in the first place?

I live in Sweden and we have comma as a separator in numbers (12,356) where many other countries have dots.

One of our systems “gets fed” by reading tab-separated text-files from Excel. And the system does not accept commas as a separator.

I must say that I’ve been very impressed by the speed for getting an answer to a question!

Thanks all!

Mollethewizard