Format numbers with different decimal and thousand separator

I'm creating an international spreadsheet with different currency's. On the same sheet i need to display (and calculate):

Invoice 1: 300 000,14 Sek
Invoice 2: 320,000.19 Dollar

Open in new window


Sweden and USA have different decimal and thousand separator. How do i format cells so i can control what decimal and thousand separator is used in what cell?
Paer ToernellAsked:
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.

Rob HensonFinance AnalystCommented:
You could use conditional formatting based on a currency indicator.

Or use TEXT function to display required format and hide number column:

=TEXT (A1,"0.00")

Change the 0.00 section to a lookup on currency indicator to get format.
0
Paer ToernellAuthor Commented:
I cant get it to work, how do you use that formula if you want a comma as decimal separator?
0
Rob HensonFinance AnalystCommented:
I am away from PC at the minute but can look later
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Naresh PatelFinancial AdviserCommented:
Try this
=SUBSTITUTE(TEXT(A1,"0.00"),".",",")

Open in new window

0
xtermieCommented:
That would make your numbers be treated as text though (so any calculations will not result in a valid result).  Please note that you can not have two different decimal separators as this not an Office setting. but is a setting in Windows under "Regional and Language Options" in the "Control Panel".

However, if you use the custom formats in the attached sample workbook, depending on who opens the spreadsheet (i.e. in Sweden with European settings or in US) the numbers will be displayed properly.

Another way around it, would be to have the sum as a regular number, hide that row, and then SHOW the Invoice sums using the custom formats I suggest for the one or with the substitute formulas Rob and itjocley suggest (but you wont be able to use that "text number" for further calculations.
Example.xlsx
0
Rob HensonFinance AnalystCommented:
I have used xtermie's uploaded file as a sample and have used the following TEXT function to do as required:

=CHOOSE(MIN(MATCH($C2,$H$2:$H$4,0),2),TEXT(INT($B2),"# ###")&","&TEXT(($B2-INT($B2))*100,"00"),TEXT(B2,"0,000.00"))&" "&C2

C2 = Currency indicator (SEK, USD, GBP etc)
H2:H4 = list of currency indicators with SEK at top of list
B2 = value to be converted

Col B           Col C
300000.14  SEK       becomes 300 000,14 SEK
300000.14  USD      becomes 300,000.14 USD

Columns B and C can be hidden if required.

If you have more than one currency that requires non-standard format let me know and I will adjust the formula.

See attached.

Thanks
Rob
Example.xlsx
3

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
xtermieCommented:
Good job Rob, but the author should be aware that these numbers are text now and can not be used in any formulas.
1
Paer ToernellAuthor Commented:
Yes and in Sweden the decimal separator is comma and thousand separator is space, and i need them both on the same spreadsheet.
0
xtermieCommented:
great comments and collaborative solution provided by experts
0
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.