Converting numbers into words

shimaa01234

shimaa01234

Active Member
Joined
Jun 24, 2014
Messages
446
Hi,

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

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

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
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 :eek:)

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.
 
Upvote 0
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
instead of
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question
Request help in extracting mutiple number groups from specific character positions from a varying number size.
Replies
3
Views
122
Fluff
Fluff
G
  • Question
Transpose tables sorted by performance order
Replies
1
Views
120
jimrward
jimrward
C
  • Solved
The Numbered Week of the Month
Replies
5
Views
293
lrobbo314
lrobbo314
T
  • Solved
Convert several columns of data into two distinct columns
Replies
3
Views
130
Fluff
Fluff
Steviemac
  • Question
Random Number Array - Select with criteria
Replies
0
Views
283
Steviemac
Steviemac

Forum statistics

Threads
1,214,645
Messages
6,120,711
Members
448,984
Latest member
foxpro

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top