Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Format numbers with different decimal and thousand separator

Posted on 2016-08-31
9
Medium Priority
?
56 Views
Last Modified: 2016-09-23
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?
0
Comment
Question by:Sunsales
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 1000 total points (awarded by participants)
ID: 41778412
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

by:Sunsales
ID: 41778474
I cant get it to work, how do you use that formula if you want a comma as decimal separator?
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 1000 total points (awarded by participants)
ID: 41778507
I am away from PC at the minute but can look later
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Assisted Solution

by:Naresh Patel
Naresh Patel earned 400 total points (awarded by participants)
ID: 41778538
Try this
=SUBSTITUTE(TEXT(A1,"0.00"),".",",")

Open in new window

0
 
LVL 18

Assisted Solution

by:xtermie
xtermie earned 600 total points (awarded by participants)
ID: 41779356
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 33

Accepted Solution

by:
Rob Henson earned 1000 total points (awarded by participants)
ID: 41779433
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 18

Expert Comment

by:xtermie
ID: 41779669
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

by:Sunsales
ID: 41782977
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 18

Expert Comment

by:xtermie
ID: 41812163
great comments and collaborative solution provided by experts
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question