Function that lets you Spell Out Numbers Inside Excel

Spell Out Numbers In Excel

You know what’s annoying? When you need to spell out a number in Excel, and the software doesn’t offer an easy solution. Ugh, Excel can be such a tease sometimes.

But hey, no need to fret! I’m here to help you out. I’ve got a trick up my sleeve that will make spelling out numbers in Excel a breeze. And if you’re not in the mood to read, we’ve got a video tutorial ready for you. How’s that for convenience?

Now, let’s talk about why Excel hasn’t added this feature yet. Beats me! But hey, we don’t need to wait around for them to do it. Let’s take matters into our own hands and make something that will do it for us. I mean, why not, right?

And guess what? The code we’re using today is courtesy of our product CelTools. It’s a total game-changer, with over 70+ functions and macros that make Excel easy and fast to use. You definitely want to check it out sometime.

Anyway, the VBA code.

So, here’s what you need to do. First things first, press Alt and F11 together to open up the VBA Editor. From there, find the writing that has your workbook’s name (it’s probably ‘Book1’), right-click, insert, and Module. Easy peasy.

Next, copy and paste the VBA code we’ve provided below. Don’t worry, we’ve got your back.

VBA Code

Function SpellOutNumber(number As Double) As String
On Error Resume Next

Select Case number
Case 0: SpellOutNumber = “zero”
Case 1: SpellOutNumber = “one”
Case 2: SpellOutNumber = “two”
Case 3: SpellOutNumber = “three”
Case 4: SpellOutNumber = “four”
Case 5: SpellOutNumber = “five”
Case 6: SpellOutNumber = “six”
Case 7: SpellOutNumber = “seven”
Case 8: SpellOutNumber = “eight”
Case 9: SpellOutNumber = “nine”
Case 10: SpellOutNumber = “ten”
Case 11: SpellOutNumber = “eleven”
Case 12: SpellOutNumber = “twelve”
Case 13: SpellOutNumber = “thirteen”
Case 14: SpellOutNumber = “fourteen”
Case 15: SpellOutNumber = “fifteen”
Case 16: SpellOutNumber = “sixteen”
Case 17: SpellOutNumber = “seventeen”
Case 18: SpellOutNumber = “eighteen”
Case 19: SpellOutNumber = “nineteen”
Case Is < 30: SpellOutNumber = “twenty-” & SpellOutNumber(number – 20)
Case Is < 40: SpellOutNumber = “thirty-” & SpellOutNumber(number – 30)
Case Is < 50: SpellOutNumber = “forty-” & SpellOutNumber(number – 40)
Case Is < 60: SpellOutNumber = “fifty-” & SpellOutNumber(number – 50)
Case Is < 70: SpellOutNumber = “sixty-” & SpellOutNumber(number – 60)
Case Is < 80: SpellOutNumber = “seventy-” & SpellOutNumber(number – 70)
Case Is < 90: SpellOutNumber = “eighty-” & SpellOutNumber(number – 80)
Case Is < 100: SpellOutNumber = “ninety-” & SpellOutNumber(number – 90)
Case Is < 1000: SpellOutNumber = SpellOutNumber(Int(number / 100)) & ” hundred ” & SpellOutNumber(number Mod 100)
Case Is < 1000000: SpellOutNumber = SpellOutNumber(Int(number / 1000)) & ” thousand ” & SpellOutNumber(number Mod 1000)
Case Is < 1000000000#: SpellOutNumber = SpellOutNumber(Int(number / 1000000#)) & ” million ” & SpellOutNumber(number Mod 1000000#)
Case Is < 1000000000000#: SpellOutNumber = SpellOutNumber(Int(number / 1000000000#)) & ” billion ” & SpellOutNumber(number Mod 1000000000#)
Case Is < 1E+15: SpellOutNumber = SpellOutNumber(Int(number / 1000000000000#)) & ” trillion ” & SpellOutNumber(number Mod 1000000000000#)
Case Else: SpellOutNumber = “Number too large”
End Select

SpellOutNumber = Replace(SpellOutNumber, “-zero”, “”)

End Function

Function ConvertToNumber(inputText As String) As String
‘ Define variables
Dim words() As String
Dim i As Long
Dim Result As Double
Dim Dol As String
Dol = “”

Dim lastval As Long
inputText = Replace(inputText, “-“, ” “)

‘ Split input text into individual words
words = Split(inputText, ” “)

‘ Loop through each word
For i = LBound(words) To UBound(words)

‘ Remove commas and periods
words(i) = Replace(words(i), “,”, “”)
words(i) = Replace(words(i), “.”, “”)

‘ Convert currency symbols to empty string
If InStr(words(i), “$”) > 0 Then
words(i) = “”
End If

‘ Convert spelled-out numbers to their corresponding numeric value
Select Case LCase(words(i))
Case “zero”: Result = Result + 0
Case “one”: Result = Result + 1
Case “two”: Result = Result + 2
Case “three”: Result = Result + 3
Case “four”: Result = Result + 4
Case “five”: Result = Result + 5
Case “six”: Result = Result + 6
Case “seven”: Result = Result + 7
Case “eight”: Result = Result + 8
Case “nine”: Result = Result + 9
Case “ten”: Result = Result + 10
Case “eleven”: Result = Result + 11
Case “twelve”: Result = Result + 12
Case “thirteen”: Result = Result + 13
Case “fourteen”: Result = Result + 14
Case “fifteen”: Result = Result + 15
Case “sixteen”: Result = Result + 16
Case “seventeen”: Result = Result + 17
Case “eighteen”: Result = Result + 18
Case “nineteen”: Result = Result + 19
Case “twenty”: Result = Result + 20
Case “thirty”: Result = Result + 30
Case “forty”: Result = Result + 40
Case “fifty”: Result = Result + 50
Case “sixty”: Result = Result + 60
Case “seventy”: Result = Result + 70
Case “eighty”: Result = Result + 80
Case “ninety”: Result = Result + 90

Case “dollar”: Dol = “$”
Case “dollars”: Dol = “$”

Case “and”: Result = Result * 100
Case “cents”: Result = Result / 100
Case “hundred”:
If Result > 1000 Then
lastval = CInt(Mid(CStr(Result), Len(CStr(Result)), 1))
Result = (Result – lastval) + (lastval * 100)
Else
Result = Result * 100
End If
Case “thousand”:

If Result > 10000 Then

lastval = CInt(Mid(CStr(Result), Len(CStr(Result)), 1))
Result = (Result – lastval) + (lastval * 1000)
Else
Result = Result * 1000
End If
‘result = result + (result Mod 1000) * 1000 – result Mod 1000
Case “million”:

If Result > 100000 Then

lastval = CInt(Mid(CStr(Result), Len(CStr(Result)), 1))
Result = (Result – lastval) + (lastval * 10000)
Else
Result = Result * 10000
End If
‘result = result + (result Mod 1000000) * 1000000 – result Mod 1000000
Case “billion”:

If Result > 1000000 Then

lastval = CInt(Mid(CStr(Result), Len(CStr(Result)), 1))
Result = (Result – lastval) + (lastval * 100000)
Else
Result = Result * 100000
End If
‘result = result + (result Mod 1000000000) * 1000000000 – result Mod 1000000000
End Select

Next i

‘ Return the final numeric value
ConvertToNumber = Dol & Result
End Function

See? Piece of cake. Now all you have to do is type into a cell “=SpellOutNumber(“, click on the cell that you want to spell out, end the formula with a “)”, and hit enter. Boom! Your number has been spelled out for you.

Let’s try an example: type in 123, and watch it transform into “One Hundred Twenty-Three.” Pretty cool, huh? It’s like magic, except it’s Excel.

What if you want to change it back? Don’t worry, it’s just as easy. Same steps as before, but this time type in “=ConvertToNumber(” instead. Follow the same steps, and voila! Your 123 is right back where it started.

So, there you have it, my friend. Spelling out numbers in Excel doesn’t have to be a pain in the neck. With the right tools, anything is possible. And remember, CelTools is always here to make your Excel experience a little smoother. Give it a try, and let us know what you think.