Written by Jane Hames
Excel number formatting for when working with users with a different international format
As often happens during Excel training, recently a question came up that I had not been asked before. The way people use Excel is so personal to their own style, the type of work they do and what their organisation does, so I love it when a question is asked that enables me to do some troubleshooting and experiment with new ways of working.
The question this time, was from an Excel user who works in the UK and – as we do here – uses a comma to separate thousands and a dot as the decimal place. The user was receiving workbooks from colleagues elsewhere in Europe where they were using a dot to separate the thousands and a comma instead of a dot for decimals. So, as you can imagine the user was getting very frustrated and even had to resort to re-typing the numbers!
My challenge was to solve this problem, and this what I did:
- This is what I was starting off with:
- Select the cells containing the numbers with the commas and dots in the wrong place.
- From the Home Tab, click on the Find & Select button
- Choose Replace and type a dot in the Find what box and leave the Replace with box blank.
- Click on Replace All to replace all dots in the selected cells with nothing.
- From the Find and Replace box, now type a comma in the Find what box and type a dot in the Replace with box.
- Click on Replace All to replace all commas in the selected cells with a dot.
- Click on the close button.
- Now format the selected cells with Comma Style formatting by clicking on the Comma Style button in the Number group of the Home Tab.
