Comments on Yogesh Gupta's Excel Tips: Excel Number Format : Indian Style Comma Separatio...

Hi Yogesh, I have 2000 rows of data. They have w...

2012-02-02T01:42:27.733+05:30

Hi Yogesh,

I have 2000 rows of data. They have with some products names and values. I would like to add comma before any value(number) in the data without typing. Kindly please suggest me how could I do this ?

There is no code available to convert numbers in D...

2011-11-29T18:27:55.247+05:30

There is no code available to convert numbers in Devanagari text e.g. 1 = एक, १०=दस.Code is likely to be more complicated for numbers from Twenty one to 99.Moreover vb does not accept Devanagari text written in Unicode.I shows as ?????.

Thanks for the useful macro.

2011-11-09T13:27:55.547+05:30

Thanks for the useful macro.

is there any contact no. and suitable time for whi...

2011-07-04T16:02:08.723+05:30

is there any contact no. and suitable time for which any quries.

you says:-For easy application of this code you ne...

2011-07-04T16:00:43.916+05:30

you says:-For easy application of this code you need to save it as a excel addin or add it to your personal macro book. Adding a custom toolbar for this code will make it easy to apply. You can chage the number of decimal places by changing the number of zeros at the end of custom number format given above. how i save it as a excel addin and add it to my personal macrobook. kindly reply my id is sidharthsaroha@gmail.com i will be thankful to you for experience sharing

very very thank....

2011-02-19T16:20:31.598+05:30

very very thank....

Yogesh, Let's revisit this old thread on form...

2010-07-30T11:17:34.043+05:30

Yogesh,

