Quick conversion of Excel numbering syntax from 50 000,00 to 50,000.00

numbers coming in with Nordic formatting i.e. 50.000,00 - how to quickly change that to 50,000.00 format?  User using Office 365..
LVL 1
agwalshAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Matt NicholasBusiness AnalystCommented:
Assuming your Nordic values are in column A2

=SUBSTITUTE(SUBSTITUTE(A2,".",","),",","",2)

Copy and paste the result as VALUES then convert to desired format
aikimarkCommented:
If importing this data from an external file, you must first import the column as text.
Rob HensonFinance AnalystCommented:
Assuming your local settings recognise the period as the decimal place rather than a '000 separator, ie the other way round to the Nordic values, the value of 50.000,00 will be seen in excel as text anyway but the placing of the decimal and the comma will confuse it when trying to convert to a number if using VALUE function or other methods for converting to value, eg multiply by 1.

Matt - your SUBSTITUTE is incorrect, you are missing the part to convert the comma to a decimal, you are converting it to blank. Also it will still be a text string.

It should be:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,".",","),",",".",2))

Copy and paste as values as suggested and then format as required.

The above SUBSTITUTE function will also only work on values up to 999,999 (in Nordic 999.999) ie with only one period as a separator.
Rob HensonFinance AnalystCommented:
Try this instead:

=IF(ISNUMBER(A2),A2*1000,IF(ISERROR(FIND(",",A2,1)),VALUE(SUBSTITUTE(A2,".","")),SUBSTITUTE(LEFT(A2,FIND(",",A2,1)-1),".","")+RIGHT(A2,LEN(A2)-FIND(",",A2,1))/100))

The ISNUMBER function checks if the string imported is already a number, eg 90.000 may be incorrectly recognised as 90. If so it will just multiply by 1000.

The ISERROR checks the FIND function to check for a comma in the string, being in Nordic format it will either have only one or none at all.

If there is no comma, the SUBSTITUTE removes all periods by replacing with blank, leaving a string of numbers; they are still text so VALUE converts to a number.

If there is a comma, the SUBSTITUTE function removes all periods but only in the left part of the string until it finds the comma; the RIGHT function then takes the remaining digits after the comma and divides by 100, (I decided not to assume only 2 digits after the comma); this last part is then added to the converted left part of the string. This option does not need the VALUE function because adding the two sections together will force Excel to re-evaluate the string as a number.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
No confirmation from author but solution correctly covered all options
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.