in excel a particular no e.g. 3600000 can be change by using 1000
seprater i,e, 3,600,000,
i am trying to convert this no to 36,00,000. i have more than 2000 no
to be convert.

If I understand what you want correctly, select all the cells you want to
convert (you can select cells that have nothing in them as well if they will
or might get entries in the future also), right click the cell(s) and select
"Format Cells" from the popup menu that appears, then select Number from the
list on the left, change the number of "Decimal Places" field to 0 (I guess,
from the the example values you posted) and put a check mark in the "Use
1000 Separator (,)" check box, then click OK.

Rick Rothstein (MVP - Excel)


in excel a particular no e.g. 3600000 can be change by
using 1000 seprater i,e, 3,600,000, i am trying to convert
this no to 36,00,000. i have more than 2000 no to be convert.

On Mar 7, 10:01*pm, yogi wrote:
in excel a particular no e.g. 3600000 can be change by
using 1000 seprater i,e, 3,600,000, i am trying to convert
this no to 36,00,000. i have more than 2000 no to be convert.

As I understand it, you want to change the __appearance__ (format) of
the number, not convert it per se. You want numbers to appear with
the grouping 12,34,56,789 instead of 123,446,789.

If your numbers will be 7 digits or less, you can use the following
Custom format: [<1000]0;[<100000]0\,000;0\,00\,000

Unfortunately, such "condition value" custom formats are limited to 2
conditions, as shown. At least, that is the case in XL2003. I don't
know about XL2007 and later.

Note: In the Regional and Language Options control panel, there is a
custom Digit Grouping 12,34,56,789. But I was not able to get Excel
to group based on the regional setting. Nevertheless, there might be
some way that I have not thought of. Food for thought.