# Converting numbers into words

#### shimaa01234

##### Active Member
Hi,

How can i convert this numbers into words....
 1 First 2 Second 3 Third 4 Fourth 5 fifth 6 sixth . . . . . . . . . . . . . . . . . . 3000 Three thousand

<colgroup><col><col></colgroup><tbody>
</tbody>

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### BiocideJ

##### Well-known Member
OK, based on the link Scott shared, this should be a variation that gives you the ORDINAL numbers like you requested. In the testing I've done it appears to work and SHOULD be good for all numbers from 1 - 999,999,999,999,999 (First to Nine Hundred Ninety Ninth Trillion Nine Hundred Ninety Ninth Billion Nine Hundred Ninety Ninth Million Nine Hundred Ninety Ninth Thousand Nine Hundred Ninety Ninth )

pasted into a module and called from an Excel field with =SpellOrdinal(A1)

Code:
``````Option Explicit
'Main Function
Function SpellOrdinal(ByVal MyNumber)
Dim OrdinalNum, Temp
Dim 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.

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

SpellOrdinal = Trim(Replace(OrdinalNum, " th", "th"))
End Function

' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber, Optional ForceOrdinal As Boolean = False)
Dim Result As String
If Val(MyNumber) = 0 And Not ForceOrdinal 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), ForceOrdinal)
Else
If ForceOrdinal Then
Result = Result & GetOrdinalDigit(Mid(MyNumber, 3))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
End If
If Result = "" And ForceOrdinal Then Result = "th"
GetHundreds = Result
End Function

' Converts a number from 10 to 99 into text.
Function GetTens(TensText As String, ForceOrdinal As Boolean)
Dim Result As String
Result = ""           ' Null out the temporary function value.
If ForceOrdinal Then
Select Case Val(TensText)
Case 10: Result = "Tenth"
Case 11: Result = "Eleventh"
Case 12: Result = "Twelfth"
Case 13: Result = "Thirteenth"
Case 14: Result = "Fourteenth"
Case 15: Result = "Fifteenth"
Case 16: Result = "Sixteenth"
Case 17: Result = "Seventeenth"
Case 18: Result = "Eighteenth"
Case 19: Result = "Nineteenth"
Case 20: Result = "Twentieth"
Case 30: Result = "Thirtieth"
Case 40: Result = "Fortieth"
Case 50: Result = "Fiftieth"
Case 60: Result = "Sixtieth"
Case 70: Result = "Seventieth"
Case 80: Result = "Eightieth"
Case 90: Result = "Ninetieth"
Case Else
End Select
If Result = "" Then
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 & GetOrdinalDigit(Right(TensText, 1))  ' Retrieve ones place.
End If
Else
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
If Result = "" Then
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 & GetOrdinalDigit(Right(TensText, 1))  ' Retrieve ones place.
End If
End If
GetTens = Result
End Function

' Converts a number from 1 to 9 into text.
Function GetOrdinalDigit(Digit)
Select Case Val(Digit)
Case 1: GetOrdinalDigit = "First"
Case 2: GetOrdinalDigit = "Second"
Case 3: GetOrdinalDigit = "Third"
Case 4: GetOrdinalDigit = "Fourth"
Case 5: GetOrdinalDigit = "Fifth"
Case 6: GetOrdinalDigit = "Sixth"
Case 7: GetOrdinalDigit = "Seventh"
Case 8: GetOrdinalDigit = "Eighth"
Case 9: GetOrdinalDigit = "Ninth"
Case Else: GetOrdinalDigit = ""
End Select
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``````

I have tested this moderately.

#### shimaa01234

##### Active Member
Fantastic Cod, Mister "BiocideJ"
Thank you very much

Last edited:

#### BiocideJ

##### Well-known Member
Overnight, I realized I had made a minor error in my logic.

999,999,999,999,999 should be
Nine Hundred Ninety Nine Trillion Nine Hundred Ninety Nine Billion Nine Hundred Ninety Nine Million Nine Hundred Ninety Nine Thousand Nine Hundred Ninety Ninth
Nine Hundred Ninety Ninth Trillion Nine Hundred Ninety Ninth Billion Nine Hundred Ninety Ninth Million Nine Hundred Ninety Ninth Thousand Nine Hundred Ninety Ninth

This corrected code resolves that problem.
Code:
``````Option Explicit
'Main Function
Function SpellOrdinal(ByVal MyNumber)
Dim OrdinalNum, Temp
Dim 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.

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

SpellOrdinal = Trim(Replace(OrdinalNum, " th", "th"))
End Function

' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber, Optional ForceOrdinal As Boolean = False)
Dim Result As String
If Val(MyNumber) = 0 And Not ForceOrdinal 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), ForceOrdinal)
Else
If ForceOrdinal Then
Result = Result & GetOrdinalDigit(Mid(MyNumber, 3))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
End If
If Result = "" And ForceOrdinal Then Result = "th"
GetHundreds = Result
End Function

' Converts a number from 10 to 99 into text.
Function GetTens(TensText As String, ForceOrdinal As Boolean)
Dim Result As String
Result = ""           ' Null out the temporary function value.
If ForceOrdinal Then
Select Case Val(TensText)
Case 10: Result = "Tenth"
Case 11: Result = "Eleventh"
Case 12: Result = "Twelfth"
Case 13: Result = "Thirteenth"
Case 14: Result = "Fourteenth"
Case 15: Result = "Fifteenth"
Case 16: Result = "Sixteenth"
Case 17: Result = "Seventeenth"
Case 18: Result = "Eighteenth"
Case 19: Result = "Nineteenth"
Case 20: Result = "Twentieth"
Case 30: Result = "Thirtieth"
Case 40: Result = "Fortieth"
Case 50: Result = "Fiftieth"
Case 60: Result = "Sixtieth"
Case 70: Result = "Seventieth"
Case 80: Result = "Eightieth"
Case 90: Result = "Ninetieth"
Case Else
End Select
If Result = "" Then
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 & GetOrdinalDigit(Right(TensText, 1))  ' Retrieve ones place.
End If
Else
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
If Result = "" Then
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
If ForceOrdinal Then
Result = Result & GetOrdinalDigit(Right(TensText, 1))  ' Retrieve ones place.
Else
Result = Result & GetDigit(Right(TensText, 1))  ' Retrieve ones place.
End If
End If
End If
GetTens = Result
End Function

' Converts a number from 1 to 9 into text.
Function GetOrdinalDigit(Digit)
Select Case Val(Digit)
Case 1: GetOrdinalDigit = "First"
Case 2: GetOrdinalDigit = "Second"
Case 3: GetOrdinalDigit = "Third"
Case 4: GetOrdinalDigit = "Fourth"
Case 5: GetOrdinalDigit = "Fifth"
Case 6: GetOrdinalDigit = "Sixth"
Case 7: GetOrdinalDigit = "Seventh"
Case 8: GetOrdinalDigit = "Eighth"
Case 9: GetOrdinalDigit = "Ninth"
Case Else: GetOrdinalDigit = ""
End Select
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``````

#### shimaa01234

##### Active Member
Thank you very much Mister "BiocideJ" , for this clarification

Replies
4
Views
223
Replies
0
Views
425
Replies
1
Views
202
Replies
5
Views
167
Replies
3
Views
287

1,191,501
Messages
5,986,923
Members
440,067
Latest member
Swatts1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back