Convert numeric to text

[jacknc]

I have a value in a5 (82700000) Needing it to display in a6 as text 82.7 Million. Could also be 827000000, needs to be displayed as text 8.27 Billion
I know it has to do with Len
Just cant get there,
Any tips or solutions are greatly appreciated.
Jack

 

[philwojo]

I'm a bit busy, and I'm very sure there is a better way to do this, but if you can only have those two lengths of values then this should work. If you can have something other than those two lengths, then this doesn't account for that.

=IF(LEN(A1)=8,LEFT(A1,2)&"."&MID(A1,3,1)&" Million",LEFT(A1,1)&"."&MID(A1,2,2)&" Billion")

 

[jtakw]

Hi,

If you want the converted Text in a Separate cell, use B5 formula copied down, be aware that results are Text and no longer Numeric values.

Or, Custom Format the subject cells as shown in D11, subject cells will display as D5:D8, cell values Remains as Real Numbers:

Book1
	A	B	C	D
5	82700000	82.7 Million		82.7 Million
6	8270000000	8.27 Billion		8.27 Billion
7	827000000	827 Million		827. Million
8	8270000	8.27 Million		8.27 Million
9
10				Custom format cells
11				[<1000000000]#.##,," Million";#.##,,," Billion"
Sheet448

Cell Formulas
Range	Formula
B5	=A5/IF(LEN(A5)<10,1000000,1000000000)&(IF(LEN(A5)<10," Million"," Billion"))

 

[GR00007]

Here's a function that will take a numeric cell and return words: https://www.ablebits.com/office-addins-blog/2013/08/29/convert-numbers-words-excel/