How could the number, '102845678' be displayed as '10,28,45,678'?

introduce this custom format

[=100000]##\,##\,##0.00;##,##0.00 indian style with 2 decimal

[=100000]##\,##\,##0;##,##0 indian style with no decimal


this is for commas after lakhs and thousands

modify to make the comma after crore also

this was given in the newsgroup. I am sorry I forgot to note down the name
of the MVP

vnranand wrote in message
...
How could the number, '102845678' be displayed as '10,28,45,678'?

V,

I don't think you can, at least if the comma is the thousands separator
("digit grouping symbol") called for in Windows Regional Settings. If you
can use another character, such as a hyphen, try Format - Cells - Number
tab - Custom, and put in something like:
##-##-##-###

Failing that, you could use a formula in another cell (another column,
perhaps). Post back for that. Or a worksheet change event macro could
convert the number to a string like you want.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"vnranand" wrote in message
...
How could the number, '102845678' be displayed as '10,28,45,678'?

In short, you can't. Why? Because '10,28,45,678'is not a number. However,
were you to start the cell entry with an apostrophe "'" then you can
achieve the effect of 10,28,45,678. Excel will not be able to perform
numerical calculations on this cell.


Hth,
Oli

--
Message posted via http://www.officekb.com

Hi, All;
Curious, I tried this. Custom format [=100000]##\,##\,##0;##,##0 gave me
1028,45,678 instead of 10,28,45,678. AutoSum worked on it, though. Adding two
of them gave me 2056,91,356. Excel treats these as numbers. It is wise to
avoid provincial assumptions about other peoples' number formats, and refrain
from blanket negatives about Excel. ;)
Regards,
Ian.

"Oliver Ferns via OfficeKB.com" wrote:

In short, you can't. Why? Because '10,28,45,678'is not a number. However,
were you to start the cell entry with an apostrophe "'" then you can
achieve the effect of 10,28,45,678. Excel will not be able to perform
numerical calculations on this cell.


Hth,
Oli

--
Message posted via http://www.officekb.com

as I said in the message this is for lakhs(an indian system of arithmetic).
you can MODIFY that for crores(the next level to lakh in indian system) ie.
10 crores 28 lakhs 45thousand 678.


IanRoy wrote in message
...
Hi, All;
Curious, I tried this. Custom format [=100000]##\,##\,##0;##,##0 gave me
1028,45,678 instead of 10,28,45,678. AutoSum worked on it, though. Adding
two
of them gave me 2056,91,356. Excel treats these as numbers. It is wise to
avoid provincial assumptions about other peoples' number formats, and
refrain
from blanket negatives about Excel. ;)
Regards,
Ian.

"Oliver Ferns via OfficeKB.com" wrote:

In short, you can't. Why? Because '10,28,45,678'is not a number.
However,
were you to start the cell entry with an apostrophe "'" then you can
achieve the effect of 10,28,45,678. Excel will not be able to perform
numerical calculations on this cell.


Hth,
Oli

--
Message posted via http://www.officekb.com

Reg: how to format numbers in indian style (in crores) instead of
default american style (in millions).

do a Ctrl+1 and copy-paste the following text into the " Type " box:

if you want two decimal places:
[=10000000]##\,##\,##\,##0.00;[=100000]##\,##\,##0.00;##,##0.00

If you don't want decimal places. use this
[=10000000]##\,##\,##\,##0;[=100000]##\,##\,##0;##,##0


--
bkurve
------------------------------------------------------------------------
bkurve's Profile: http://www.excelforum.com/member.php...o&userid=34516
View this thread: http://www.excelforum.com/showthread...hreadid=344315