How to format numbers as lakhs and crores in excel google spreadsheet when the number could be negative too

0 votes

I have to format numbers in the Indian formatting system with commas showing up for lakhs(1,00,000) and crores (1,00,00,000).

This answer works for positive numbers

Displaying Lakhs and Crores in Google Sheets

But negative numbers show the commas for millions and billions only. How do I make one format work for both positive and negative?

Oct 31 in Others by Kithuzzz
• 20,660 points
116 views

1 answer to this question.

0 votes

Excel formatting, in my opinion, can only accept a maximum of two requirements, plus a default if those conditions are not met. Conditional formatting may be your only escape from this, in my opinion. I'd format this in a cell using one of the examples from your linked solution.

[$₹][>9999999]##\,##\,##\,##0.00;[$₹][>99999]##\,##\,##0.00;[$₹]##,##0.00

Then in conditional formatting, you would need to have a condition if cell<0 and apply the conditional format of:

[<-9999999][$₹]-##\,##\,##\,##0.00;[<-99999][$₹]-##\,##\,##0.00;[$₹]##,##0.00

If you want to change decimals, symbols, etc you should be able to work it out from the linked solution, and this one.

answered Oct 31 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
0 answers
0 votes
1 answer
0 votes
2 answers

How to get the URL of the current tab in Google Chrome?

Its so simple.... If you want to ...READ MORE

answered Aug 12, 2020 in Others by Steve
• 200 points
1,261 views
0 votes
1 answer

Thousand and million formatting for negative numbers (excel/ googlesheets)

Its not possible. What you could do is ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
56 views
0 votes
1 answer

How to split text values by a delimiter?

The Split function is what you are looking for: =Split(A1, ...READ MORE

answered Oct 11 in Others by narikkadan
• 37,660 points
70 views
0 votes
1 answer

Currency conversion:number to words excel

Try looking for javascript solutions to use ...READ MORE

answered Oct 28 in Others by narikkadan
• 37,660 points
41 views
0 votes
1 answer

How to remove gridlines on select cells in Google Sheets?

The google sheets don't have an exact ...READ MORE

answered Nov 20 in Others by narikkadan
• 37,660 points
20 views
0 votes
1 answer

How to format numbers in excel in crores or lacks

Try this: =TEXT(A1/10000000,"#,### \cr") READ MORE

answered Nov 6 in Others by narikkadan
• 37,660 points
36 views
0 votes
1 answer

I have filtered my Excel data and now I want to number the rows. How do I do that?

Solution Filter your data. Select the cells you want ...READ MORE

answered Nov 14 in Others by narikkadan
• 37,660 points
26 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP