European Number Format Conversion

[acfbobcat]

Excel 2010 Using Windows Operating System


Hi,

I'm an America trying to convert the values I have in 1 spreadsheet to a European number format. The American format seperates thousands with "," and decimals with ".". The European system is the opposite seperating thousand figures with a "." and decimals with a ",".

The values in this spreadsheet must be converted into a CSV file and uploaded into an online European database. The database will only accept the european number formatting.

I have tried several solutions to try and convert the values before uploading.

1. I have tried using a formula such as =--substitute(a1,",","."). Something in the excel settings overrides this and I get the same result in the previous format (6,123,456.00 still shows as 6,123,456.00).

2. I have tried setting a custom number format as #.###.###,##. Again, excel overrides this to show as 6,123,456.00.

3. I have also tried downloading a Euroconvert formula Add-In. No luck.

I've read another solution elsewhere that you could change your system settings to a European format but I don't want to do that because I only want this one spreadsheet to show results in a european format.

Does anyone have any ideas? Thanks,

Drew

 

[Sam_D_Ben]

Let this number 6,123,456.33 in Cell A1, Then in Cell B1 use this formula =TEXT(A1,"#,###,###.##"), will give you 6,123,456.33. Then in Cell C1 use this formula = SUBSTITUTE(B1,",",".") ,will give you 6.123.456.33, Then in Cell D1 use this formula =","&RIGHT(H12,2), will give you ,33. Then again come to Cell C1 Do text to columns or other options to remove the last digits with decimals and then concatenate result with Cell D1 shows the last three digits. This tip will ends up in 6.123.456,33

This is just sample ….go ahead just use your ideas along ..Thank You….
As well please reply if it works....

 

[pgc01]

Hi Drew

In excel 2010 you can manage the separators inside Excel.

To change the separators in all the number values

Options->Advanced, in the first group "Editing options"
- Uncheck Use system separators.
- In the Decimal sparator box write a comma
- In the Thousands separator box write a dot
- Press OK

Go back to the worksheet and check that all the number are now displayed with the new separators.
Save the worksheet as csv and confirm opening it for ex., with the notepad.

P. S. make the test in a copy of the workbook

 

[acfbobcat]

Let this number 6,123,456.33 in Cell A1, Then in Cell B1 use this formula =TEXT(A1,"#,###,###.##"), will give you 6,123,456.33. Then in Cell C1 use this formula = SUBSTITUTE(B1,",",".") ,will give you 6.123.456.33, Then in Cell D1 use this formula =","&RIGHT(H12,2), will give you ,33. Then again come to Cell C1 Do text to columns or other options to remove the last digits with decimals and then concatenate result with Cell D1 shows the last three digits. This tip will ends up in 6.123.456,33

This is just sample ….go ahead just use your ideas along ..Thank You….
As well please reply if it works....

WORKS!! Thank you so much. Very frustrating experience but you've resolved it for me.

Also thank you PGC01 - This works as well