How to format very large numbers?

[JenniferMurphy]

I need to format numbers from 0 to 10^24 or more. Here's what I'd like

0 - 999 -> as is
1,000 - 999,000 -> xxx thousand
1,000,000 - 999,000,000 -> xxx million
1,000,000,000 - 999,000,000,000 -> xxx billion
... and so on

Is there a formatting string that will do that?

Is there a built-in function that will convert the number into that string or does anyone have a UDF that will do that.

I don't care whether the result is a number or a string.

 

[Engberg]

I made this, it would work all the way up to e24 and beyond, if it wasn't for Excel starting to convert numbers to scientific notation after 100 Trillion. At that point the function starts outputting "1E Thousand", incorrectly. I might revisit to fix it, or maybe someone else can look at it.

Function namesOfLargeNumbers(n As Double)
On Error GoTo ErrorHandler
    If Not IsNumeric(n) Then GoTo ErrorHandler
    Dim nStr As String
    
    nStr = CStr(n)
    l = Len(nStr)
    
    If l < 4 Then namesOfLargeNumbers = n: GoTo Continue
    
    Dim d
    Set d = CreateObject("Scripting.Dictionary")
    d.Add 4, "Thousand"
    d.Add 7, "Million"
    d.Add 10, "Billion"
    d.Add 13, "Trillion"
    d.Add 16, "Quadrillion"
    d.Add 19, "Quintillion"
    d.Add 22, "Sextillion"
    d.Add 25, "Septillion"
    
    Dim name As String
    Dim k As Integer
    For i = 0 To d.Count - 1
        If l >= d.Keys()(i) And l < d.Keys()(i + 1) Then
            name = d.Items()(i)
            k = d.Keys()(i) - 1
            Exit For
        End If
    Next
    
    remainingDigits = Left(n, l - k)
    'Debug.Print remainingDigits, name
    
    namesOfLargeNumbers = remainingDigits & " " & name

Continue:
Exit Function

ErrorHandler:
namesOfLargeNumbers = "<Error>"
Exit Function

End Function

 

[JenniferMurphy]

I got this code to work all the way up to decillions!

Function ConvertToScaledText(pNumber)

Application.Volatile

Debug.Assert False
Stop

Dim aUnits() As String          'Array for units
Const sUnits As String = "x thousand million billion trillion quadrillion" _
                         & " quintillion sextillion septillion octillion nonillion" _
                         & " decillion"
aUnits = Split(sUnits, " ")     'Split the units into the array
aUnits(LBound(aUnits)) = ""     'Make <1000 null

Dim nScaled As Double   'Number that gets scaled along the way
nScaled = pNumber       '.Start with input number

Dim i As Long           'Loop index
For i = LBound(aUnits) To UBound(aUnits)    'Go through the units
                                'If the scaled number < 100 OR this is the last unit
    If Round(nScaled, 0) < 1000 Or i = UBound(aUnits) Then
        ConvertToScaledText = Format(Round(nScaled, 0), "#,##0") & " " & aUnits(i)
        Exit Function               'Do the formatting & exit
    Else                        'If scaled number is still > 1000
        nScaled = nScaled / 1000    'Scale another 3 decimal places & try again
    End If
Next i

End Function

Here's a minisheet testing it. Let me know if you see any errors or know a better way.

ConvertToScaledText.xlsx
	C	D
4	Number	ConvertToScaledText
5	0.4999	0
6	0.5000	0
7	0.5100	1
8	999.4990	999
9	999.5000	1 thousand
10	999,499.0000	999 thousand
11	999,500.0000	1 million
12	999,499,000.0000	999 million
13	999,500,000.0000	1 billion
14	999,499,000,000.0000	999 billion
15	999,500,000,000.0000	1 trillion
16	999,499,000,000,000.0000	999 trillion
17	999,500,000,000,000.0000	1 quadrillion
18	999,499,000,000,000,000.0000	999 quadrillion
19	999,500,000,000,000,000.0000	1 quintillion
20	999,499,000,000,000,000,000.0000	999 quintillion
21	999,500,000,000,000,000,000.0000	1 sextillion
22	999,499,000,000,000,000,000,000.0000	999 sextillion
23	999,500,000,000,000,000,000,000.0000	1 septillion
24	999,499,000,000,000,000,000,000,000.0000	999 septillion
25	999,500,000,000,000,000,000,000,000.0000	1 octillion
26	999,499,000,000,000,000,000,000,000,000.0000	999 octillion
27	999,500,000,000,000,000,000,000,000,000.0000	1 nonillion
28	999,499,000,000,000,000,000,000,000,000,000.0000	999 nonillion
29	999,500,000,000,000,000,000,000,000,000,000.0000	1 decillion
30	9,994,990,000,000,000,000,000,000,000,000,000,000.0000	9,995 decillion
31	999,499,000,000,000,000,000,000,000,000,000,000,000.0000	999,499 decillion
32	99,949,900,000,000,000,000,000,000,000,000,000,000,000.0000	99,949,900 decillion
33	900,000,000,000,000,000,000,000,000,000,000,000,000,000,000.0000	900,000,000,000 decillion
Sheet1

