Solved

# Format numbers with different decimal and thousand separator

Posted on 2016-08-31
31 Views
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
``````

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?
0
Question by:Sunsales
• 3
• 3
• 2
• +1

LVL 31

Assisted Solution

Rob Henson earned 250 total points (awarded by participants)
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

Author Comment

I cant get it to work, how do you use that formula if you want a comma as decimal separator?
0

LVL 31

Assisted Solution

Rob Henson earned 250 total points (awarded by participants)
I am away from PC at the minute but can look later
0

LVL 8

Assisted Solution

itjockey earned 100 total points (awarded by participants)
Try this
``````=SUBSTITUTE(TEXT(A1,"0.00"),".",",")
``````
0

LVL 17

Assisted Solution

xtermie earned 150 total points (awarded by participants)
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

LVL 31

Accepted Solution

Rob Henson earned 250 total points (awarded by participants)
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

LVL 17

Expert Comment

Good job Rob, but the author should be aware that these numbers are text now and can not be used in any formulas.
1

Author Comment

Yes and in Sweden the decimal separator is comma and thousand separator is space, and i need them both on the same spreadsheet.
0

LVL 17

Expert Comment

great comments and collaborative solution provided by experts
0

## Featured Post

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

#### Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!