- Click Tools->Macro->Visual Basic Editor
- Insert a module
- Paste the following code:

'****************

' 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(

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!

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

hello!

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

thanks!

Could you please help me modify?

120.50 = ONE HUNDRED TWENTY AND CENTS FIFTY ONLY.

THANK YOU VERY MUCH!

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

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.

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

hi fiShBoN3,

cannot add the text 'only.' at the last sentence?

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

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.

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" & DecimalTextto" and " & DecimalText & " cents only"hi fiShBoN3,

if 123.05

it show 'one hundred twenty three and cents fifty five only'

it didn't show 'cents five only'

Great Code, Just Perfect! Thank You

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

Hello!

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

THANKS

Hello!

How can I program, 100 to ONE HUNDRED ONLY?

Thanks