agwalsh
asked on
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..
If importing this data from an external file, you must first import the column as text.
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(SUBSTITU
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.
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(SUBSTITU
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No confirmation from author but solution correctly covered all options
=SUBSTITUTE(SUBSTITUTE(A2,
Copy and paste the result as VALUES then convert to desired format