Knowing how to convert numbers from US to European isn’t something that I would usually do. Even though I live in the UK, I would still use the US currency number format. Let me explain.
I had a tutorial out there that converted numbers from the European format: €34.456,23 to UK or US format £34,456.23.
The thing to notice here is that the dots and the commas in the different formats are in different places. It the European format the thousands separator is a period, instead of a comma. This is different to the UK and US format which places the thousands separator as a comma and the decimal as a full stop.
Just before we continue I’d like to take the opportunity to mention that our partners Computer Tutoring offer a fantastic Microsoft Excel 365 Intermediate training course in London which can also be delivered online. Organisations have found answers to specific problems and situations they face regularly with Excel by attending this course. Computer Tutoring also has also been rated 4.8 stars on Trust Pilot.
Now getting back to the reason you’re here. There was a question on a video on a YouTube channel that I run and it goes like this..
“Would it be possible to convert the numbers from the US format to the European format.” The fact that the video was using a rather basic text to columns method of converting the numbers from European to US format, I thought that the answer would be simple.
However, I found that there was a little more involved. So I looked at an earlier video I created on converting European currency numbers to UK numbers. This video used a formula and it got me thinking.
You see the trouble is that because my Exce is set up to use the UK format. Because I live in the UK that’s the format that I want to use. So what I would have to do is to convert the number into text. Then use the substitute function to replace the commas and periods.
Formula to Convert European Numbers to US
Let’s get straight to the formula! If you have a spreadsheet set up in the following way:
The formula would read as follows:
=SUBSTITUTE(SUBSTITUTE(TEXT(A1,”#,##0.00″),”,”,”.”,1),”.”,”,”,2)
So here’s how the formula works.
First, I use the TEXT function to convert the number into text using the hashtags and the 0 to correctly format the numbers.
Next, I use a substitute function to change the first comma to a full stop.
=SUBSTITUTE(SUBSTITUTE(TEXT(A1,”#,##0.00″),”,”,”.”,1),”.”,”,”,2)
After that, I wrap the above Substitute function in another substitute function to change the full stop to a comma.
=SUBSTITUTE(SUBSTITUTE(TEXT(A1,”#,##0.00″),”,”,”.”,1),”.”,”,”,2)
There you have it! One thing to note is the instance number argument in the substitute function. The nested substitute function uses the value of 1, while the outer substitute function uses the value of 2. You need to do this so that one function doesn’t counter act the other. If you don’t the the outer substitute function will change the first full stop back to a comma.
Hopefully, this convert US numbers to European numbers guide has helped you.
Here is the Excel file should you want to check the result of the conversion formula yourself.
Again this is just my idea so if you have a better way of doing this then please comment below.