convert value in to words in excel

A

anil21

New Member
Joined
Jan 12, 2013
Messages
20
R/Sir
i am download this formula from the internet but this formula was not working fine please correct this formula this formula was display a result like this for Example " this formula was count this value & display a result like this "1,15550 =Rupees One Lac Fifteen Thousand Five Hundred Fifty only." i am send the formula please correct this formula for

=IF(OR(LEN(FLOOR(B3,1))>=13,FLOOR(B3,1)<=0),"Out of range",PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(B3),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(B3),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(B3),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(B3),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(B3),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(B3>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(B3),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(B3),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(B3),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(B3),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(B3),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(B3),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(B3),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(B3),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(B3),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(B3),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(B3),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(B3),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(B3),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(B3),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(B3),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(B3,1)>1," Rupees"," Rupee")))

thanking you sir
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
replace B3 with the location of your value
 
Upvote 0
r/sir
this formula was not working correctly please correction in this formula,this formula was not calculate lac & Crore
for example than i am insert 100000 in B3 column this formula was convert this figure to words in "one hundred thousand rupees" please correct this formula this formula was convert this figure to words in "Rupees One Lac only"
 
Upvote 0
I don't think the code was ever designed to operate at different currency variants
 
Upvote 0
anil21 said:
r/sir
this formula was not working correctly please correction in this formula,this formula was not calculate lac & Crore
for example than i am insert 100000 in B3 column this formula was convert this figure to words in "one hundred thousand rupees" please correct this formula this formula was convert this figure to words in "Rupees One Lac only"
Click to expand...

anil21,

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0
Hi Anil you can try this function
Public Function Inwords(ByVal varRS As String) As String
' this function writes in words any amount of money less than 99,99,999
Dim la As Long
Dim t As Integer
Dim h As Integer
Dim te As Integer
'Dim Number As Long

Dim lakh As Integer
Dim Thousand As Integer
Dim Hundred As Integer
Dim Tens As Integer
Dim unit As Integer
Dim r1 As Long
Dim r2 As Long
Dim r3 As Long
Dim LaTens As Long
Dim LaUnit As Long
Dim ThTens As Long
Dim ThUnit As Long
Dim s1 As String * 3
Dim s2 As String * 3
Dim s3 As String * 5
Dim s4 As String * 4
Dim s5 As String * 4
Dim s6 As String * 3
Dim s7 As String * 5
Dim s8 As String * 5
Dim s9 As String * 4
Dim s10 As String * 3
Dim s11 As String * 6
Dim s12 As String * 6
Dim s13 As String * 8
Dim s14 As String * 8
Dim s15 As String * 7
Dim s16 As String * 7
Dim s17 As String * 9
Dim s18 As String * 8
Dim s19 As String * 8
Dim s20 As String * 6
Dim s30 As String * 6
Dim s40 As String * 5
Dim s50 As String * 5
Dim s60 As String * 5
Dim s70 As String * 7
Dim s80 As String * 6
Dim s90 As String * 6
Dim sLaTens As String
Dim SLaUnit As String
Dim sThTens As String
Dim sThUnit As String
Dim sLakh As String
Dim sThousand As String
Dim sTens As String
Dim sUnit As String
Dim lesshundred As String


' declaring constants
la = 100000
t = 1000
h = 100
te = 10

s1 = "one"
s2 = "two"
s3 = "three"
s4 = "four"
s5 = "five"
s6 = "six"
s7 = "seven"
s8 = "eight"
s9 = "nine"
s10 = "ten"
s11 = "eleven"
s12 = "twelve"
s13 = "thirteen"
s14 = "fourteen"
s15 = "fifteen"
s16 = "sixteen"
s17 = "seventeen"
s18 = "eighteen"
s19 = "nineteen"
s20 = "twenty"
s30 = "thirty"
s40 = "forty"
s50 = "fifty"
s60 = "sixty"
s70 = "seventy"
s80 = "eighty"
s90 = "ninety"

If Val(varRS) >= 100000 Then
lakh = Val(varRS) \ la
r1 = Val(varRS) Mod la
Thousand = r1 \ t
r2 = r1 Mod t
Hundred = r2 \ h
r3 = r2 Mod h
Tens = r3 \ te
unit = r3 Mod te
Else
Thousand = Val(varRS) \ t
r1 = Val(varRS) Mod t
Hundred = r1 \ h
r2 = r1 Mod h
Tens = r2 \ te
unit = r2 Mod te
End If

If (lakh > 20) Then
LaTens = lakh \ te
LaUnit = lakh Mod te
Select Case LaTens
Case 2
sLaTens = s20
Case 3
sThTens = s30
Case 4
sLaTens = s40
Case 5
sLaTens = s50
Case 6
sLaTens = s60
Case 7
sLaTens = s70
Case 8
sLaTens = s80
Case 9
sLaTens = s90
End Select

Select Case LaUnit
Case 1
SLaUnit = s1
Case 2
SLaUnit = s2
Case 3
SLaUnit = s3
Case 4
SLaUnit = s4
Case 5
SLaUnit = s5
Case 6
SLaUnit = s6
Case 7
SLaUnit = s7
Case 8
SLaUnit = s8
Case 9
SLaUnit = s9
End Select
sLakh = sLaTens & SLaUnit & " lakh "

