Thread: Customising the TEXT(Value,"Format") Function for Indian Currency

Customising a TEXT(Value,"Format") for Indain Currency

I use a particular format for displaying numeric values as money in Indian Rupees with appropriate commas after Thousands and Lakhs..

I have the following two formats to be used in the FORMAT CELL for the Numeric values where Money or Currency has to be displayed..

1. [>9999999]"Rs "#\,##\,##\,##0;[>99999]"Rs "#\,##\,##0;"Rs "#,##0 without dedimal points.
Ex:- 125999 -->> Rs 1,25,999

2. [>9999999]"Rs "#\,##\,##\,##0.00;[>99999]"Rs "#\,##\,##0.00;"Rs "#,##0.00 with decimal points upto 2 decimal places...Ex:- 125999 -->> Rs 1,25,999.00

Now this works fantastically for numeric entries, however I need to use the same format when using in a sentence such as Rs 1,25,999 for the XYZ expense..

So how do I customise the same in the TEXT Function..?

What FORMAT do I use in the TEXT Functions parameter to get the same result using it in a sentence..

Regards
e4excel

Try:

TEXT(A1,"[>9999999]""Rs ""#\,##\,##\,##0;[>99999]""Rs ""#\,##\,##0;""Rs ""#,##0")

note the doubling up of quotes.

Yes those are the only changes...

Since the "format" part of the text function itself has to be enclosed in quotes, you have to double up the inside quotes, so that the function doesn't interpret the second quote (before the first RS) as a closing quote...

Have a look at these 2 links to help you understand custom formatting....

http://www.ozgrid.com/Excel/CustomFormats.htm

http://peltiertech.com/Excel/NumberFormats.html

I am probably not the best person to come to for VBA, but if I create a simple sub:

Sub test()
Sheets("NewSheetName").Cells(8, 10).NumberFormat = "[>9999999]""Rs ""#\,##\,##\,##0.00;[>99999]""Rs ""#\,##\,##0.00;""Rs ""#,##0.00"

End Sub

It works fine and displays as: Rs 39,371.00

My sincere apologies for not being able to explain precisely but thats to do more with my VBA lacunae...
I was trying to use the format not in a cell but in a Comment alongwith other Text Strings...
So, please pardon me for any inconveniece caused to you over this..
Nevertheless, that really worked in gettting the desired format in a cell using VBA and with this solution I learnt something about the .NumberFormat option in VBA..


Thanks NBVC,

That works brilliantly in VBA setup..but the only problem is that I was using this to store it in a String variable to be used in a Comment..

Sheets("NewSheetName").Cells(8, 10).Value = 39371

The actual value in this cell is a Number 39371 and using the formatting in the FOrmat cell I get to display it as Rs 39,371.00 and with your help in the posts # 2

TEXT(A1,"[>9999999]""Rs ""#\,##\,##\,##0;[>99999]""Rs ""#\,##\,##0;""Rs ""#,##0")

I could use the above format while using it in Concatenated Text too!

However, I am trying to use this value to be Added in a Comment with the Formatting.

So, my Novice VBA code goes like this

Dim Comm As String
Comm = "Opening Balance" & char(10) &Sheets("NewSheetName").Cells(8, 10).Value
Sheets(NewSheetName).Cells(8, 10).AddComment Text:=Comm
Sheets(NewSheetName).Cells(8, 10).Comment.Visible = True

But when I tried this it simply gave me only the number as 39371 in the comment..
So, I tried using the format option as mentioned in my post # 6

Format(Sheets(NewSheetName).Cells(8, 10), "[>9999999]""Rs ""#\,##\,##\,##0;[>99999]""Rs ""#\,##\,##0;""Rs ""#,##0")

it gave me this Rs,,39371

But I need to get it as this in the Comment:

"Opening Balance"
Rs 39,371,00

So, its kinda using the Number Format in a TEXT option the way you showed me in the POST #2 however for VBA and more importantly in the Comments which is shown wit the variable Comm as String..

Warm Regards
e4excel

Perhaps try using the Excel Worksheet Function: Text

E.g.

Dim Comm As String
With Sheets("NewSheetName")
    Comm = "Opening Balance" & Chr(10) & Application.WorksheetFunction.Text(.Cells(8, 10), "[>9999999]""Rs ""#\,##\,##\,##0.00;[>99999]""Rs ""#\,##\,##0.00;""Rs ""#,##0.00")
    .Cells(8, 10).AddComment Text:=Comm
    .Cells(8, 10).Comment.Visible = True
End With

Originally Posted by e4excel

P.S. Just had a small correction With Sheets(NewSheetName) as this was a String Variable used for new Sheets..

Warm Regards
e4excel

Yeah, I wasn't sure about that... but in order to make it work for my sample, I named the actual sheet as NewSheetName... that is the reason for the quotes around the name....