Number to Text in Excel

I was asked today by one of my classmates about converting numeric values to literal values in excel. I thought there was a function for this but I found none. Well I was curious so I looked it up on the web. I've done a little modification here to make it more or less generic (specially when writing in checks). Here's how it's done:
  1. Click Tools->Macro->Visual Basic Editor
  2. Insert a module
  3. Paste the following code:
Option Explicit
'****************
' Main Function *
'****************

Function SpellNumber(ByVal MyNumber)
Dim Temp, WholeNumberText, DecimalText
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

' String representation of amount.
MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
DecimalText = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then
WholeNumberText = Temp & Place(Count) & WholeNumberText
End If
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

Select Case DecimalText
Case ""
DecimalText = " and 0/100"
Case Else
DecimalText = " and " & DecimalText & "/100"

End Select
SpellNumber = WholeNumberText & DecimalText
End Function


'*******************************************
' Converts a number from 100-999 into text *
'*******************************************
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If

GetHundreds = Result
End Function

'*********************************************
' Converts a number from 10 to 99 into text. *
'*********************************************

Function GetTens(TensText)
Dim Result As String

Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function


'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function



4. Close and return to excel
5. To use the macro, write the following on the cell to which you want to display the text version of your numeric value: =SpellNumber()
refers to the cell where your number value is written.




Comments

16 Responses to "Number to Text in Excel"

Melanie Strom said... January 8, 2009 at 1:05 AM

This is exactly what I was looking for. However, I need to modify it slightly but really don't have the know-how.

I need to accommodate up to 4 decimal places. For example, I could have 118.57 (One Hundred Eighteen and 57/100) or I could have 520.4998 (Five Hundred Twenty and 4998/10,000). Could you please help me add/modify what is necessary? Thank you!

fiShBoN3 said... January 8, 2009 at 10:25 PM

Hi there!
I've made some modifications to handle your requirements. Please have a look at
http://rixxtech.blogspot.com/2009/01/number-to-text-in-excel-that-can-handle.html

tracy said... February 10, 2009 at 2:24 PM

hello!
can u explain more for the step number 5, i don't how how to use the macro.
thanks!

tracy said... February 10, 2009 at 2:37 PM

Could you please help me modify?
120.50 = ONE HUNDRED TWENTY AND CENTS FIFTY ONLY.
THANK YOU VERY MUCH!

fiShBoN3 said... February 13, 2009 at 11:34 PM

Hi tracy,

I'm sorry it took me long before I got back to you. To change the decimal portion to words, see my post at http://rixxtech.blogspot.com/2009/02/number-to-text-in-excel-in-currency.html

tracy said... February 16, 2009 at 7:49 AM

sorry, may u help me to modify again, I don't want to show the 'dollars' and I wish to show text 'only' at the last.
274.65
TWO HUNDRED SEVENTY FOUR AND CENTS SIXTY FIVE ONLY.

fiShBoN3 said... February 16, 2009 at 8:31 PM

Hi tracy,
Replace the code with the following:


Option Explicit
'****************
' Main Function *
'****************

Function SpellNumber(ByVal MyNumber)
Dim Temp, WholeNumberText, DecimalText
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

' String representation of amount.
MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
DecimalText = GetTens(Round(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2), 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then
WholeNumberText = Temp & Place(Count) & WholeNumberText
End If
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

Select Case DecimalText
Case ""
DecimalText = " and 0/100"
Case Else
DecimalText = " and cents " & DecimalText

End Select
SpellNumber = WholeNumberText & " " & DecimalText
End Function


'*******************************************
' Converts a number from 100-999 into text *
'*******************************************
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If

GetHundreds = Result
End Function

'*********************************************
' Converts a number from 10 to 99 into text. *
'*********************************************

Function GetTens(TensText)
Dim Result As String

Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function


'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function

tracy said... February 17, 2009 at 8:25 AM

hi fiShBoN3,
cannot add the text 'only.' at the last sentence?

fiShBoN3 said... February 17, 2009 at 10:14 PM

Hi tracy,

try this code:

Option Explicit
'****************
' Main Function *
'****************

Function SpellNumber(ByVal MyNumber)
Dim Temp, WholeNumberText, DecimalText
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

' String representation of amount.
MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
DecimalText = GetTens(Round(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2), 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then
WholeNumberText = Temp & Place(Count) & WholeNumberText
End If
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

Select Case DecimalText
Case ""
DecimalText = " and Cents Zero"
Case Else
DecimalText = " and Cents " & DecimalText

End Select
SpellNumber = WholeNumberText & " " & DecimalText
End Function


'*******************************************
' Converts a number from 100-999 into text *
'*******************************************
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If

GetHundreds = Result
End Function

'*********************************************
' Converts a number from 10 to 99 into text. *
'*********************************************

Function GetTens(TensText)
Dim Result As String

Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function


'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function

tracy said... February 18, 2009 at 7:46 AM

hi fiShBoN3,
still no the text 'Only.' at the last sentence.
add I found that if no cents, it will appear 'And Cents Zero'. can don't show out this.

fiShBoN3 said... February 22, 2009 at 4:29 PM

Hi tracy,

Sorry it took quite a long time before I got back to you. There's a portion on the code that says:


DecimalText = " and Cents Zero"
Case Else
DecimalText = " and Cents " & DecimalText



just change the " and Cents Zero" to "", then change the " and Cents" & DecimalText to " and " & DecimalText & " cents only"

tracy said... February 23, 2009 at 9:35 AM

hi fiShBoN3,
if 123.05
it show 'one hundred twenty three and cents fifty five only'
it didn't show 'cents five only'

Anonymous said... July 10, 2009 at 1:22 AM

Great Code, Just Perfect! Thank You

Anonymous said... June 16, 2010 at 10:15 AM

Hi Fishbon3
I am also having the same problem as the 123.05 does not read as five cents but instead as fifty five cents.
Need your great help.

Thanks so much =)

Yati

Anonymous said... August 14, 2010 at 10:45 AM

Hello!

How can I program e.g. 100 to HUNDRED ONLY?

THANKS

Anonymous said... August 14, 2010 at 10:49 AM

Hello!

How can I program, 100 to ONE HUNDRED ONLY?

Thanks