6 thoughts on “How to Convert Numbers into Words

  1. Excel Geek

    Hi

    You will need to create a custom Microsoft Excel function that will convert numeric currency values into words for you. Microsoft have published the VBA for this and I have tweaked it to so that it is compatible with UK currency.

    The code below details what you need to enter into a new module in the VBA editor. As I said this is Microsoft code that I have tweaked to be UK compatible.

    Option Explicit

    '****************
    ' Main Function *
    '****************

    Function SpellNumber(ByVal MyNumber)

    Dim Pounds, Pence, Temp

    Dim DecimalPlace, Count

    'Dim GetTens, GetHundreds

    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

    Pence = GetTens(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 Pounds = Temp & Place(Count) & Pounds

    If Len(MyNumber) > 3 Then

    MyNumber = Left(MyNumber, Len(MyNumber) - 3)

    Else

    MyNumber = ""

    End If

    Count = Count + 1

    Loop

    Select Case Pounds
    Case ""
    Pounds = "No Pounds"
    Case "One"
    Pounds = "One Pound"
    Case Else
    Pounds = Pounds & " Pounds"
    End Select

    Select Case Pence
    Case ""
    Pence = " and No Pence"
    Case "One"
    Pence = " and One Pence"
    Case Else
    Pence = " and " & Pence & " Pence"
    End Select

    SpellNumber = Pounds & Pence

    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

    Once you have the code above in module it is fairly simple to use. Either type in a cell =spellnumbner(55.30) will produce Fifty Five Pounds and Thirty Pence. Just change the value inside the brackets to that which you want spelt. Or,if there is a currency value already entered in a cell, eg cell A1, then type =spellnumber(A1). Again swap the cell reference inside the brackets to that of the cell you wish to reference.

    I hope this helps! You can also download the Excel file containing the VBA code to convert numeric values to words.

    Thanks for writing and please do recommend Excel Geek.

  2. Dave Cheesman

    Unfortunately, the code doesn’t catch that unique (non-US) habit we brits have of leaving the ‘and’ out of a hundreds number if the tens and units are zero. For example, £200.67 is ‘two hundred pounds and sixty seven pence’, whereas £234.67 is ‘two hundred AND thirty four pounds and sixty seven pence. I’ve tried to modify this code to catch this exception but so far all it has done is drive me nuts!!

  3. andrew walker Post author

    Hey, thanks for writing. Try this modification… it should put the ‘and’ in for you. 🙂

    Navigate down the code to where these five lines are. It starts ' Convert the hundreds place..

    ' Convert the hundreds place.
    If Mid(MyNumber, 1, 1) <> "0" Then
    Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred and "
    End If

    Then replace those five lines above with the code below. 🙂

    ' Convert the hundreds place.
    If Mid(MyNumber, 1, 1) <> "0" Then
    Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred and "
    End If

    If Mid(MyNumber, 2, 2) = "00" Then
    'Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If

    I hope this helps!

  4. Peter Smith

    Hey Guys,

    Sorry to latch on to your thread but hoping you can help as I’m stuck.

    Thanks for the above, i’ve used your VBA above for the correct SpellNumber in UK currency and also edited it to correct the English preference to include “and” after the hundred number.

    Another habit I’ve tried to edit by myself but can’t get it to work is to include comma’s after the trillion, billion, million and thousand.

    Using the above VBA the number “1234567891.23” would appear as:

    “One Billion Two Hundred and Thirty Four Million Five Hundred and Sixty Seven Thousand Eight Hundred and Ninety One Pounds and Twenty Three Pence”

    Does anyone know how to edit the VBA or can issue a revised VBA so that the above figure and any edit would

    “One Billion, Two Hundred and Thirty Four Million, Five Hundred and Sixty Seven Thousand, Eight Hundred and Ninety One Pounds and Twenty Three Pence”

    Any help would be very much appreciated!

    Regards,

    Peter

  5. Michael

    What if I want to add hyphen in the range between (21 and 99) ?

    EX:
    1872
    One Thousand Eight Hundred Seventy-Two
    1920
    One Thousand Nine Hundred Twenty

  6. Daniel Kwame Dadzie

    Hi can any one me to write 1020 as “one thousand and twenty” please.

Leave a Reply

Your email address will not be published. Required fields are marked *