Adjusting the separator

Question

Adjusting the separator

I am trying to create a custom format to display Indian currency. I would like to add the separator after three, five and seven digits left of the decimal point.

1) Numbers less than 100000 (a hundred thousand) should be displayed as ##,###.##
2) Numbers less than 10000000 (ten million) and greater than 99999, should be displayed as ##,##,###.##

I tried to create a custom format, but cannot seem to find a way to move the separator. As far as I can see, the separator is always at positions three, six, nine digits left of the decimal point. I used the search terms "move separator" to search for posts, but came up with questions not directly related to the above question. The help topics also do not seem to indicate whether moving the separator is possible !!

Defining this format in excel is possible. Does numbers offer a way to do this ? Can someone point me in the right direction ?

Thank you.

Mac OS X (10.6.3)

Posted on Apr 7, 2010 4:11 PM

Reply

Answer 1

Badunit

Level 7

31,202 points

Apr 9, 2010 5:33 AM in response to Community User

I'm sorry I don't have a better answer. I am curious why you want your numbers to have the commas in non-standard places.

You can apply and create formats for cells with formulas. I suspect what you are doing is double clicking on the cell as if you are going to edit the formula. While in that state, the cell format is grayed out. Try a single click to select the cell.

Reply

Answer 2

Barry

Level 9

60,204 points

Apr 7, 2010 11:58 PM in response to Community User

In Numbers, "separators" is short for "thousands separators" (and "decimal separator). Both types of separators occupy fixed positions—the decimal separator between the ones and the tenths places, and the thousands separators at positions of three, six, nine, etc. places to left and right of the decimal.

If you treat the numbers as text, it is possible to create a text string with separators where you want them. Here's the start of a formula that will do what you want.

=CHOOSE(LEN(B),"0.0"&B,"0."&B,LEFT(B,1)&"."&RIGHT(B,2),LEFT(B,2)&"."&RIGHT(B,2), "over")

This version will work with up to four digits in the number entered into column B (and return "over" if five digits are entered). To extend the formula beyond four digits (where more than one separator is needed), it will be necessary to add the MID() function. For now, I'll leave that as an exercise for the reader. With luck, I'll be able to get back to it late tomorrow.

Regards,
Barry

Reply

Answer 3

Badunit

Level 7

31,202 points

Apr 8, 2010 10:42 AM in response to Community User

You can create a format with the commas where you want. It will look like this:
####,##,###.00

Choose "custom" for the format.
Add a .## format (decimal places) to the format that is there.
Choose to show trailing zeros.
Click the + and add a format for "if greater than 99999"
Drag two #,### blocks to it and one .##
Choose to not show the separators in any of the #,### blocks
Choose to show trailing zeros in the decimal format block
Choose two digits for the middle #### and three for the rightmost ####
Type in the commas between the ### blocks.
It should look like ####,##,###.00

You did not say what to do for numbers greater than "99,99,999". The format created above has no additional commas so it will continue on as "999,99,999", "9999,99,999", etc. If you want more commas, you'll have to add a similar format for "if greater than 9999999".

Message was edited by: Badunit

Reply

Answer 4

Badunit

Level 7

31,202 points

Apr 9, 2010 7:45 AM in response to Community User

I see, as explained in http://en.wikipedia.org/wiki/Indiannumberingsystem

If you have your system preferences set so the region is India, you will get the separators in the places you desire. Unfortunately, Numbers (or any particular document) can't be set to something different than what is in your Language & Text (International) preferences, as far as I know.

Message was edited by: Badunit

Reply

Answer 5

KOENIG Yvan

Level 8

41,845 points

Apr 9, 2010 8:50 AM in response to Community User

I apologize but I don't know which language must be set at top of the System's list to get MacOs X running for India.
I assumed that I may find Hindi in the list but I didn't.
What is displayed at the bottom of the formats pane when the System is set for India ?

User uploaded file

Or, more meaningful, how are file sizes displayed in the infos window ?
If they don't match the Indian rules, it's time to send a report to Apple asking them to take care of your local rules.
If the system take care of them, Numbers will do the same.

Yvan KOENIG (VALLAURIS, France) vendredi 9 avril 2010 17:50:02

Reply

Answer 6

Badunit

Level 7

31,202 points

Apr 9, 2010 9:05 AM in response to KOENIG Yvan

In English, the format is "India". It is the seventh one on the list for me.

AND, I may have a solution to the problem:

One thing I found very interesting, but which may not be a robust solution (because it is undocumented) is to set the region preference to India, go into Numbers and format the cells that need the separators per Indian standards as Number with separators and decimals, then change the region back to whatever you usually use. The Indian formatted cells keep the India format even after saving and reopening.

Reply

Answer 7

KOENIG Yvan

Level 8

41,845 points

Apr 7, 2010 10:44 PM in response to Community User

No need for a custom format.
What you ask for is a standard format.

User uploaded file

Yvan KOENIG (VALLAURIS, France) jeudi 8 avril 2010 07:44:12

Reply

Answer 8

KOENIG Yvan

Level 8

41,845 points

Apr 8, 2010 11:34 AM in response to KOENIG Yvan

Oops

It seems that I must change my glasses 😟

I missed the fact that you need two digits between separators, not three.

Yvan KOENIG (VALLAURIS, France) jeudi 8 avril 2010 20:34:37

Reply

Answer 9

Apr 8, 2010 3:23 PM in response to Badunit

Thanks all for your input. I followed Badunit's suggestions, and got it working. However, I keep running into some limitations -

Firstly, I need several conditions
1) no condition met
2) > 99,999
3) < -99,999 (same issue for negative numbers, just need a new conditional entry)
4) > 99,99,999
5) < -99,99,999

I would like add one more level but I only see a way to add upto three conditions. As soon as I add the third condition, the + sign (to add new conditions) gets grayed out. Is this a limitation which numbers puts ?

I worked around this by creating a custom formula exclusively for larger numbers. It does not seem ideal, but works as a temporary solution. With this, the problem I encountered is for cells which are formulas, where the number is generated based on other numbers. One issue is that the value could fluctuate between the larger format and smaller format. The other is that I do not see the way to change the cell format for formulas. Clicking on the cell and then clicking on the "view inspector" does not allow me to edit the format. The cell format shows up as a blank which cannot be edited.

I like this solution, since it allows me to retain the element as a number rather than text. I have formulas which operate on these numbers, and it is very convenient to be able to see them with the separator in the correct position. However, it would be great if I could add up to 6 conditions in a single format. That should solve the problem since most numbers I am dealing with, will fall under those conditions.

Reply

Answer 10

Apr 9, 2010 7:26 AM in response to Badunit

I did get the formula cell working too. I need separators in non-standard locations because Indian currency follows those units. A hundred thousand is more commonly called as a lakh. Ten million is more commonly called as a crore.

Thanks though, I am going to stick with this till I find a better solution.

Reply