Custom format for Value in Crore

[Ketan_ashiyani]

Dear Team,

We need to require Custom format for Value in Crore

Ex. 1,00,00,000
Requirement 1.00 Cr

 

[Sanjeev1976]

Hi Ketan,

Welcome to the group.

You can use the format #\.0,,"0 Cr" to get the desired result

 

[Ketan_ashiyani]

Hi Ketan,

Welcome to the group.

You can use the format #\.0,,"0 Cr" to get the desired result

Dear Sir,

Thank,

My Excel Cell Value Rs. 1251701213

and as per the provided solution, the result value is 125.20 Cr but need to Value is 125.17 Cr

 

[Ketan_ashiyani]

Dear Team,

Please provide solution for the same

 

[felixstraube]

With just formatting the cell I don't think it is possible.
You would need to use a formula. Basically you are dividing by 10.000.000 and showing 2 decimal places.

Book1.xlsx
	A	B
1	1251701213	125.17
Sheet2

Cell Formulas
Range	Formula
B1	B1	=A1/10000000

 

[Ketan_ashiyani]

Dear Team,

We need to require Custom format for Value in Crore

Ex. 1,00,00,000
Requirement 1.00 Cr

Please reply on this..

 

[felixstraube]

I did. But you ignored my reply.

 

[felixstraube]

here the same but with another formatting:

Cell A1 has custom format: #","##","##","##0
Cell B1 has custom format: 0.00 "Cr"

Book3
	A	B
10	125,17,01,213	125.17 Cr
Sheet2

Cell Formulas
Range	Formula
B10	B10	=A10/10000000

There is no way through formatting to show a value divided by 10.000.000. You can only divide by multiples of 1000 adding a coma at the end of the format string.

 

[felixstraube]

I found a trick to show it as you want

Book1
	A	B
1	123,45,67,890.00	123.46 Crs %
Sheet1

Cell Formulas
Range	Formula
B1	B1	=A1

Format in A1:
#\,##\,##\,##0.00

Format in B1:
0.00,,," Crs"<Ctrl+J>%

Where it says <Ctrl+J> you would hold Ctrl and press J what would add a "carriage return".
So actually you would have this in your cell:

But you adjust the height of the cell to show only the first line of text. And you need to have

activated.
So it will show as:

 

[Ketan_ashiyani]

I found a trick to show it as you want

Book1
	A	B
1	123,45,67,890.00	123.46 Crs %
Sheet1

Cell Formulas
Range	Formula
B1	B1	=A1

Format in A1:
#\,##\,##\,##0.00

Format in B1:
0.00,,," Crs"<Ctrl+J>%

Where it says <Ctrl+J> you would hold Ctrl and press J what would add a "carriage return".
So actually you would have this in your cell:

View attachment 117186
But you adjust the height of the cell to show only the first line of text. And you need to have View attachment 117187 activated.
So it will show as:

View attachment 117188

Thank for your sharing the solution but also we want for negative value is should be in red colour with bracket