Convert numbers to words with VBA

Question

I have a column of numbers. In the next column, I want the text/word conversion of the numbers.

Example: 123.561 would convert to One hundred twenty three point five six one.

I do not want to convert to currency, just number to text, with any number of decimal places.

How can I do this?

How big is the biggest number? thousands? millions? trillions? — ashleedawg, Jul 06 '18 at 06:36
If you had googled before you would have found out that Microsoft provides a solution for that: [How to convert a numeric value into English words in Excel](https://support.microsoft.com/en-us/help/213360/how-to-convert-a-numeric-value-into-english-words-in-excel) — Pᴇʜ, Jul 06 '18 at 06:37
@Pᴇʜ I searched this mate, but this only works for 2 decimal places. I want for more than 2. Also How can i make this work as a macro on click of a button in the ribbon.? — Aman Devrath, Jul 06 '18 at 06:54
@aman - check out the modified procedure. It no longer lists dollars or cents, and includes unlimited decimal places. — ashleedawg, Jul 06 '18 at 07:14

score 14 · Accepted Answer · edited Jul 08 '18 at 04:16

14

Edit: I've adapted the procedure below to non-currency, unlimited decimal places.

Edit 2 considers internationalisation via two changes in (1) Function SpellNumber and (2) Function fractionWords to make code work with other decimal separators (e.g. colon in middle Europe) ' - see comment

Example:

MsgBox SpellNumber(2123.4575)

...returns:

Two Thousand One Hundred Twenty Three point Four Five Seven Five

Paste the following code into a new module:

Option Explicit

Function SpellNumber(ByVal numIn)
    Dim LSide, RSide, Temp, DecPlace, Count, oNum
    oNum = numIn
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "
    numIn = Trim(Str(numIn)) 'String representation of amount
    ' Edit 2.(0)/Internationalisation
    ' Don't change point sign here as the above assignment preserves the point!
    DecPlace = InStr(numIn, ".") 'Pos of dec place 0 if none 
    If DecPlace > 0 Then 'Convert Right & set numIn
        RSide = GetTens(Left(Mid(numIn, DecPlace + 1) & "00", 2))
        numIn = Trim(Left(numIn, DecPlace - 1))
    End If
    RSide = numIn
    Count = 1
    Do While numIn <> ""
        Temp = GetHundreds(Right(numIn, 3))
        If Temp <> "" Then LSide = Temp & Place(Count) & LSide
        If Len(numIn) > 3 Then
            numIn = Left(numIn, Len(numIn) - 3)
        Else
            numIn = ""
        End If
        Count = Count + 1
    Loop

    SpellNumber = LSide
    If InStr(oNum, Application.DecimalSeparator) > 0 Then    ' << Edit 2.(1) 
        SpellNumber = SpellNumber & " point " & fractionWords(oNum)
    End If

End Function

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

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

Function GetDigit(Digit) 'Converts a number from 1 to 9 into text
    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

Function fractionWords(n) As String
    Dim fraction As String, x As Long
    fraction = Split(n, Application.DecimalSeparator)(1)   ' << Edit 2.(2)
    For x = 1 To Len(fraction)
        If fractionWords <> "" Then fractionWords = fractionWords & " "
        fractionWords = fractionWords & GetDigit(Mid(fraction, x, 1))
    Next x
End Function

^{(Adapted from the Source: Microsoft)}

There are several other examples online. You may have had couple finding them if you were searching for "convert numbers to text" since that implies changing the data type. A better search term would be "vba convert numbers to words".

edited Jul 08 '18 at 04:16

T.M.

9,436
3
33
57

answered Jul 06 '18 at 06:38

ashleedawg

20,365
9
72
105

2

You'll get better performance by using the `String` returning functions `Left$`, `Right$`, `Mid$` and `Trim$`. The `Val` function should be avoided, as it is not locale aware and will return erroneous values in some regions - better to use `CDbl` or `CLng` as appropriate. Also, make the return type of `SpellNumber` an explicit `String` and strongly type all of your variables and helper functions. And finally, it's better to use `vbNullString` than `""`. – ThunderFrame Jul 06 '18 at 06:49
I said in my question, there can be any number of decimal places. Be it 0 decimal places or 5 decimal places. I found this answer, but it only focuses on 2 decimal places and also I want this code to work as macro from the ribbon. – Aman Devrath Jul 06 '18 at 06:53
@AmanDevrath I modified the procedure. See the example, and try it out. – ashleedawg Jul 06 '18 at 07:06
@ThunderFrame - interesting, solid advice although IMO kinda overkill in this case. Firstly I didn't write the code, and if I was to revamp it I'd have other concerns like data type declaration. I'd be interested to see any documentation indicating that `Left$` is any more efficient than `Left` (especially on a sub that will hardly be used), and especially curious to see any official documentation showing that a constant that is 7 times as many characters as literal `""` is any "better". Regardless, many areas of coding are a matter of personal preference. You could post an alternative answer. – ashleedawg Jul 06 '18 at 07:13
1

@ashleedawg the only difference between `""` and `vbNullString` is 6 bytes of memory: [According to this comparison](http://www.xtremevbtalk.com/tutors-corner/26717-difference-vbnullstring.html) `""` takes 6 bytes of memory where `vbNullString` takes 0 bytes. So if you don't care about that 6 bytes I think that's not a point of concern. – Pᴇʜ Jul 06 '18 at 07:20
@AmanDevrathTo make it work from the ribbon you can work with this: https://stackoverflow.com/questions/8850836/how-to-add-a-custom-ribbon-tab-using-vba – Jul 06 '18 at 07:30
1

@Pᴇʜ - I'm afraid there's a hole in that logic: indeed 6 bytes of variable storage memory is saved, but the code required to type that word takes an extra 10 bytes of code every time you use it. Similar idea to why `Integer` shouldn't be used. It takes 2 bytes less storage, which are wasted by the 2 bytes required by the 2 extra keystrokes. (Also, no, I am not concerned about the 6 bytes.) – ashleedawg Jul 06 '18 at 07:57
2

@ashleedawg Well, storage is cheap, memory is expensive. Also in most cases you have a lot more storage than memory available. So I would always prefer optimization in memory and speed vs optimization in storage. Code length should never be a concern (if its length optimizes memory or speed). I never heard of anyone who run out of storage because of a VBA code, but I have heard a lot running out of memory! *(But this is getting off-topic now)* – Pᴇʜ Jul 06 '18 at 08:03
1

@Pᴇʜ - actually, I took another look at that link and it's incorrect, at least now. i'm using Office 365 64-bit and running the test on that page shows that `vbNullString` and `""` take the same storage space for the variable, so that means vbNullString` is wasteful of the extra 10 bytes of code storage space. Even if you were saving that 6 bytes, that would take an awful lot of nullstrings to fill up a GB (or KB for that matter). *I will agree with you that we're off-topic. We'll find something worth arguing next time.* – ashleedawg Jul 06 '18 at 08:10
@AmanDevrath - Glad to hear it works. In retrospect, I made a mistake .. Instead of `dot` technically the word should be **`point`**. "Dot" is more of a programming reference. (I'm surprised nobody pointed *that* out to me!) – ashleedawg Jul 06 '18 at 08:30
Actually, in my case dot works, as my client is satisfied with it. :P But yeah, I'll try to convince him to use `point` as it is not good to use to `dot`. Learned something new. Thanx again. – Aman Devrath Jul 06 '18 at 08:41
3

@ashleedawg the length of the plaintext code is irrelevant. The code text gets converted to p-code, is then interpreted and then run as exe-code. What matters is the bytes that are used during execution. `vbNullString` is 0 bytes and doesn't require a memory address be created, filled and destroyed each time a string literal like `""` is encountered, so it uses less memory while running, and it runs more quickly. The Rubberduck VBA add-in (to which I'm a contributor), will identify and fix these performance improvement opportunities. – ThunderFrame Jul 06 '18 at 14:06
3

@ashleedawg The `Left` function is actually an alias for the hidden function `_B_var_Left` and is designed to both accept and return a `Variant`, whereas the `Left$` function is actually an alias for the `_B_str_Left` function and is designed to both accept and return a `String`. If your code is passing in and/or expecting a `String` in return, then your code will run more efficiently, as you won't encounter the implicit conversions from `String` to `Variant`, and then `Variant` back to `String` on *every* call to functions like `Left`, `Right`, `Mid`, `Trim` and others. – ThunderFrame Jul 06 '18 at 14:10
1

@ThunderFrame - Interesting ...that's a VBA add-In? Where might one find it? I was unaware of the differences with `Left`/`Right`/`Mid`/`Trim`, however as for the handling of literal `""` I'm not convinced the information is up to date. For example, the [demo shared above](http://www.xtremevbtalk.com/tutors-corner/26717-difference-vbnullstring.html) did not produce the result for me as the author described (and is based on VBA 2-decades ago, ie., no 64bit installs). The changes are obviously still irrelevant to this question but you obviously know your stuff, thanks for taking the time. :-) – ashleedawg Jul 06 '18 at 14:22
2

@ashleedawg [Rubberduck's home](http://www.rubberduckvba.com/). Regarding the `""` vs. `vbNullString` debate: Another matter to consider is the programmer's *intent* (/code clarity) - `vbNullString` is much better in conveying the intention: Yes, I really want to have a zero-length string here - I did not accidentally forget to put meaningful characters between the quotes. It essentially eliminates one source of misunderstanding/error. – Inarion Jul 06 '18 at 14:34
1

@ashleedawg If I compare the timings of repeated calls to `If myString = vbNullString Then` and `If myString = "" Then` then the `vbNullString` comparison is about 5% faster. than the `""` comparison But more convincingly, if I fill an array with `vbNullString` and again with `""`, the `vbNullString` approach is **7 times faster** than the `""` approach. – ThunderFrame Jul 06 '18 at 14:38
@Inarion - IMHO that argument is a bit of a stretch, I can't say I've ever written or seen code where someone "forgot to put something between the quotes"... but thank you for the input anyhow, I always appreciate other's point of view. ...and thank you for the link. – ashleedawg Jul 06 '18 at 16:00
1

@ThunderFrame - I ran a test , the difference wasn't quite as significant on my machine (*3x faster* when filling and actually *13% **slower*** with `If`/comparison) but nonetheless more of a difference I was not expecting. Interesting for argument's sake (unfortunately little/no practical difference since that's not something I use *that* often.) - However, I can see the text functions making a noticeable difference though. Thanks again for sharing your knowledge. – ashleedawg Jul 06 '18 at 16:06
Two changes to make above code work with other **decimal separators** (e.g. colon in middle Europe) **[1]** Last condition in Function SpellNumber: `If InStr(oNum, Application.DecimalSeparator) > 0 Then` ***) see note below ** [2]** First assignment in Function fractionWords: `fraction = Split(n, Application.DecimalSeparator)(1)` *)Note: the assignment `DecPlace = InStr(numIn, ".")` must not to be changed, as the prior assignment `numIn = Trim(Str(numIn))` preserves the point.* – T.M. Jul 07 '18 at 14:59
1

@T.M. good thinking,, sometimes I don't think about internationalization (and Excel's [**List of `Application.International` properties**](https://msdn.microsoft.com/vba/excel-vba/articles/application-international-property-excel).) Feel free to [edit] my answer if you like! (assuming your change is tested). – ashleedawg Jul 07 '18 at 15:09
@ashleedawg, this is really awesome, although I've found it is not working properly for decimals with any zero directly after decimal point, like 123.01 (instead of _"point Zero One"_ it provides _"point One"_). I've added `Case 0: GetDigit = "Zero"` to `Function GetDigit` and it works fine now. – Bartek Nowakowski Jul 30 '20 at 08:52

Convert numbers to words with VBA

1 Answers1

Example:

Linked