Apr 19 2018
08:22 AM
- last edited on
Jul 25 2018
11:45 AM
by
TechCommunityAP
Apr 19 2018
08:22 AM
- last edited on
Jul 25 2018
11:45 AM
by
TechCommunityAP
Good day,
I'm looking for a way to convert numbers(which inside a single cell) into words.
I search the net and found this code:
==============================================================
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 & " "
Else
Temp2 = ""
End If
If n = 3 Then
If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = " "
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) = ""
If DecimalPosition > 0 And DecimalPosition < Len(NumStr) Then
Temp1 = " "
For n = DecimalPosition + 1 To Len(NumStr)
Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1)))
Next n
WordNum = WordNum & " and" & Temp1 & " Fils"
End If
If Len(WordNum) = 0 Or Left(WordNum, 2) = 0 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
==============================================================
But after using the code, I still want some modification. This are the values of the cells(as per sample) that I want to convert 1st-123,456.789, 2nd-123,456.000, 3rd-0.789
The module gives me output of the following:
1st = One hundred Twenty Three thousand Four hundred Fifty Six and Seven Eight Nine
2nd = One hundred Twenty Three thousand Four hundred Fifty Six
3rd = and seven Eight Nine
----------------------------------
What I want as outputs are the following:
1st = One hundred Twenty Three thousand Four hundred Fifty Six and Seven hundred Eighty Nine Fils Only
2nd = One hundred Twenty Three thousand Four hundred Fifty Six Only
3rd = Seven hundred Eighty Nine Fils Only
----------------------------------
What problems I'm with?:
1st part
- numbers after the decimal point(.) not converting as I want.
2nd part
- I want it to show "only" in each end of the word converted.
3rd part
- "and" is showing. If the right side part is =0, I want the converted word only of the right side part.
Overall, I tried to put the "only" and if appears in all 3cells but the problem also appears in the 1st part like this: One hundred Twenty Three thousand Four hundred Fifty Six Only and Seven hundred Eighty Nine Fils Only. It shows the word "only" twice.
-------------------------------------------
Please help me with my issue. I'll wait for any response.
Thank you in advance.
Apr 19 2018 09:59 AM
Apr 21 2018 01:07 AM
Yes, that will be great Sir. I will happy to try it. How will I get that formula?
Thanks in advance Sir Jamil.
Apr 24 2018 10:11 AM
SolutionHi Christoffer,
This one was a hell of a formula.
In B2 formula below
=IF(A2<=0,"Referred Cell Must be Empty",IF(A2>10^12-0.01,"Exceeds Maximum which is 999999999999.99",TRIM(PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(A2),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(A2),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(A2>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hu
ndred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(A2),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(A2),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(A2),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(A2),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(A2),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(A2),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(A2),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(A2),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(A2,1)>1," "," ")&IF(AND(A2>=1,A2<2)," ",""))&" and "&IF(ISERROR(FIND(".",A2)),"Only",PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),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(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(A2>=10^9," billion ",""),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hu
ndred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),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(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),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(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),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(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),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(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(TEXT(MOD(A2,1)*1000,"000"),1)>1," Fils Only"," ")&IF(AND(TEXT(MOD(A2,1)*1000,"000")>=1,TEXT(MOD(A2,1)*1000,"000")<2),"Fil Only",""))))))
AND in C2
=IF(A2<1,SUBSTITUTE(B2,"and "," "),IF(ISERROR(FIND(".",A2)),SUBSTITUTE(B2," and "," "),B2))
Please see attached sample file.
May 01 2018 07:14 AM
Good day Mr. Mohammed,
Thank you very much for a hell of formula. I was able to check the formula and adjust some as per my concern and it worked!! Though it's really a hell long formula, it really gives me the exact output that I want! Very very much appreciated! Worth the wait.
Again, THANK YOU so MUCH Sir!
Chris
May 01 2018 02:05 PM
Jul 24 2018 12:40 PM
In a posted conversation Jamil created this amazing formula to convert numbers their equivalent word. It work perfectly, however I just want to alter it slightly so it doesn't include and only at the end
for example when I use the number 358 it converts the number to Three Hundred Fifty-Eight and Only.
i just want it to read Three Hundred Fifty-Eight.
=IF(A2<=0,"Referred Cell Must be Empty",IF(A2>10^12-0.01,"Exceeds Maximum which is 999999999999.99",TRIM(PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(A2),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(A2),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(A2>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hu
ndred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(A2),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(A2),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(A2),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(A2),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(A2),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(A2),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(A2),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(A2),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(A2,1)>1," "," ")&IF(AND(A2>=1,A2<2)," ",""))&" and "&IF(ISERROR(FIND(".",A2)),"Only",PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),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(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(A2>=10^9," billion ",""),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hu
ndred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),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(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),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(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),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(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),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(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"))))))
Jul 25 2018 09:50 AM
Would it be possible to modify this formula so the and only is eliminated?
358 converts to Three Hundred Fifty-Eight and Only
I will only be working with whole numbers no decimals.
Thank you
Jennifer
Jul 25 2018 10:58 AM
Jul 26 2018 08:52 AM
Jamil
sorry to bother you but any luck updating this formula?
Jul 27 2018 05:23 PM
Hi Jennifer,
here is the formula. also embedded it in the attached workbook.
=IF(A1<=0,"Referred Cell Must be Empty",IF(A1>10^12-0.01,"Exceeds Maximum which is 999999999999",TRIM(PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),1,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)),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 ","two hundred ","three hu ndred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),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 ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),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(FLOOR(A1,1)>1," "," ")&IF(AND(A1>=1,A1<2),"","")))))
Jul 17 2019 04:14 PM
I just tried this formula and it works great, thanks.@Jamil Mohammad
Jul 17 2019 07:19 PM
Jul 18 2019 11:19 AM
Oct 20 2021 09:45 PM
Apr 24 2018 10:11 AM
SolutionHi Christoffer,
This one was a hell of a formula.
In B2 formula below
=IF(A2<=0,"Referred Cell Must be Empty",IF(A2>10^12-0.01,"Exceeds Maximum which is 999999999999.99",TRIM(PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(A2),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(A2),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(A2>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hu
ndred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(A2),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(A2),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(A2),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(A2),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(A2),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(A2),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(A2),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(A2),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(A2,1)>1," "," ")&IF(AND(A2>=1,A2<2)," ",""))&" and "&IF(ISERROR(FIND(".",A2)),"Only",PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),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(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(A2>=10^9," billion ",""),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hu
ndred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),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(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),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(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),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(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),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(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(TEXT(MOD(A2,1)*1000,"000"),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(TEXT(MOD(A2,1)*1000,"000"),1)>1," Fils Only"," ")&IF(AND(TEXT(MOD(A2,1)*1000,"000")>=1,TEXT(MOD(A2,1)*1000,"000")<2),"Fil Only",""))))))
AND in C2
=IF(A2<1,SUBSTITUTE(B2,"and "," "),IF(ISERROR(FIND(".",A2)),SUBSTITUTE(B2," and "," "),B2))
Please see attached sample file.