convert numbers into words with no currency

K

Kathy Garcia

New Member
Joined
Nov 25, 2022
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
How do you convert numbers into words when there is no currency? I am using VBA, and deleting the dollars results in an error.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Kathy, you can try the below formula with the number in cell A1 and copy as required :

Excel Formula: 
=IF(A1<1,PROPER(IF(LEN(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2))=1,CHOOSE(1*LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2),"ten","twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&" Cents","")&CONCATENATE(CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen")&" Cents","twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine")&" Cents",IF(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)="01","one Cents",IF(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),1)="0",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine")&" Cents",""))))),IF(OR(LEN(FLOOR(A1,1))=13,FLOOR(A1,1)<=0),"Out of range",PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),1,1)+1,"","one hundred And ","two hundred And ","three hundred And ","four hundred And ","five hundred And ","six hundred And ","seven hundred And ","eight hundred And ","nine hundred And "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),3,1)+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(A1>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),4,1)+1,"","one hundred And ","two hundred And ","three hundred And ","four hundred And ","five hundred And ","six hundred And ","seven hundred And ","eight hundred And ","nine hundred And "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),9,1)+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),7,3))," thousAnd ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),10,1)+1,"","one hundred And ","two hundred And ","three hundred And ","four hundred And ","five hundred And ","six hundred And ","seven hundred And ","eight hundred And ","nine hundred And "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," "))&IF(ISERROR(FIND(".",A1,1)),""," And "&PROPER(IF(LEN(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2))=1,CHOOSE(1*LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2),"ten","twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&" Cents","")&CONCATENATE(CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen")&" Cents","twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine")&" Cents",IF(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)="01","one Cent",IF(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),1)="0",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine")&" Cents",""))))))))
 
Upvote 0
Kathy Garcia said:
How do you convert numbers into words when there is no currency? I am using VBA, and deleting the dollars results in an error.
Click to expand...

Or a function that you can paste into a standard code module and call like this from a cell:

=fncWordNum(1989)

This function puts the word 'and' in all of the appropriate places.

VBA Code: 
Public Function fncWordNum(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
        fncWordNum = "Value too large"
        Exit Function
    End If

    Call 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 "
 fncWordNum = Trim(Temp2 & Temp1)
End If
If n = 2 Then fncWordNum = Trim(Temp2 & Temp1 & " thousand " & fncWordNum)
If n = 1 Then fncWordNum = Trim(Temp2 & Temp1 & " million " & fncWordNum)

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) Then
         
         Temp1 = " point"
         
         For n = DecimalPosition + 1 To Len(NumStr)
            Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1)))
        Next n
     
         fncWordNum = fncWordNum & Temp1
    
    End If

    If Len(fncWordNum) = 0 Or Left(fncWordNum, 2) = " p" Then
     
        fncWordNum = "Zero" & fncWordNum
    
    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
 
Upvote 0
Herakles said:
Or a function that you can paste into a standard code module and call like this from a cell:

=fncWordNum(1989)

This function puts the word 'and' in all of the appropriate places.

VBA Code: 
Public Function fncWordNum(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
        fncWordNum = "Value too large"
        Exit Function
    End If

    Call 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 "
 fncWordNum = Trim(Temp2 & Temp1)
End If
If n = 2 Then fncWordNum = Trim(Temp2 & Temp1 & " thousand " & fncWordNum)
If n = 1 Then fncWordNum = Trim(Temp2 & Temp1 & " million " & fncWordNum)

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) Then
        
         Temp1 = " point"
        
         For n = DecimalPosition + 1 To Len(NumStr)
            Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1)))
        Next n
    
         fncWordNum = fncWordNum & Temp1
   
    End If

    If Len(fncWordNum) = 0 Or Left(fncWordNum, 2) = " p" Then
    
        fncWordNum = "Zero" & fncWordNum
   
    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
Click to expand...
Please ignore the code above and use this code.

VBA Code: 
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

Public Function fncWordNum(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
        fncWordNum = "Value too large"
        Exit Function
    End If

    Call 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 "
 fncWordNum = Trim(Temp2 & Temp1)
End If
If n = 2 Then fncWordNum = Trim(Temp2 & Temp1 & " thousand " & fncWordNum)
If n = 1 Then fncWordNum = Trim(Temp2 & Temp1 & " million " & fncWordNum)

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) Then
         
         Temp1 = " point"
         
         For n = DecimalPosition + 1 To Len(NumStr)
            Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1)))
        Next n
     
         fncWordNum = fncWordNum & Temp1
    
    End If

    If Len(fncWordNum) = 0 Or Left(fncWordNum, 2) = " p" Then
     
        fncWordNum = "Zero" & fncWordNum
    
    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
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Vstack, Chooserows & Dollar
2
Replies
18
Views
401
Nathan Asius
N
peterv6
  • Question
Currency comparison problem
Replies
1
Views
139
awoohaw
awoohaw
E
  • Question
How to Keep both Dollars and Cents in Formatted Output?
Replies
2
Views
90
Eternallamp
E
C
  • Question
How do I Convert a number to text?
Replies
8
Views
300
ExceLoki
ExceLoki
A
  • Question
Need to convert value depending on currency and date
Replies
2
Views
156
deLockloire
D

Forum statistics

Threads
1,215,092
Messages
6,123,063
Members
449,090
Latest member
fragment

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