Number format to display as 1,000,000

[Chandresh]

Hi Team,
I am using the below formula for converting the number format to million formats however the same is not happening
=IF(ISTEXT(B4),B4, "")&TEXT(C4,"#,##0;;@")
the output is coming as 1,20,00,000 however I need the number as 12,000,000 could you please help

Thanks

 

[Tom.Jones]

Hi,

What is in B4 and C4?
Why use TEXT function?

 

[Chandresh]

Hi,

What is in B4 and C4?
Why use TEXT function?

I am creating one paragraph with text at the start and at the end I have to plot the amount so B4 is the text and C4 is the amount.

• For example, my sentence will be like this - I have to pay RS 12,000,000

B4 value = I have to pay RS

C4 value =12000000

 

[Tom.Jones]

Try

=IF(ISTEXT(B4),B4,"")&TEXT(C4,"#,##")

 

[awoohaw]

it is working for me: (but I think you need a spacing space inbetween.)

Mr Excel Questions 3.xlsm
	A	B	C	D
4		text	12500000	text12,500,000
5		8	12500000	12,500,000
6			12500000	12,500,000
Sheet6

Cell Formulas
Range	Formula
D4:D6	D4	=IF(ISTEXT(B4),B4,"")& TEXT(C4,"#,##0;;@")

 

[awoohaw]

what is the regional setting on your computer for the the thousands separator?

 

[awoohaw]

It looks like if your regional settings use a "." as the thousands separator, you cannot override it in a custom number format or TEXT Function format argument.

 

[Chandresh]

It looks like if your regional settings use a "." as the thousands separator, you cannot override it in a custom number format or TEXT Function format argument.

Thanks

 

[awoohaw]

Maybe you can get around the issue by using the words million with a decimal of your regional format:
what I would use #.0,, "Million";;@, a suggestion for you: #,0.. "Million";;@