Display thousand separator in text function output

[Sachin2k6]

Hi all

I have numbers in cell A1 (say 3545852) as a sum of a data range. I want to display it like this "Rs. 35,45,852.00 Cr."

How can i do this. I used text function like this

="Rs. " & text(A1,"0,00.00") & " Cr."

But it displays "Rs. 3545852.00 Cr."

I want thousand separator also.

Is it possible???

 

[Andrew Poulsom]

The thousands separator separates thousands, not hundreds. You need to add the comma as text:

="Rs. " & TEXT(A1,"00\,00\,000.00")& " Cr."

 

[Sachin2k6]

Thanks Andrew.

Just one more thing why you use "\"?? when

="Rs. " & TEXT(A1,"00,000.00")& " Cr." gives same result.

 

[Andrew Poulsom]

That doesn't give the same result, it gives Rs. 3,545,852.00 Cr.. The backslash is used to add characters within the format, eg ="Rs. " & TEXT(A1,"00\|00\|000.00")& " Cr.". For more information see:

Number format codes - Excel - Office.com

 

[Sachin2k6]

That doesn't give the same result, it gives Rs. 3,545,852.00 Cr.. The backslash is used to add characters within the format, eg ="Rs. " & TEXT(A1,"00\|00\|000.00")& " Cr.". For more information see:

Number format codes - Excel - Office.com

Actually it's a regional setting. When region is set to English(India) digits groups like 35,45,852.00 else if region is set to English(U.S.) it displays 3,545,852.00

 

[Andrew Poulsom]

I wasn't aware of that. It seems to have been introduced in Excel 2007. With English(India) set you can use:

=TEXT(A1,"#,##0.00")

 

[Rick Rothstein]

Hi all

I have numbers in cell A1 (say 3545852) as a sum of a data range. I want to display it like this "Rs. 35,45,852.00 Cr."

How can i do this. I used text function like this

="Rs. " & text(A1,"0,00.00") & " Cr."

But it displays "Rs. 3545852.00 Cr."

I want thousand separator also.

Is it possible???

You may find this mini-blog article of mine to be of some interest...

Excel Number Format: Indian Style Comma Separation

 

[Sachin2k6]

Thanks Sir