Cell Formulas
Range	Formula
D5:D33	D5	=ConvertToScaledText([@Number])

 

[Engberg]

If it works, it's good Curious though, if you Debug.Print pNumber at the top of your function, what does it print for 1 quadrillion or larger?

 

[JenniferMurphy]

If it works, it's good Curious though, if you Debug.Print pNumber at the top of your function, what does it print for 1 quadrillion or larger?

Interesting. That took a bit of work. Just adding that statement was not very helpful. I got 29 results in the Immediate window, but they were not in the same order as the table. So I added a Row column to the table and passed that to the UDF. It then printed the row number with the value of pNumber. I was then able to copy those values into Word, sort o the row, and paste that back into Excel. The results are in this minisheet. Do you see a problem?

ConvertToScaledText.xlsx
	C	D	E	F
4	Number	Row	ConvertToScaledText	pNumber
5	0.4999	1	0	0.4999
6	0.5000	2	0	0.5
7	0.5100	3	1	0.51
8	999.4990	4	999	999.499
9	999.5000	5	1 thousand	999.5
10	999,499.0000	6	999 thousand	999499
11	999,500.0000	7	1 million	999500
12	999,499,000.0000	8	999 million	999499000
13	999,500,000.0000	9	1 billion	999500000
14	999,499,000,000.0000	10	999 billion	9.99499E+11
15	999,500,000,000.0000	11	1 trillion	9.995E+11
16	999,499,000,000,000.0000	12	999 trillion	9.99499E+14
17	999,500,000,000,000.0000	13	1 quadrillion	9.995E+14
18	999,499,000,000,000,000.0000	14	999 quadrillion	9.99E+17
19	999,500,000,000,000,000.0000	15	1 quintillion	1.00E+18
20	999,499,000,000,000,000,000.0000	16	999 quintillion	9.99E+20
21	999,500,000,000,000,000,000.0000	17	1 sextillion	1.00E+21
22	999,499,000,000,000,000,000,000.0000	18	999 sextillion	9.99E+23
23	999,500,000,000,000,000,000,000.0000	19	1 septillion	1.00E+24
24	999,499,000,000,000,000,000,000,000.0000	20	999 septillion	9.99E+26
25	999,500,000,000,000,000,000,000,000.0000	21	1 octillion	1.00E+27
26	999,499,000,000,000,000,000,000,000,000.0000	22	999 octillion	9.99E+29
27	999,500,000,000,000,000,000,000,000,000.0000	23	1 nonillion	1.00E+30
28	999,499,000,000,000,000,000,000,000,000,000.0000	24	999 nonillion	9.99E+32
29	999,500,000,000,000,000,000,000,000,000,000.0000	25	1 decillion	1.00E+33
30	9,994,990,000,000,000,000,000,000,000,000,000,000.0000	26	9,995 decillion	9.99E+36
31	999,499,000,000,000,000,000,000,000,000,000,000,000.0000	27	999,499 decillion	9.99E+38
32	99,949,900,000,000,000,000,000,000,000,000,000,000,000.0000	28	99,949,900 decillion	9.99E+40
33	900,000,000,000,000,000,000,000,000,000,000,000,000,000,000.0000	29	900,000,000,000 decillion	9.00E+44
Sheet1

