0

Friends I want to put comma automatically into the digits like this- 10,00,000 Or 1,00,000

For this I want to use a formula. I also know that I can do this by format cell>number>use 1000 separator (,). But I need a formula for this task because we can use comma like eastern (especially Indian) in M S Excel 2010 and its further versions but I have M S Excel 2007 in windows XP which does not allow to use comma like eastern (especially Indian). It puts comma like this- 100,000,000 or 1,000,000 so I searched on net for a formula I found this formula

=REPLACE(A1,FIND(" ",A1),0,",")

If this formula is in b1 and any name is written in a1 cell like joe desuja then it puts comma after first name and the result (in b1) is this- joe, desuja. I want this kind of formula which should work with the numeric digits. Kindly help me. Thanks a lot.

  • excel 2003 should support formatting cells with a custom notation. That is definitely the preferred method. It probably fails because the locale is not recognised in XP, but a custom formatting, where you specify the notation yourself should still work. – LPChip Dec 2 '15 at 13:52
2

There are a couple of ways to take care of this - through cell formatting and through the TEXT conversion formula. Cell formatting allows more flexibility for working with the number after it is converted to the format, but it might not be suitable in every case.

For your example as a formula, try this:

=TEXT(A1,"[<-9999999](##\,##\,##\,##0);[<-99999](##\,##\,##0);##,##0")

To do it with formatting, use Format Cells... from the destination cells' context menu, change the Number category to Custom, and put:

[<-9999999](##\,##\,##\,##0);[<-99999](##\,##\,##0);##,##0

in the Type field.

You may need to adjust the formatting string if you need to support decimals.

This site has more examples which may help you: https://exertia.wordpress.com/2006/04/23/displaying-lakhs-and-crores-in-excel/

0

You can set your default currency and number format to display like Indian style by changing global settings in Control Panel.

In XP, go to Control Panel -> Regional & Language option. In the dialogue box click customize. On the Number and Currency tab, in Digit grouping option, select appropriate grouping from Drop down.

Reopen excel and check by entering number in a cell.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.