Numbers to text in Excel

By John — in addins, arrows, macro, Numbers to text in excel, numbers to words, spell numbers, vba · 13 min read
Numbers to text in Excel

Have you ever wanted to spell out a number as words , perhaps for reporting / automated form filling ?

This script  / UDF  takes some cues from the various scripts out there, however they didn't cover numbers beyond a billion, decimal places beyond two, and often appended currency names to them. 

If you did want to use currency names or smaller numbers, here's some links to other, similar scripts 

Online Training Hub 

AbleBits.com

Support.Office.Com 

Insanely big numbersThe above is taking it to extremes, but you get the idea. 

So how do we turn 123456789 into :

'ONE HUNDRED TWENTY THREE MILLION FOUR HUNDRED FIFTY SIX THOUSAND SEVEN HUNDRED EIGHTY NINE'

Excel itself has some limitations when it comes to BIG numbers.

Beyond a certain size, numbers will start being truncated / rounded. 

Case in point - if you tried typing a random number of digits up to a Quadrillion (Short-Scale) or more into a cell, it'd default to scientific notation, and promptly round up to the nearest thousand .

To get around this, for big numbers, you can prefix with an apostrophe ( ' ).

This is the shortcut for telling excel to interpret the cell value as text - will allow you to enter some really big numbers, without fear of Excel rounding them off when you get beyond a trillion ( Short-Scale ) .

Garbage in / Garbage out 

Before beginning on any transformation -   we need to remove any formatting or additional data.

To cover all bases, we can process the string looking for only numbers, dashes and the first occurance of a decimal place. Beyond that we can remove the unnecessary characters.

We want to keep ASCII 48 > 57, on occurrence of 45, and one occurrence of 46.

The rest we can discard. 

clean string numbers remove extraneous data

The code :



Private Function NumChunkCleanString(MyString) As String

Dim LP As Integer ' :: GENERIC LOOP VARIABLE ::

' :: RETURN CLEANSED STRING

MyString = Replace(MyString, " ", "") ' :: REMOVE SPACES
MyString = Replace(MyString, ",", "") ' :: REMOVE COMMAS
MyString = Replace(MyString, "'", "") ' :: REMOVE APOSTROPHES
MyString = StrReverse(Replace(StrReverse(MyString), ".", "|", , 1))   ' :: POINT TO PIPE FOR FIRST POINT
MyString = Replace(MyString, Chr(46), "")  ' :: NULL ALL ADDITIONAL POINTS ::
MyString = Replace(MyString, Chr(133), "")  ' :: NULL ALL ADDITIONAL POINTS ::

' :: REMOVE ANY ADDITIONAL DASHES ::
If Left(MyString, 1) = "-" And Len(MyString) > 1 Then MyString = "-" & Replace(Right(MyString, Len(MyString) - 1), "-", "")

':: REMOVE NON-NUMERIC CHARACTERS ::

For LP = 1 To Len(MyString)

    If NumericDict(Asc(Mid(MyString, LP, 1))) = "Y" Then NumChunkCleanString = NumChunkCleanString & Mid(MyString, LP, 1)

Next LP


End Function

This will cleanse the initial string of any extraneous characters, retaining only numbers [1-9] , a single dash on the leftmost position, and a single decimal place.

So the later functions can work off a clean number, you can see how it deals with various formats :

 CLEAN NUMERIC STRINGS

 Hopefully this'll cover all bases.

What's the NumericDict function ? 

This is just a dictionary of 'permitted' ASCII codes , that returns a 'Y' if valid. 

We'll be using this functionality later, and the VBA dictionary object is a quick and easy way of performing lookups within code. 

N.B If you're not already using it, you'll need a reference to the Microsoft Scripting Runtime, this will allow you to utilise data types like dictionaries that aren't in the standard Excel.

You can enable it via Tools > References > MicroSoft Scripting Runtime ( when in the VBA window ALT+F11 )

Runtime




Private Function NumericDict(ByVal MyInteger As Integer) As String

' :: RETURN ' Y ' OR  ' N ' IF VALID CHARACTER ::
Dim MyDict As Dictionary
   
Set MyDict = New Dictionary
MyDict(48) = "Y" 'ZERO
MyDict(49) = "Y" 'ONE
MyDict(50) = "Y" 'TWO
MyDict(51) = "Y" 'THREE
MyDict(52) = "Y" 'FOUR
MyDict(53) = "Y" 'FIVE
MyDict(54) = "Y" 'SIX
MyDict(55) = "Y" 'SEVEN
MyDict(56) = "Y" 'EIGHT
MyDict(57) = "Y" 'NINE
MyDict(45) = "Y" 'DASH
MyDict(46) = "Y" 'POINT
MyDict(124) = "Y" 'PIPE

If MyDict.EXISTS(MyInteger) Then
    
    NumericDict = MyDict(MyInteger)

Else

    NumericDict = "N"

End If

End Function

Zero Padding 

Now that we've cleaned the extraneous data from the string, the next step is to normalise the length of it into chunks of three digits.


Private Function NumChunkPadMe(ByVal MyString As String) As String

':: PAD STRING TO NEAREST MULTIPLE OF THREE ( ROUND UP!! )

Dim MyPad As String

NumChunkPadMe = "" & ZeroPad(MyString, NumChunkCount(MyString) * 3)

End Function

This will be used in the next step and simplifies the process of iterating through the numeric string and turning each 'chunk' into its written counterpart.

If we have the number  123456789 , we want to pad the string to the nearest multiple of 3.

We now have "0123456789" as the nearest multiple of 3 greater than 8 is  9.

We can then iterate through each character in the string, in reverse order, to derive the value of that portion, starting with units and working upwards :

PlaceReverseDescription 
19Units 
28Tens 
37Hundreds 
46Thousands 
55Ten-Thousands 
64Hundred-Thousands 
73Millions 
82Ten-Millions 
91Hundred-Millions 
10-Billions 
11-Ten-Billions 
    

The script will work through in chunks of three,  so it'd begin with "Seven Hundred Eighty Nine"

The next cluster of three, it'd return "Four Hundred Fifty Six"  and append that bracket's name, in this case 'Thousand".

For the final cluster,  it'd return "One Hundred Twenty Three" + "million" .

Assembled into one string, you'd get:

"One Hundred Twenty Three Million, Four Hundred Fifty Six Thousand, Seven Hundred Eighty Nine." 

The first part of the code - this takes a 3 digit input, and returns the Hundreds, Tens and Units description.



Private Function NumChunkWordBlock(MyString As String) As String

Dim LP As Index ' GENERIC LOOP
Dim NumHund, NumTen, NumUnit As Double '  Portions

NumChunkWordBlock = "" ' :: MAKE BLANK ::

NumHund = Val(Left(MyString, 1)) ' HUNDREDS
NumTen = Val(Mid(MyString, 2, 1)) ' TENS
NumUnit = Val(Right(MyString, 1)) ' UNITS


' :: DO HUNDREDS FIRST ::

If NumHund > 0 Then
    
    NumChunkWordBlock = NumChunkName(NumHund) & " " & NumChunkName(100)

End If

':: CHECK FOR > 19

If (NumTen * 10) + NumUnit > 19 Then

    NumChunkWordBlock = NumChunkWordBlock & " " & NumChunkName(NumTen * 10)
    
    If NumUnit > 0 Then  ':: HAS UNITS?
        NumChunkWordBlock = NumChunkWordBlock & " " & NumChunkName(NumUnit)
    End If

End If

':: CHECK FOR <= 19

If (NumTen * 10) + NumUnit <= 19 And (NumTen * 10) + NumUnit > 0 Then

    NumChunkWordBlock = NumChunkWordBlock & " " & NumChunkName((NumTen * 10) + NumUnit)
    
End If


End Function

 The next function assembles this together with its placement within the number ( Thousands, Millions, Billions etc. )

Private Function NumChunkWords(MyString As String) As String

Dim LP As Integer       ' :: GENERIC LOOP VARIABLE ::
Dim MyChunks As Integer ' :: CHUNKS OF 3 NUMBERS ::
Dim Outstring As String ' ::  RAW OUTPUT STRING ::
Dim PadString As String '  :: ZERO-PADDED NUMBER DIVISIBLE BY THREE ::
Dim MyPowerLUV As Double  ' :: POWER OF THREE-DIGIT PORTION ::
Dim MyPos As Integer  ' :: STRING POSITION CURSOR ::

' :: only use integer portion

PadString = NumChunkPadMe(MyString) ' zero-padded string to multiple of three

MyChunks = NumChunkCount(MyString)  ' Count of three digit chunks

MyPos = 1
    
             ' :: CASE WHEN VALUE IS 0 ::
    If MyChunks = 1 And PadString = "000" Then
            NumChunkWords = "ZERO"
            Exit Function
    End If
    
    For LP = 1 To MyChunks
            
        MyPowerLUV = 10 ^ ((MyChunks - LP) * 3)  ' LOOKUP FOR POWER BRACKET ::
            
    
        If MyPos = MyChunks Then
            Outstring = Outstring & NumChunkWordBlock(Mid(PadString, (3 * LP) - 2, 3)) & " "

        End If
    
        If MyPos <> MyChunks Then
            If Val(Mid(PadString, (3 * LP) - 2, 3)) > 0 Then
                Outstring = Outstring & NumChunkWordBlock(Mid(PadString, (3 * LP) - 2, 3)) & " "
                Outstring = Outstring & NumChunkName(MyPowerLUV) & " "
            End If
        End If
        
        MyPos = MyPos + 1
        
        
    Next LP

NumChunkWords = UCase(Trim(Outstring))

End Function

This should be enough to handle all integers , but what about non-integer (floating point)  ?

 Here's the next part - this will work forwards through the decimal portion of a string and describe that number's significance :


Private Function NumChunkDecimal(MyString As String) As String

Dim LP As Integer                       ':: GENERIC LOOP VARIABLE ::
Dim LUVdouble As Double       ' ::DICTIONARY LOOKUP POWER ::

For LP = 1 To Len(MyString)

    LUVdouble = 1 / (10 ^ LP)    ' RAISE 10 TO THE POWER OF LOOP ITERATION
    
    If Mid(MyString, LP, 1) <> "0" Then ' :: ONLY RETURN DATA IF NOT ZERO AT POSITION ::
            'RETURN TEXT VALUE OF POWER FROM
            NumChunkDecimal = NumChunkDecimal & NumChunkName(Mid(MyString, LP, 1)) ' NUMBER NAME
            NumChunkDecimal = NumChunkDecimal & " " & NumChunkName(LUVdouble)  ' POSITION NAME
                If Mid(MyString, LP, 1) <> "1" Then
                    NumChunkDecimal = NumChunkDecimal & "S "
                Else
                    NumChunkDecimal = NumChunkDecimal & " "
                End If
            Debug.Print "[" & LUVdouble & "]"
End If

Next LP
NumChunkDecimal = UCase(Trim(NumChunkDecimal)) ' :: UPPER AND TRIM ::

End Function

These two functions use the following dictionary :


Private Function NumChunkName(ByVal MyInteger As Double) As String

Dim MyDict As Dictionary
   
    Set MyDict = New Dictionary
     
        MyDict(0) = "zero"
        MyDict(1) = "one"
        MyDict(2) = "two"
        MyDict(3) = "three"
        MyDict(4) = "four"
        MyDict(5) = "five"
        MyDict(6) = "six"
        MyDict(7) = "seven"
        MyDict(8) = "eight"
        MyDict(9) = "nine"
        MyDict(10) = "ten"
        MyDict(11) = "eleven"
        MyDict(12) = "twelve"
        MyDict(13) = "thirteen"
        MyDict(14) = "fourteen"
        MyDict(15) = "fifteen"
        MyDict(16) = "sixteen"
        MyDict(17) = "seventeen"
        MyDict(18) = "eighteen"
        MyDict(19) = "nineteen"
        MyDict(20) = "twenty"
        MyDict(30) = "thirty"
        MyDict(40) = "forty"
        MyDict(50) = "fifty"
        MyDict(60) = "sixty"
        MyDict(70) = "seventy"
        MyDict(80) = "eighty"
        MyDict(90) = "ninety"
        MyDict(100) = "hundred"
        MyDict(1000) = "thousand"
        MyDict(1000000) = "million"
        MyDict(1000000000) = "billion"
        MyDict(1000000000000#) = "trillion"
        MyDict(1E+15) = "quadrillion"
        MyDict(1E+18) = "quintillion"
        MyDict(1E+21) = "Sextillion"
        MyDict(1E+24) = "Septillion"
        MyDict(1E+27) = "Octillion"
        MyDict(1E+30) = "Nonillion"
        MyDict(1E+33) = "Decillion"
        MyDict(0.1) = "Tenth"
        MyDict(0.01) = "Hundredth"
        MyDict(0.001) = "Thousandth"
        MyDict(0.0001) = "Ten-Thousandth"
        MyDict(0.00001) = "Hundred-Thousandth"
        MyDict(0.000001) = "Millionth"
        MyDict(0.0000001) = "Ten-Millionth"
        MyDict(0.00000001) = "Hundred-Millionth"
        MyDict(0.000000001) = "Billionth"
        MyDict(0.0000000001) = "Ten-Billionth"
        MyDict(0.00000000001) = "Hundred-Billionth"
        MyDict(0.000000000001) = "Trillionth"
        MyDict(0.0000000000001) = "Ten-Trillionth"
        MyDict(0.00000000000001) = "Hundreed-Trillionth"
        MyDict(0.000000000000001) = "Quadrillionth"
        MyDict(1E-16) = "Ten-Quadrillionth"
        MyDict(1E-17) = "Hundred-Quadrillionth"
        MyDict(1E-18) = "Quitillionth"
        MyDict(1E-19) = "Ten-Quitillionth"
        MyDict(1E-20) = "Hundred-Quitillionth"
        MyDict(1E-21) = "Sextillionth"
        MyDict(1E-22) = "Ten-Sextillionth"
        MyDict(1E-23) = "Hundred-Sextillionth"
        MyDict(1E-24) = "Septillionth"

    

If MyDict.EXISTS(MyInteger) Then
    
    NumChunkName = MyDict(MyInteger)

Else
    
    NumChunkName = ""

End If

End Function

This should cover most scenarios. 

Putting it together

Now to combine the two functions, into a single UDF to be used within a worksheet.


Function NumberToWords(ByVal MyString As String, Optional IsInteger As Boolean = True) As String

Dim MyArray() As String

MyString = NumChunkCleanString(MyString)

MyArray = Split(MyString, "|")

Select Case UBound(MyArray, 1)

Case 0:
    NumberToWords = NumChunkWords(MyArray(0))
Case 1:
NumberToWords = NumChunkWords(MyArray(0))
    If Not IsInteger Then NumberToWords = NumberToWords & " POINT " & NumChunkDecimal(MyArray(1))
Case Else:
    NumberToWords = "Cannot Calculate"
End Select


End Function

This function works in three stages 

1 :  Splits the string where a pipe exists into either one or two parts.

2:  Checks if the optional  'IsInteger' boolean is set, if set to False move onto 

3 : Process integer and decimal portions into word strings and concatenate conditionally.

Usage in Workbook

 Here's how it'd work within a sheet :

Number NumberToWords() Output Settings
123.456 ONE HUNDRED TWENTY THREE Default - only uses integer portion
123.456 ONE HUNDRED TWENTY THREE POINT FOUR TENTHS FIVE HUNDREDTHS SIX THOUSANDTHS With IsInteger=False - output now displays the decimal portion

By using the IsInteger flag, the script will add on the '.456' decimal portion. 

All scripts 

Copy and paste the whole block below into a new module to use this in your own projects. 



Option Explicit


Function NumberToWords(ByVal MyString As String, Optional IsInteger As Boolean = True) As String

Dim MyArray() As String

MyString = NumChunkCleanString(MyString)

MyArray = Split(MyString, "|")

Select Case UBound(MyArray, 1)

Case 0:
    NumberToWords = NumChunkWords(MyArray(0))
Case 1:
NumberToWords = NumChunkWords(MyArray(0))
    If Not IsInteger Then NumberToWords = NumberToWords & " POINT " & NumChunkDecimal(MyArray(1))
Case Else:
    NumberToWords = "Cannot Calculate"
End Select


End Function

Private Function NumChunkDecimal(MyString As String) As String

Dim LP As Integer                       ':: GENERIC LOOP VARIABLE ::
Dim LUVdouble As Double       ' ::DICTIONARY LOOKUP POWER ::

For LP = 1 To Len(MyString)

    LUVdouble = 1 / (10 ^ LP)    ' RAISE 10 TO THE POWER OF LOOP ITERATION
    
    If Mid(MyString, LP, 1) <> "0" Then ' :: ONLY RETURN DATA IF NOT ZERO AT POSITION ::
            'RETURN TEXT VALUE OF POWER FROM
            NumChunkDecimal = NumChunkDecimal & NumChunkName(Mid(MyString, LP, 1)) ' NUMBER NAME
            NumChunkDecimal = NumChunkDecimal & " " & NumChunkName(LUVdouble)  ' POSITION NAME
                If Mid(MyString, LP, 1) <> "1" Then
                    NumChunkDecimal = NumChunkDecimal & "S "
                Else
                    NumChunkDecimal = NumChunkDecimal & " "
                End If
            
End If

Next LP
NumChunkDecimal = UCase(Trim(NumChunkDecimal)) ' :: UPPER AND TRIM ::

End Function

Private Function NumChunkCleanString(MyString As String) As String

Dim LP As Integer ' :: GENERIC LOOP VARIABLE ::

' :: RETURN CLEANSED STRING

MyString = Replace(MyString, " ", "") ' :: REMOVE SPACES
MyString = Replace(MyString, ",", "") ' :: REMOVE COMMAS
MyString = Replace(MyString, "'", "") ' :: REMOVE APOSTROPHES
MyString = StrReverse(Replace(StrReverse(MyString), ".", "|", , 1))   ' :: POINT TO PIPE FOR FIRST POINT
MyString = Replace(MyString, Chr(46), "")  ' :: NULL ALL ADDITIONAL POINTS ::
MyString = Replace(MyString, Chr(133), "")  ' :: NULL ALL ADDITIONAL POINTS ::

' :: REMOVE ANY ADDITIONAL DASHES ::
If Left(MyString, 1) = "-" And Len(MyString) > 1 Then MyString = "-" & Replace(Right(MyString, Len(MyString) - 1), "-", "")

':: REMOVE NON-NUMERIC CHARACTERS ::

For LP = 1 To Len(MyString)

    If NumericDict(Asc(Mid(MyString, LP, 1))) = "Y" Then NumChunkCleanString = NumChunkCleanString & Mid(MyString, LP, 1)

Next LP


End Function



Private Function NumChunkWords(MyString As String) As String

Dim LP As Integer       ' :: GENERIC LOOP VARIABLE ::
Dim MyChunks As Integer ' :: CHUNKS OF 3 NUMBERS ::
Dim Outstring As String ' ::  RAW OUTPUT STRING ::
Dim PadString As String '  :: ZERO-PADDED NUMBER DIVISIBLE BY THREE ::
Dim MyPowerLUV As Double  ' :: POWER OF THREE-DIGIT PORTION ::
Dim MyPos As Integer  ' :: STRING POSITION CURSOR ::

' :: only use integer portion

PadString = NumChunkPadMe(MyString) ' zero-padded string to multiple of three

MyChunks = NumChunkCount(MyString)  ' Count of three digit chunks

MyPos = 1
    
             ' :: CASE WHEN VALUE IS 0 ::
    If MyChunks = 1 And PadString = "000" Then
            NumChunkWords = "ZERO"
            Exit Function
    End If
    
    For LP = 1 To MyChunks
            
        MyPowerLUV = 10 ^ ((MyChunks - LP) * 3)  ' LOOKUP FOR POWER BRACKET ::
            
    
        If MyPos = MyChunks Then
            Outstring = Outstring & NumChunkWordBlock(Mid(PadString, (3 * LP) - 2, 3)) & " "

        End If
    
        If MyPos <> MyChunks Then
            If Val(Mid(PadString, (3 * LP) - 2, 3)) > 0 Then
                Outstring = Outstring & NumChunkWordBlock(Mid(PadString, (3 * LP) - 2, 3)) & " "
                Outstring = Outstring & NumChunkName(MyPowerLUV) & " "
            End If
        End If
        
        MyPos = MyPos + 1
        
        
    Next LP

NumChunkWords = UCase(Trim(Outstring))

End Function

Private Function NumChunkWordBlock(MyString As String) As String

Dim LP As Index ' GENERIC LOOP
Dim NumHund, NumTen, NumUnit As Double '  Portions

NumChunkWordBlock = "" ' :: MAKE BLANK ::

NumHund = Val(Left(MyString, 1)) ' HUNDREDS
NumTen = Val(Mid(MyString, 2, 1)) ' TENS
NumUnit = Val(Right(MyString, 1)) ' UNITS


' :: DO HUNDREDS FIRST ::

If NumHund > 0 Then
    
    NumChunkWordBlock = NumChunkName(NumHund) & " " & NumChunkName(100)

End If

':: ADD IN THE 'AND' WHERE TENS OR UNITS PORTION IS NOT ZERO

If NumTen + NumUnit > 0 And NumHund > 0 = True Then

NumChunkWordBlock = NumChunkWordBlock & " AND"

End If

':: CHECK FOR > 19

If (NumTen * 10) + NumUnit > 19 Then

    NumChunkWordBlock = NumChunkWordBlock & " " & NumChunkName(NumTen * 10)
    
    If NumUnit > 0 Then  ':: HAS UNITS?
        NumChunkWordBlock = NumChunkWordBlock & " " & NumChunkName(NumUnit)
    End If

End If

':: CHECK FOR <= 19

If (NumTen * 10) + NumUnit <= 19 And (NumTen * 10) + NumUnit > 0 Then

    NumChunkWordBlock = NumChunkWordBlock & " " & NumChunkName((NumTen * 10) + NumUnit)
    
End If


End Function


Private Function NumChunkCount(MyString As String) As Integer

':: COUNT OF BLOCKS OF THREE DIGITS IN NUMBER ::

NumChunkCount = Len(Format(Val(MyString), "#,###")) - Len(Format(Val(MyString), "#")) + 1

End Function


Private Function NumChunkPadMe(ByVal MyString As String) As String

':: PAD STRING TO NEAREST MULTIPLE OF THREE ( ROUND UP!! )

Dim MyPad As String

NumChunkPadMe = "" & ZeroPad(MyString, NumChunkCount(MyString) * 3)

End Function

Private Function NumericDict(ByVal MyInteger As Integer) As String

' :: RETURN ' Y ' OR  ' N ' IF VALID CHARACTER ::
Dim MyDict As Dictionary
   
   
' :: CHECK THE ASCII CODE PASSED AS MyInteger IS VALID NUMERIC CHARACTER ( OR VALID DELIMITER )

Set MyDict = New Dictionary
MyDict(48) = "Y" 'ZERO
MyDict(49) = "Y" 'ONE
MyDict(50) = "Y" 'TWO
MyDict(51) = "Y" 'THREE
MyDict(52) = "Y" 'FOUR
MyDict(53) = "Y" 'FIVE
MyDict(54) = "Y" 'SIX
MyDict(55) = "Y" 'SEVEN
MyDict(56) = "Y" 'EIGHT
MyDict(57) = "Y" 'NINE
MyDict(45) = "Y" 'DASH
MyDict(46) = "Y" 'POINT
MyDict(124) = "Y" 'PIPE

If MyDict.EXISTS(MyInteger) Then
    
    NumericDict = MyDict(MyInteger)

Else
    
    NumericDict = "N"

End If

End Function

Private Function NumChunkName(ByVal MyInteger As Double) As String

'
' ::  DICTIONARY TO CONVERT DOUBLE INPUT TO ENGLISH EQUIVALENT  ::
'

Dim MyDict As Dictionary
   
    Set MyDict = New Dictionary
     
        MyDict(0) = "zero"
        MyDict(1) = "one"
        MyDict(2) = "two"
        MyDict(3) = "three"
        MyDict(4) = "four"
        MyDict(5) = "five"
        MyDict(6) = "six"
        MyDict(7) = "seven"
        MyDict(8) = "eight"
        MyDict(9) = "nine"
        MyDict(10) = "ten"
        MyDict(11) = "eleven"
        MyDict(12) = "twelve"
        MyDict(13) = "thirteen"
        MyDict(14) = "fourteen"
        MyDict(15) = "fifteen"
        MyDict(16) = "sixteen"
        MyDict(17) = "seventeen"
        MyDict(18) = "eighteen"
        MyDict(19) = "nineteen"
        MyDict(20) = "twenty"
        MyDict(30) = "thirty"
        MyDict(40) = "forty"
        MyDict(50) = "fifty"
        MyDict(60) = "sixty"
        MyDict(70) = "seventy"
        MyDict(80) = "eighty"
        MyDict(90) = "ninety"
        MyDict(100) = "hundred"
        MyDict(1000) = "thousand"
        MyDict(1000000) = "million"
        MyDict(1000000000) = "billion"
        MyDict(1000000000000#) = "trillion"
        MyDict(1E+15) = "quadrillion"
        MyDict(1E+18) = "quintillion"
        MyDict(1E+21) = "Sextillion"
        MyDict(1E+24) = "Septillion"
        MyDict(1E+27) = "Octillion"
        MyDict(1E+30) = "Nonillion"
        MyDict(1E+33) = "Decillion"
        MyDict(0.1) = "Tenth"
        MyDict(0.01) = "Hundredth"
        MyDict(0.001) = "Thousandth"
        MyDict(0.0001) = "Ten-Thousandth"
        MyDict(0.00001) = "Hundred-Thousandth"
        MyDict(0.000001) = "Millionth"
        MyDict(0.0000001) = "Ten-Millionth"
        MyDict(0.00000001) = "Hundred-Millionth"
        MyDict(0.000000001) = "Billionth"
        MyDict(0.0000000001) = "Ten-Billionth"
        MyDict(0.00000000001) = "Hundred-Billionth"
        MyDict(0.000000000001) = "Trillionth"
        MyDict(0.0000000000001) = "Ten-Trillionth"
        MyDict(0.00000000000001) = "Hundreed-Trillionth"
        MyDict(0.000000000000001) = "Quadrillionth"
        MyDict(1E-16) = "Ten-Quadrillionth"
        MyDict(1E-17) = "Hundred-Quadrillionth"
        MyDict(1E-18) = "Quitillionth"
        MyDict(1E-19) = "Ten-Quitillionth"
        MyDict(1E-20) = "Hundred-Quitillionth"
        MyDict(1E-21) = "Sextillionth"
        MyDict(1E-22) = "Ten-Sextillionth"
        MyDict(1E-23) = "Hundred-Sextillionth"
        MyDict(1E-24) = "Septillionth"

    

If MyDict.EXISTS(MyInteger) Then
    
    NumChunkName = MyDict(MyInteger)

Else
    
    NumChunkName = ""

End If

End Function



Function ZeroPad(MyInput As Variant, ZeroCount As Integer) As String

ZeroPad = Right(WorksheetFunction.Rept("0", ZeroCount) & CStr(MyInput), ZeroCount)

End Function

 

 

Tagged as addins, arrows, macro, Numbers to text in excel, numbers to words, spell numbers and vba

John —

Started out in media/graphic design then moved to Financial Services / MI data / Business intelligence. ~15 years experience of Excel / VBA , and more recently Qliksense , SQL

  • [CONTACT]
  • [LOCATION]
    London, UK