Cell Formulas
Range	Formula
D5:D33	D5	=ROW()-ROW(Table1[[#Headers],[Row]])
E5:E33	E5	=ConvertToScaledText([@Number],[@Row])

PS: I pasted the values into an Excel column formatted as Text, so Excel wouldn't mess with them.

 

[Engberg]

No no, your function seems to work fine, I was only asking to figure out why my approach isn't workng :D

 

[JenniferMurphy]

No no, your function seems to work fine, I was only asking to figure out why my approach isn't workng :D

Ok. Let me know if you need anything more. Of course you have my code, so you can test it yourself.

I really like this function. I needed it because I was working on a sheet that would compare relativistic travel times to various destinations in the universe. The universe is huge. The distances in light years vary from 5.92E-06 (35 million miles to Mars) to 4.60E+10 (270 sextillion miles to the edge of the universe).

I feel like it should be built into Excel as a formatting option. It already lets us scale by affixing commas (#,##0,,). But of course when you start dealing units, especially variable units, then you get into metric vs US vs British.

 

[felixstraube]

A formatting string that does something similar, but not quite as you asked. It is like notation used in engineering:

Libro1
	E	F	G
4		"##0.0E+0"	"##0E+0"
5	0.000001	1.0E-6	1E-6
6	0.00001	10.0E-6	10E-6
7	0.0001	100.0E-6	100E-6
8	0.001	1.0E-3	1E-3
9	0.01	10.0E-3	10E-3
10	0.1	100.0E-3	100E-3
11	1	1.0E+0	1E+0
12	10	10.0E+0	10E+0
13	100	100.0E+0	100E+0
14	1000	1.0E+3	1E+3
15	10000	10.0E+3	10E+3
16	100000	100.0E+3	100E+3
17	1000000	1.0E+6	1E+6
18	10000000	10.0E+6	10E+6
19	100000000	100.0E+6	100E+6
20	1000000000	1.0E+9	1E+9
21	10000000000	10.0E+9	10E+9
22	100000000000	100.0E+9	100E+9
23	1000000000000	1.0E+12	1E+12
24	10000000000000	10.0E+12	10E+12
25	100000000000000	100.0E+12	100E+12
26	1000000000000000	1.0E+15	1E+15
27	10000000000000000	10.0E+15	10E+15
28	100000000000000000	100.0E+15	100E+15
29	1000000000000000000	1.0E+18	1E+18
30	10000000000000000000	10.0E+18	10E+18
31	100000000000000000000	100.0E+18	100E+18
32	1000000000000000000000	1.0E+21	1E+21
33	10000000000000000000000	10.0E+21	10E+21
Hoja1

Cell Formulas
Range	Formula
F5:F33	F5	=E5
G5:G33	G5	=E5
E6:E33	E6	=E5*10

 

[felixstraube]

You could use this to build a formula like this, (so you have an alternative to a UDF):

Formato.xlsx
	E	F
5	1	1
6	10	10
7	100	100
8	1000	1 Thousand
9	10000	10 Thousand
10	100000	100 Thousand
11	1000000	1 Million
12	10000000	10 Million
13	100000000	100 Million
14	1000000000	1 Billion
15	10000000000	10 Billion
16	100000000000	100 Billion
17	1000000000000	1 Trillion
18	10000000000000	10 Trillion
19	100000000000000	100 Trillion
20	1000000000000000	1 Quadrillion
21	10000000000000000	10 Quadrillion
22	100000000000000000	100 Quadrillion
23	1000000000000000000	1 Quintillion
24	10000000000000000000	10 Quintillion
25	100000000000000000000	100 Quintillion
26	1000000000000000000000	1 Sextillion
27	10000000000000000000000	10 Sextillion
Hoja1 (2)

Cell Formulas
Range	Formula
F5:F27	F5	=LET(t,TEXT(E5,"##0E+0"),TEXTBEFORE(t,"E")&" "&XLOOKUP(TEXTAFTER(t,"E"),{"+0";"+3";"+6";"+9";"+12";"+15";"+18";"+21";"+24"},{"";"Thousand";"Million";"Billion";"Trillion";"Quadrillion";"Quintillion";"Sextillion";"Septillion"},"not in list",0))

You can update the arrays to your needs. Or use a helper table in a hidden sheet for example with the {"+0";"+3";"+6";"+9";"+12";"+15";"+18";"+21";"+24"},{"";"Thousand";"Million";"Billion";"Trillion";"Quadrillion";"Quintillion";"Sextillion";"Septillion"} values

 

[JenniferMurphy]

A formatting string that does something similar, but not quite as you asked. It is like notation used in engineering:

That's a nice option to have. Thanks for reminding me about using # for scaling in scientific notation. Very helpful.

I may also look into doing the formatting in my UDF and then converting the exponent to the text number (+3 -> thousand, +6 -> million, etc.).