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
Who is Participating?

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.

Assuming your Nordic values are in column A2

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

Copy and paste the result as VALUES then convert to desired format
Commented:
If importing this data from an external file, you must first import the column as text.
Finance 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.
Finance AnalystCommented:

=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