Shucks. I was surprised to see, on re-opening the sample file I posted, that the number format had changed and the numebrs were displayed incorrectly. The problem I had with the XLS file also applies to the XLSX file equally.
You can ignore my example attachment. Although I was able to use this format in Excel 2010, upon saving a file and reopening it, the format was changed. I can't speak for other versions. The most reliable method might, for better or worse, be to round to Lakhs using the ROUND function: =ROUND(A1/100000,0) Then, you can use the following custom number format: [<-100000]-"Rs."#\,##\,##0;[<100000]"Rs."#,##0;"Rs."#\,##\,##0 I did test saving and re-opening a file with this method, and there are no problems. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Asa Rossoff Sent: Tuesday, April 10, 2012 11:19 PM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Re: Display numbers in Lakhs I think it should work to give the right comma separation, but it does not round to Lakhs. For Lakhs plus Indian comma separation, try this one (tested in Excel 2010): [<-10000000000]-"Rs."#\,##\,##0,,,%%;[<10000000000]"Rs."#,##0,,,%%;"Rs."#\,# #\,##0,,,%% This one also supports negative numbers. Amounts in the range -Rs.99,99,999 to Rs.99,99,999 Lakhs are displayed correctly. It uses the trailing commas and percent symbols to fool Excel into rounding to Lakhs correctly. See http://chandoo.org/wp/2012/01/31/custom-number-formats-multiply-divide-by-an y-power-of-10/ for how this works. If used as-is you will see %% appear after the amounts. To correct for that, you need to: 1. Format the cells with word wrap turned. 2. Press Ctrl-J just before each of the three %% instances to insert a line feed (line break). Easiest if you type the format in without them, then work right to left pressing Ctrl-J at those spots. 3. If you ever try to edit the number format, you will probably find that Excel presents it to you incorrectly to edit, and it will mess up the format if you don't compensate for it. Best to save a copy of the correct format somewhere where you can paste it back in if needed. 4. To format additional cells this way in an existing worksheet, you might find it handy to Copy a correctly formatted cell, and Paste Special - Formats (Alt-E,S,T) to apply the format to the added cells. The line feeds coupled with word wrap will cause the %% to end up on a second line in the cells, but just so long as you don't make the rows taller, they'll remain out of view. Excel does seem to reserve horizontal space for them as if they were on the first line though, giving you a minimum column width wider than you would otherwise require for your amounts to fully show. Sample attached (Excel 2007+). I was unable to get Excel 2010 to save the file correctly in XLS format, so I'm interested to hear if users of Excel 2003 and older can get this to work. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Rajan_Verma Sent: Tuesday, April 10, 2012 5:37 AM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Re: Display numbers in Lakhs It is not working in Excel 2010, I am not too good in custom format. Can you please do something with Excel 2010. Rajan. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Abhishek Jain Sent: Apr/Tue/2012 04:41 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Re: Display numbers in Lakhs Right Click on cell...choose format cells > Custom > under the "Type:" use this - [>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0 On Tue, Apr 10, 2012 at 4:11 PM, Prabhu <prabhugate...@gmail.com> wrote: Hi, Plz help to solve the below Query. On Sunday, January 29, 2012 11:12:26 AM UTC+5:30, Prabhu wrote: Hi, In Excel I needs to convert numbers in to indian format and the value should divided by lakhs and display in Lakhs. Example Rs.924104600.90 should reflect as Rs.9,241(in lakhs, Indian format) Plz help. Regards, Prabhu On Sunday, January 29, 2012 11:12:26 AM UTC+5:30, Prabhu wrote: Hi, In Excel I needs to convert numbers in to indian format and the value should divided by lakhs and display in Lakhs. Example Rs.924104600.90 should reflect as Rs.9,241(in lakhs, Indian format) Plz help. Regards, Prabhu -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
