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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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

A
  • Question Question
calculation amounts with currency in textboxes on userform
Replies
2
Views
99
Abdo
A
M
  • Solved
Pull exchange data from another workbook
Replies
2
Views
89
Maniac189
M
A
  • Solved
Currency Conversion Formula
Replies
3
Views
106
6StringJazzer
6StringJazzer
N
  • Solved
Vstack, Chooserows & Dollar
2
Replies
18
Views
2K
Nathan Asius
N
R
  • Solved
Use checkboxes to convert formulas to values in multiple cells
Replies
2
Views
449
Rainmanne
R

Forum statistics

Threads
1,224,621
Messages
6,179,938
Members
452,949
Latest member
beartooth91

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