Hello,

First can you confirm that I am getting 12 months support starting now due to completion of the Excel VBB course I attended last Friday with Jonathan Stevens?

Second my question is this: I have received a file from Germany which when I open it up in Excel shows where we should have decimal points eg 25.52 is instead replaced by commas eg 25,52

I have made sure my language and regional settings are UK and correct, and also played around with the Tools/Options to choose the decimal place or comma separator. But the commas remain. How do I change the comma separators to decimal points without mainly going into each cell.

Have tried to use the format painter as well to quicken the process when doing it manaully, but for some reason it does not work.

This is not a VBA question, but posted it here anywhere to make sure it is related to my course last Friday.

Thanks in advance,
Eric

Hi Eric

This solution might sound very simple but did you try Find replace?

Once you've replaced all your , (commas) to . (full stops) they will be formatted as general. You can format those cells to currency with two decimal places.

Hope this helps!!

I have tried usuing the find/replace function. Finding "," and replacing with "." However in the replace tab I can only look in "Formulas". So that whenever I try to replace all I get an error saying "The formula you typed contains an error" as it looks at the commas in my formulas on the same worksheet.

Try TOOLS > OPTIONS > INTERNATIONAL tab.
In the first part, tick or untick the box that says USE SYSTEM SEPARATORS.

this will give you access to specify what you want to use for separators.

Tried the tools/options/international etc. to no avail.

The number stays at eg 31,28 instead of 31.28

May need to put this down to a corrupt file.

Could be the numbers are stored as text?

PS. yes, your 12 months support starts from when you post your first question.

Dear Eric

When I use Find Replace it seems to be working few things I want to clarify:

I hope you are not using the inverted commas (" "). Please only use the , and . in the find and replace respectively!!

Also may be try to select the group of cells which has got ,(commas) and then use this!!

Also please make sure that you don't put a tick on either the Match case or Match entire cell content

Even if the cells are text for any reason because of, (commas) they automatically should be converted to number.

If it still doesn't work then as long as the data is not sensitive you can send a copy of the file or part of the file to the following address as attachment:

forum AT microsofttraining.net
(please replace the AT with @ symbol)

Also once you have submitted the relevant file please copy and paste that URL (Web Address) from the address bar and post it on the Forum so that we can have a look at it and hoipefully resolve this!!

Thank you for your time!!