Let's revisit this old thread on formatting numbers in the Indian style. This requestion recently came up in another blog...
-
http://chandoo.org/wp/2010/07/26/indian-currency-format-excel/#comments
-
and I posted basically the same event code I posted here. One of the respondents make an side comment about it working for typed in text as well as formulas. Well, it really doesn't work for formulas, but the comment prodded me to modify the code so it could react to either... a number typed directly into the cells being monitored by the event code or, a formula in that range where the formulas displayed value changes because one of the cells referred to in the formula got changed. Here is that modified event code which I posted over in the other blog...
-
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, Cell As Range
Set R = Range("C:C")
If Not Intersect(Target, R) Is Nothing Then
With Target
If WorksheetFunction.IsNumber(.Value) Then
.NumberFormat = Trim(Replace(Format(String(Len(Int(Abs(.Value))) - 1, "#"), _
" @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"
Else
.NumberFormat = "General"
End If
End With
ElseIf Not Intersect(Target.Dependents, R) Is Nothing Then
For Each Cell In Target.Dependents
If Not Intersect(Cell, R) Is Nothing Then
With Cell
If .Value <> "" Then
If WorksheetFunction.IsNumber(.Value) Then
.NumberFormat = Trim(Replace(Format(String(Len(Int(Abs(.Value))) - 1, "#"), _
" @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"
Else
.NumberFormat = "General"
End If
End If
End With
End If
Next
End If
End Sub
-
There is one line of code to maybe watch out for. I don't remember is this blog's comment processor removes "not equal to" symbols (a less than sign followed by a greater than sign. If you do not see a not equal sign in this line...
-
If .Value <> "" Then
-
then here is the line as it should be with a .NE. substituted for the less than/greater than symbols...
-
If .Value .NE. "" Then

Hi Vijay As pointed out by you one liner had some...

2010-03-24T23:16:22.952+05:30

Hi Vijay

As pointed out by you one liner had some issue with the negative numbers. I have corrected this, following code should be able to handle it correctly.

Sub IndianNumberFormat()
For Each C In Selection
C.NumberFormat = Trim(Replace(Format(String( _
Len(Int(Abs(C.Value))) - 1, "#"), _
" @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), _
" \,", "")) & ".00"
Next C
End Sub

However I am not sure about rest of the items pointed out by you.

Thanks
Yogesh Gupta

Hi, for negative values one liner printed like th...

2010-03-24T10:45:03.515+05:30

Hi,

for negative values one liner printed like this -,11,40,773 which needs to be corrected. Also it should retain original decimal values format, color and parenthesis for negative values. Can someone make the changes please.

Thanks

Hi Yogesh, Although I said it in one of my earlie...

2009-11-14T14:37:36.009+05:30

Hi Yogesh,

Although I said it in one of my earlier messages in this thread, I just wanted to say again that you are quite welcome and that I am glad you found my contribution of value.

Hi Augusto Thanks for your appreciative comments...

2009-11-08T10:34:50.469+05:30

Hi Augusto

Thanks for your appreciative comments. This one liner is contributed by Rick Rothstein (MVP - Excel)

Rick is very active on various forums and you can notice his contribution at various places on the net. When ever you serach any VBA help, you may get a code contributed by him.

By this note I want to say Thanks to Rick, he has really added value to this post.

Thanks
Yogesh Gupta

I'm totally amazed. How could I miss till now ...

2009-11-08T05:33:41.456+05:30

I'm totally amazed. How could I miss till now that indian punctuation is so different?
Even the one liner to replace it is awesome!

Thank you for sharing

http://bit.ly/iUhlP

2009-11-08T05:30:56.467+05:30

This comment has been removed by the author.

Hello Rick I like the "challange" and l...

2009-11-03T22:46:53.206+05:30

Hello Rick

I like the "challange" and let me explain the alog of your one liner for that let me break your code into various parts as it is doing mutiple line processing in single line due to its length.

1. Most inner part of your one liner is building up string of # based on cell value. If value is 10 it is #. Incase it is 1000 string is ##. It is actually lengh of the int - 1.

2. Formating this sting of # into " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0" . This builds indian style comma separation number format. However there are some " \," if the length of the int is less than 14.
3. Replacing the " \," with ""
4. Trim the string built after replace as it leaves space in the begining.
5. Finaly adding ".00" for decimal points

Now the entire string is ready as per Indian style comma separation format.

I hope I have understood your code. Let me know if I have missed something.

Regards

You are quite welcome... I am glad that you liked ...

2009-11-03T21:06:18.162+05:30

You are quite welcome... I am glad that you liked my offerings. As for that single line of code solution... well, one-liners are kind of a "thing" with me. Before becoming an Excel MVP, I was a MVP for Visual Basic where I built up a reputation for writing one-liners. Google used to be able to show well over 1000 of them via a simple search (although I think I have written posted several thousand one-liners across the 9/10 years I have been volunteering answering newsgroup questions), but they changed something in their search engine a couple of years ago and now it is hard to find links to them. Anyway, this one-liner contains the same "challenge" for you and your readers that all my past posted ones did... try to figure out how and/or why it works.[grin]

Rick Rothstein (MVP - Excel)

Hello Rick Thanks for your inputs. I am really am...

2009-11-03T10:44:05.345+05:30

Hello Rick

Thanks for your inputs. I am really amazed with your single line code to handle Indian Style comma separation for upto 15 digits number.

These are great inputs and have really added value to my post.

Thanks

By the way, instead of using a manually implemente...

2009-11-03T02:26:18.580+05:30

By the way, instead of using a manually implemented macro, we can make this functionality automatic by using worksheet event code. Just right click the tab at the bottom of the worksheet that is to have this functionality, select View Code from the popup menu that appears and then copy/paste the following into the code window that appears...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Set R = Range("A:A,C3:E10")
If Not Intersect(Target, R) Is Nothing Then
With Target
.NumberFormat = Trim(Replace(Format(String( _
Len(Int(.Value)) - 1, "#"), _
" @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), _
" \,", "")) & ".00"
End With
End If
End Sub

Just change the Range address in the Set statement to cover the range of cells you want to have this functionality. My sample above is set to apply the Indian Number Format to all of Column A plus just the cells in range C3:E10. After entering any number within this range of cells, it will be reformatted into the Indian style for a number.

You can get rid of that entire Select..Case block ...

2009-11-02T21:00:39.118+05:30

You can get rid of that entire Select..Case block and replace it with a single (albeit long) statement...

Sub IndianNumberFormat()
For Each C In Selection
C.NumberFormat = Trim(Replace(Format(String( _
Len(Int(C.Value)) - 1, "#"), _
" @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), _
" \,", "")) & ".00"
Next C
End Sub

The above code should be able to handle up to a 15-digit positive or negative number.

Rick Rothstein (MVP - Excel)

Hi Sam Thanks for your input. However this can ha...

2009-11-01T19:49:25.698+05:30

Hi Sam

Thanks for your input. However this can handle only positive numbers upto 100 Millions.

That is why I have said that no single format can handle all kind of number lenght. This is the reason for using case statement to choose different format based on value.

Thanks

For Positive Numbers Sub Ind_Num_Fmt() For Each C...

2009-11-01T16:48:57.915+05:30

For Positive Numbers

Sub Ind_Num_Fmt()
For Each Cellval In Selection
Cellval.NumberFormat = "[>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00"
Next
End Sub

Sam

Hello Jon - Thanks for your inputs, I am updating ...

2009-10-30T22:08:46.974+05:30

Hello Jon - Thanks for your inputs, I am updating the post above.

You can cut your routine in half. Change the For-E...

2009-10-30T21:40:08.377+05:30

You can cut your routine in half. Change the For-Each to

For Each c In Selection.Cells

and remove everything from If to Else, plus End If.