Else
Select Case lakh
Case 1
sLakh = s1 & " lakh "
Case 2
sLakh = s2 & " lakh "
Case 3
sLakh = s3 & " lakh "
Case 4
sLakh = s4 & " lakh "
Case 5
sLakh = s5 & " lakh "
Case 6
sLakh = s6 & " lakh "
Case 7
sLakh = s7 & " lakh "
Case 8
sLakh = s8 & " lakh "
Case 9
sLakh = s9 & " lakh "
Case 10
sLakh = s10 & " lakh "
Case 11
sLakh = s11 & " lakh "
Case 12
sLakh = s12 & " lakh "
Case 13
sLakh = s13 & " lakh "
Case 14
sLakh = s14 & " lakh "
Case 15
sLakh = s15 & " lakh "
Case 16
sLakh = s16 & " lakh "
Case 17
sLakh = s17 & " lakh "
Case 18
sLakh = s18 & " lakh "
Case 19
sLakh = s19 & " lakh "
Case 20
sLakh = s20 & " lakh "
'writing lakh
End Select
End If

If (Thousand > 20) Then
ThTens = Thousand \ te
ThUnit = Thousand Mod te

Select Case ThTens
Case 2
sThTens = s20
Case 3
sThTens = s30
Case 4
sThTens = s40
Case 5
sThTens = s50
Case 6
sThTens = s60
Case 7
sThTens = s70
Case 8
sThTens = s80
Case 9
sThTens = s90
End Select

Select Case ThUnit
Case 1
sThUnit = s1
Case 2
sThUnit = s2
Case 3
sThUnit = s3
Case 4
sThUnit = s4
Case 5
sThUnit = s5
Case 6
sThUnit = s6
Case 7
sThUnit = s7
Case 8
sThUnit = s8
Case 9
sThUnit = s9
End Select
sThousand = sThTens & sThUnit & " thousand "

Else
Select Case Thousand
Case 1
sThousand = s1 & " thousand "
Case 2
sThousand = s2 & " thousand "
Case 3
sThousand = s3 & " thousand "
Case 4
sThousand = s4 & " thousand "
Case 5
sThousand = s5 & " thousand "
Case 6
sThousand = s6 & " thousand "
Case 7
sThousand = s7 & " thousand "
Case 8
sThousand = s8 & " thousand "
Case 9
sThousand = s9 & " thousand "
Case 10
sThousand = s10 & " thousand "
Case 11
sThousand = s11 & " thousand "
Case 12
sThousand = s12 & " thousand "
Case 13
sThousand = s13 & " thousand "
Case 14
sThousand = s14 & " thousand "
Case 15
sThousand = s15 & " thousand "
Case 16
sThousand = s16 & " thousand "
Case 17
sThousand = s17 & " thousand "
Case 18
sThousand = s18 & " thousand "
Case 19
sThousand = s19 & " thousand "
Case 20
sThousand = s20 & " thousand "
'writing thousand
End Select
End If

Select Case Hundred
Case 1
shundred = s1 & " hundred "
Case 2
shundred = s2 & " hundred "
Case 3
shundred = s3 & " hundred "
Case 4
shundred = s4 & " hundred "
Case 5
shundred = s5 & " hundred "
Case 6
shundred = s6 & " hundred "
Case 7
shundred = s7 & " hundred "
Case 8
shundred = s8 & " hundred "
Case 9
shundred = s9 & " hundred "
' writing hundred
End Select

If (Tens >= 2) Then
Select Case Tens
Case 2
sTens = s20
Case 3
sTens = s30
Case 4
sTens = s40
Case 5
sTens = s50
Case 6
sTens = s60
Case 7
sTens = s70
Case 8
sTens = s80
Case 9
sTens = s90
End Select

Select Case unit
Case 1
sUnit = s1
Case 2
sUnit = s2
Case 3
sUnit = s3
Case 4
sUnit = s4
Case 5
sUnit = s5
Case 6
sUnit = s6
Case 7
sUnit = s7
Case 8
sUnit = s8
Case 9
sUnit = s9
End Select
slesshundred = sTens & sUnit

Else

Select Case r2
Case 1
slesshundred = s1
Case 2
slesshundred = s2
Case 3
slesshundred = s3
Case 4
slesshundred = s4
Case 5
slesshundred = s5
Case 6
slesshundred = s6
Case 7
slesshundred = s7
Case 8
slesshundred = s1
Case 9
slesshundred = s9
Case 10
slesshundred = s10
Case 11
slesshundred = s11
Case 12
slesshundred = s12
Case 13
slesshundred = s13
Case 14
slesshundred = s14
Case 15
slesshundred = s15
Case 16
slesshundred = s16
Case 17
slesshundred = s17
Case 18
slesshundred = s18
Case 19
slesshundred = s19
Case 20
slesshundred = s20
End Select
End If
Inwords = UCase(sLakh & sThousand & shundred & slesshundred & " only")
If Val(varRS) = 0 Then
Inwords = "NIL"
End If
End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

DRSteele
  • Question
Spell out numbers into words for Cheques
Replies
3
Views
255
kweaver
kweaver
JohnZ1156
  • Solved
Formula to convert $$$ numbers to text.
Replies
5
Views
573
Joe4
Joe4
K
  • Solved
Convert Number to Words
Replies
2
Views
620
Kalim Shaikh
K
W
  • Question
multiple password protected pages with same password and using macros
Replies
0
Views
462
WindowGuy
W
P
  • Question
spellnumber VBA
Replies
2
Views
707
PeggyLee
P

Forum statistics

Threads
1,215,059
Messages
6,122,918
Members
449,093
Latest member
dbomb1414

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