Previous Topic Next Topic

WordNum help needed

Solved/Closed
april_tat Posts 1 Registration date Tuesday May 20, 2014 Status Member Last seen May 20, 2014 - May 20, 2014 at 01:38 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 19, 2015 at 12:16 PM
Hello,

I hope somebody can help.
I use WordNum to spell the result of a formula and it gives me 4 digits (spelled) after the point. I need just 2 digits.

Example:

=RC[-1]*18%
11.37 2.05 Eleven point Three Seven Two point Zero Four Six Six

Is there any way around it?

Please help!

2 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
May 22, 2014 at 11:11 AM
Hi April,

So you display the value 2.0466 with 2 decimals which looks like 2.05.

When you use the function ROUND, the value will change to 2.05 instead of just visually look like 2.05.

Or you can look for the code line:
For n = DecimalPosition + 1 To Len(NumStr)
and replace it with:
For n = DecimalPosition + 1 To 4

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
0
April
May 22, 2014 at 03:10 PM
Hi, Trowa,

Thank you very much for suggestion. I tried to change the code, but for some reason it did not work :( .So I used Round function and it is fine. I really appreciate your advise!

April
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
May 26, 2014 at 10:43 AM
Hi April,

Good to see you worked it out.

I noticed that the code adjustment only works if you have 1 number before the comma.
Changing it to:
For n = DecimalPosition + 1 To DecimalPosition + 2
should do the trick.

Bet regards,
Trowa
0
april
May 27, 2014 at 01:37 PM
Hi Trowa,

Thank you for your help. I applied the changes and it works fine.

Do you mind me asking, - at work I have to deal with paperwork in different currencies and this macros is handy, because it does not have any particular currencies, but not the best solution, altought, I have to say it is not the best way to put in a document "Eleven point Three Seven" , it will sound nicer if I could do "Eleven dollars and Thirty Seven cents" and I know you can use SpellNimber macros for it. But because I have to use different currencies (not just USD) in different documents it is not very convenient. I was wondering, if there is a macro, which can be used for selected currencies (EUR. GBP) ? I just thought if you might suggest anything.

Once again, thank you for your help with my previous question.

April
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jun 2, 2014 at 12:20 PM
Hi April,

Depending on how much currencies you work with, you can alter the macro and giving it a different name. Just look for the part in the code where the dollar and cents references are and change them.


Another possibility could be to use this formula structure:
=wordnum(INT(B1))&" "&A1& " "&wordnum(RIGHT(B1,2))&" "&A2

Here the value is located in B1.
Column A will be used for the different currencies.
Then you can either change the value in cell A1 and A2 or
change the cell references in the formula (and place "Dollar and" in A3 and "cents" in A4 for example) to match the desired currency.
Example:
B1: 2500.25
A1: Euro and
A2: Eurocents
Result will be: Two thousand Five hundred Euro and Twenty Five Eurocents

Best regards,
Trowa
0
EllTee Posts 1 Registration date Monday March 16, 2015 Status Member Last seen March 16, 2015 > TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022
Mar 16, 2015 at 06:36 AM
Good day TrowaD,
I thank you for the wordnum code and the currency formula you gave April, however, where the cents are 10, 20 etc, the code/formula returns "two cents" instead of "twenty cents". Also when the number is in the millions, the last two Euro digits are returned twice, as euros and as cents. Please help with this as this will be most useful for the routine I'm trying to build.

Reagrds,
EllTee.
0
Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Mar 19, 2015 at 12:16 PM
Hi Elltee,

Here is an altered version of the WordNum function, which tackles the issues you came across: 
Option Explicit
Public Numbers As Variant, Tens As Variant

Sub SetNums()
Numbers = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
End Sub

Function WordNum(MyNumber As Double) As String
Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String
Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String
' This macro was written by Chris Mead - www.MeadInKent.co.uk

If Abs(MyNumber) > 999999999 Then
    WordNum = "Value too large"
    Exit Function
End If

SetNums

' String representation of amount (excl decimals)
NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9)
ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3)))

For n = 3 To 1 Step -1  'analyse the absolute number as 3 sets of 3 digits
    StrNo = Format(ValNo(n), "000")
    
    If ValNo(n) > 0 Then
        Temp1 = GetTens(Val(Right(StrNo, 2)))
        
        If Left(StrNo, 1) <> "0" Then
            Temp2 = Numbers(Val(Left(StrNo, 1))) & " hundred"
            If Temp1 <> "" Then Temp2 = Temp2 & " and "
        Else
            Temp2 = ""
        End If
    
        If n = 3 Then
            If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = "and "
            WordNum = Trim(Temp2 & Temp1)
        End If
        
        If n = 2 Then WordNum = Trim(Temp2 & Temp1 & " thousand " & WordNum)
        If n = 1 Then WordNum = Trim(Temp2 & Temp1 & " million " & WordNum)
    End If
Next n

NumStr = Trim(Str(Abs(MyNumber)))

' Values after the decimal place
DecimalPosition = InStr(NumStr, ".")
Numbers(0) = "Zero"
If DecimalPosition > 0 And DecimalPosition < Len(NumStr) And Len(NumStr) - DecimalPosition = 1 Then
    Temp1 = " point"
    Temp1 = Temp1 & " " & Tens(Val(Mid(NumStr, DecimalPosition + 1, 2)))
    WordNum = WordNum & Temp1
End If

If DecimalPosition > 0 And DecimalPosition < Len(NumStr) And Len(NumStr) - DecimalPosition = 2 Then
    Temp1 = " point"
    Temp1 = Temp1 & " " & GetTens(Val(Mid(NumStr, DecimalPosition + 1, 2)))
    WordNum = WordNum & Temp1
End If


If Len(WordNum) = 0 Or Left(WordNum, 2) = " p" Then
    WordNum = "Zero" & WordNum
End If
End Function

Function GetTens(TensNum As Integer) As String
' Converts a number from 0 to 99 into text.
If TensNum <= 19 Then
    GetTens = Numbers(TensNum)
Else
    Dim MyNo As String
    MyNo = Format(TensNum, "00")
    GetTens = Tens(Val(Left(MyNo, 1))) & " " & Numbers(Val(Right(MyNo, 1)))
End If
End Function


Best regards,
Trowa
0