How to convert number to words in Excel, comments page 10

Fahmid says:
2019-04-12 at 8:27 am

Thanks for authentic codes. It works where codes from Microsoft gave an error message.
Best wushes!
Regards,

Yannick says:
2019-04-10 at 10:10 am

It working very well. Thank's

Mike says:
2019-04-06 at 3:05 am

Hi, Can please I have the code without dollars and cents? thanks

Raja Zeeshan Khaliq says:
2019-06-25 at 8:33 am

Hey Mike!
do you get the code without dollars and cents
i also need this code
if you get this code please send me
i have really need this code

Manjeet kimar says:
2019-04-04 at 11:19 am

when i copy and paste this, it gives me errors, what am i doing wrong?

ARNON says:
2019-04-04 at 9:53 am

SIMPLE, LOL, YOUR MACROS WORKED FIRST TIME, NO PROBLEMS, ONLY REQUEST IS FOR A CHANGE TO 00/00...01/00...03/00 AND SO ON INSTEAD OF CENTS, GOT YOURS TO WORK 00/00 AND 01/00 TO WORK, BUT AFTER THAT, ALL IS TEXT /00. SO I DELETED CENTS ALL TOGETHER TILL I GET THE CORRECT MACROS FOR THE OO/00 TO 99/00, PLEASE AND THANKS

David Zahn says:
2020-08-06 at 5:08 pm

Here is my solution in a shorter macro:
Function NumberToWords(ByVal StartNumber As Currency) As String

Dim Buffer As String
Dim Dollars
Dim Cents
Dim NumberofGroups
Dim NumberSegment
Dim Hundreds
Dim Tens
Dim Ones
GroupNames = Array("", "Thousand ", "Million ", "Billion ", "Trillion ")
OnesNames = Array("", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ", "Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen ")
TensNames = Array("", "", "Twenty ", "Thirty ", "Fourty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety ")

Dollars = Fix(StartNumber)
Cents = Fix(100 * (StartNumber - Fix(StartNumber)) + 0.5)
NumberofGroups = Fix((Log(Dollars) / Log(10)) / 3)

Dim i
For i = NumberofGroups To 0 Step -1
NumberSegment = Fix(Dollars / 10^ ^ (i * 3))
Hundreds = Fix(NumberSegment / 100)
Tens = Fix((NumberSegment - (Hundreds * 100)) / 10)
Ones = NumberSegment - (Hundreds * 100) - (Tens * 10)

If Hundreds > 0 Then
Buffer = Buffer + OnesNames(Hundreds) + "Hundred "
End If
If Tens > 0 Then
If Tens > 1 Then
Buffer = Buffer + TensNames(Tens) + OnesNames(Ones)
Else
Buffer = Buffer + OnesNames(Tens * 10 + Ones)
End If
Else
Buffer = Buffer + OnesNames(Ones)
End If

If NumberSegment > 0 Then
Buffer = Buffer + GroupNames(i)
End If

Dollars = Dollars - (NumberSegment * 10^ ^ (i * 3))
Next i

Buffer = Buffer + "and "
If Cents < 10 Then
Buffer = Buffer + "0"
End If
Buffer = Buffer + Trim(Str(Cents)) + "/100 *****************************************"

NumberToWords = Buffer

End Function
Arvin says:
2019-05-11 at 1:53 am

I need the answer for this

Zainul Abdeen says:
2019-04-04 at 8:56 am

Thanks you very much....

Mujahid Azeem says:
2019-04-02 at 6:48 am

Hi,
Could you tell me the code where I can mention "and" before the last figures e.g. Rs 1,987,290 (Rupees one million nine Hundred eighty seven thousand two hundred and ninety only)

KRISHNAN says:
2021-06-30 at 3:11 am

pls use below function
Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Rupees, only, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand, "
Place(3) = " Million, "
Place(4) = " Billion, "
Place(5) = " Trillion, "

MyNumber = Trim(Str(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace > 0 Then
only = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp "" Then Rupees = Temp & Place(Count) & Rupees
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Rupees
Case ""
Rupees = "No Rupees"
Case "One"
Rupees = "One Dollar"
Case Else
Rupees = Rupees & " Only"
End Select
SpellNumber = "Rupees " & Rupees
End Function

Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function

Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19…
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99…
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function

Jay says:
2019-03-27 at 12:31 pm

Hi, can i have the code without dollars and cents, E.g.
Four Million Six Hundred Three Thousand Four Hundred Twenty Two

Guruprasad says:
2019-03-19 at 9:55 am

Thank you Bro....

haider says:
2019-03-15 at 7:59 am

i just want the wording not any currency. what should i do?

Sultan Ahmed says:
2019-02-21 at 1:02 pm

How can I download vba code to my phone?

Ola says:
2019-02-20 at 5:19 pm

Thank you for this.
However, this was noticed.
For example, i tried to spell 450,750.34
Four Hundred Fifty Thousand Seven Hundred Fifty Dollars and Thirty Four Cents
In my country, we spell like this
Four Hundred and Fifty Thousand, Seven Hundred and Fifty Dollars, Thirty Four Cents
Can you please help me tweak the VBA code to have this new result.

Bindesh says:
2019-08-11 at 8:47 am

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Rupees, Paisa, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

MyNumber = Trim(Str(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace > 0 Then
Paisa = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp "" Then Rupees = Temp & Place(Count) & Rupees
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Rupees
Case ""
Rupees = "No Rupees"
Case "One"
Rupees = "One Dollar"
Case Else
Rupees = Rupees & " Rupees"
End Select
Select Case Paisa
Case ""
Paisa = " and No Paisa"
Case "One"
Paisa = " and One Cent"
Case Else
Paisa = " and " & Paisa & " Paisa"
End Select
SpellNumber = Rupees & Paisa
End Function

Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function

Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function
wasiullah says:
2019-04-01 at 8:02 am

did u got that code? i need it too

kherlen alih says:
2019-02-18 at 2:26 am

help me naman po pano mag convert numbers to words sa excell for cheque please ..

Liza says:
2019-02-12 at 4:59 am

Thank you Sir for sharing this formula. It's help me a lot! I really appreciate that. Thanks again.

Adil Farooqui says:
2019-02-02 at 1:55 pm

This formula is very good working on any Currency..

Tanzeel Hussain says:
2019-02-01 at 4:00 pm

I Was looking for him a lot, This formula hit is very good working on any Currency,
i am very happy thank you very much sir, Great job, May Allah bless you.?????????

Raja Zeeshan Khaliq says:
2019-06-25 at 8:31 am

Assalam -o- Alaikum
My dear brother i want to convert a mathematical digits in english words without any currency like as
564 Five Hundred and Sixty Four
what i do now please help me as soon as possible
i have to prepare Result Cards
1. Ikram Ullah says:
  2019-09-19 at 10:35 am
  
  Wa Alaikum Assalam,
  Dear Raja Zeeshan
  Please in macro editor window press Ctrl+F , find Dollar and replace it with empty space . do it again for Dollars and similarly for Cent and cents respectively. I hope it will solve your problem.

Supriya says:
2019-01-31 at 7:18 am

I am getting a value like this :Four Hundred Eighty Dollars and Seventy One Cents. Instead of Dollars I want to have it in Kuwaiti Dinars and fills.
Pls help me

Supriya says:
2019-01-31 at 7:16 am

Dear, this is to update you that the formula is not working perfectly. See the response that I am getting:->
For Kuwaiti Dinars
for KD 122.050 I got: One Hundred Twenty Two Kuwait Dinar and Five fils

the correct answer has to be: One Hundred Twenty Two Kuwait Dinar and Fifty fils
What to change now

Eimantas says:
2019-06-05 at 5:55 am

the issue is to replace the separator from "." to "," in the number or in the formula from:
MyNumber = Trim(Str(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
to:
MyNumber = Trim(Str(MyNumber))
DecimalPlace = InStr(MyNumber, ",")
thon says:
2019-03-08 at 12:34 am

look at the figure, it says .05 and not .50, the result is correct it seems the figure is point zero five, therefore the result should be five instead of fifty

Jorge Baez says:
2019-01-29 at 10:25 pm

I keep getting a syntax error
Do While MyNumber ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp "" Then Dollars = Temp & Place(Count) & Dollars

Marjorie Montemayor says:
2019-01-29 at 3:50 am

how can i convert numbers to words for Peso

Oli says:
2019-01-29 at 5:50 am

replace lng mam yung dollars nang pesos (control+h) tapos replace all lng..

dollars to pesos
dollar to peso
1. Joseph Robert A Costacorta says:
  2020-05-27 at 7:09 am
  
  How about po yong 66.15 for example.
  Sinasabi nya Sixty six pesos and Fourteen Cents lng. Paano po babaguhin para maging fifteen sya.

jeevan singh says:
2019-01-25 at 6:59 am

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

MyNumber = Trim(Str(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace > 0 Then
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp "" Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Dollars
Case ""
Dollars = "No Dollars"
Case "One"
Dollars = "One Dollar"
Case Else
Dollars = Dollars & " Dollars"
End Select
Select Case Cents
Case ""
Cents = " and No Cents"
Case "One"
Cents = " and One Cent"
Case Else
Cents = " and " & Cents & " Cents"
End Select
SpellNumber = Dollars & Cents
End Function

Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function

Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function

Loy says:
2019-03-23 at 1:26 am

when i copy and paste this, it gives me errors, what am i doing wrong?
1. Abhishek kumar says:
  2019-09-04 at 4:30 am
  
  Option Explicit
  'Main Function
  Function SpellNumber(ByVal MyNumber)
  Dim Rupees, Paisa, Temp
  Dim DecimalPlace, Count
  ReDim Place(9) As String
  Place(2) = " Thousand "
  Place(3) = " Million "
  Place(4) = " Billion "
  Place(5) = " Trillion "
  
  MyNumber = Trim(Str(MyNumber))
  DecimalPlace = InStr(MyNumber, ".")
  If DecimalPlace > 0 Then
  Paisa = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
  "00", 2))
  MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
  End If
  Count = 1
  Do While MyNumber ""
  Temp = GetHundreds(Right(MyNumber, 3))
  If Temp "" Then Rupees = Temp & Place(Count) & Rupees
  If Len(MyNumber) > 3 Then
  MyNumber = Left(MyNumber, Len(MyNumber) - 3)
  Else
  MyNumber = ""
  End If
  Count = Count + 1
  Loop
  Select Case Rupees
  Case ""
  Rupees = "No Rupees"
  Case "One"
  Rupees = "One Rupees"
  Case Else
  Rupees = Rupees & " Rupees"
  End Select
  Select Case Paisa
  Case ""
  Paisa = " and No Paisa"
  Case "One"
  Paisa = " and One Paisa"
  Case Else
  Paisa = " and " & Paisa & " Paisa"
  End Select
  SpellNumber = Rupees & Paisa
  End Function
  
  Function GetHundreds(ByVal MyNumber)
  Dim Result As String
  If Val(MyNumber) = 0 Then Exit Function
  MyNumber = Right("000" & MyNumber, 3)
  ' Convert the hundreds place.
  If Mid(MyNumber, 1, 1) "0" Then
  Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
  End If
  ' Convert the tens and ones place.
  If Mid(MyNumber, 2, 1) "0" Then
  Result = Result & GetTens(Mid(MyNumber, 2))
  Else
  Result = Result & GetDigit(Mid(MyNumber, 3))
  End If
  GetHundreds = Result
  End Function
  
  Function GetTens(TensText)
  Dim Result As String
  Result = "" ' Null out the temporary function value.
  If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
  Select Case Val(TensText)
  Case 10: Result = "Ten"
  Case 11: Result = "Eleven"
  Case 12: Result = "Twelve"
  Case 13: Result = "Thirteen"
  Case 14: Result = "Fourteen"
  Case 15: Result = "Fifteen"
  Case 16: Result = "Sixteen"
  Case 17: Result = "Seventeen"
  Case 18: Result = "Eighteen"
  Case 19: Result = "Nineteen"
  Case Else
  End Select
  Else ' If value between 20-99...
  Select Case Val(Left(TensText, 1))
  Case 2: Result = "Twenty "
  Case 3: Result = "Thirty "
  Case 4: Result = "Forty "
  Case 5: Result = "Fifty "
  Case 6: Result = "Sixty "
  Case 7: Result = "Seventy "
  Case 8: Result = "Eighty "
  Case 9: Result = "Ninety "
  Case Else
  End Select
  Result = Result & GetDigit _
  (Right(TensText, 1)) ' Retrieve ones place.
  End If
  GetTens = Result
  End Function
  
  Function GetDigit(Digit)
  Select Case Val(Digit)
  Case 1: GetDigit = "One"
  Case 2: GetDigit = "Two"
  Case 3: GetDigit = "Three"
  Case 4: GetDigit = "Four"
  Case 5: GetDigit = "Five"
  Case 6: GetDigit = "Six"
  Case 7: GetDigit = "Seven"
  Case 8: GetDigit = "Eight"
  Case 9: GetDigit = "Nine"
  Case Else: GetDigit = ""
  End Select
  End Function
  1. Dr. RAtnapal says:
    2019-09-17 at 4:53 pm
    
    i use this code but not given result. massage come Privacy warning; This documents contains macros, Activex controls,XML expansion pack information, or web components. These may include personal information that can not be removed by the documents inspector.
  2. Abhishek kumar says:
    2019-09-04 at 4:30 am
    
    than save ur file

Yohannes.melesse@syngenta .com says:
2019-01-18 at 8:14 am

please provide sample video

Bryan says:
2019-01-10 at 7:31 pm

Hello,

I'm trying to convert the numbers into a weight.
ex. 2000 = two thousand pounds
Is there a way to easily convert that?

Regards,
Bryan

AK1 says:
2019-01-09 at 7:01 am

How can I convert 123,156,236.64 KG into words? Please help me

Kieron Morrow says:
2019-01-03 at 10:23 am

Can anyone help with it converting negative numbers?

for example:

-£120.69 reading as Minus One Hundred Pound and Sixty Nine Pence

VISHAL SONI says:
2019-01-01 at 1:36 pm

I am use this formula but there is not convert amount in word format instead of that i show the formula in sheet. pl guide me.

Aple says:
2018-12-19 at 9:01 pm

Finally it works on Excell 2010!!! Thank you, Sir!!

I followed your instructions and it works!

Titigh says:
2018-12-12 at 10:01 am

123=One hundred and twenty three.
But excel make it
One hundred twenty three.
How am I going to edit the code so that it can accept the "and" even when I type 20550 It should be able to read "Twenty Five Thousand Five Hundred and Fifty
Please Note the "and"

Satham says:
2018-12-12 at 8:12 am

How not spelled if no cents.

Phaneth says:
2018-12-12 at 7:42 am

Every perfect, but error with unicode is "S" to "?"

Arvind says:
2018-12-11 at 1:50 am

Please share the password of Popup Spell Zip File (add on to MS Excel) I have downloaded the Popup Spell Add on but while extracting it asks for password.

Please help

sandip says:
2018-12-03 at 12:17 pm

Excellent & easy way to convert number in word ..
nothing to say .....

Michael James says:
2018-11-21 at 2:32 pm

Can one use =spellnumber to convert ordinary numbers, not currency) to words. If so how.

Ependra Singh says:
2018-11-16 at 4:49 am

Hi EveryOne

Greeting for the day!!!

I need solution that how to unhide hidden row which is hidden by advanced filter in vba. When i select all sheet and unhide the rows but it not work. Please suggest if anybody have any idea for it.

Thanks,
Ependra Singh
9899808895

Jaseem says:
2018-11-06 at 4:01 pm

Hi,

Please give me one solution for convert sum amount to words in excel

Win says:
2018-10-14 at 1:53 am

I Like This

kunal says:
2018-10-04 at 6:38 am

after closing excel file and open again this method doesn't work.
please help me!

Sir Banter says:
2018-09-25 at 1:47 pm

since our currency is peso and the cents are express as centavos, i modified the code to reflect our currency...

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Pesos, Centavos, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

MyNumber = Trim(Str(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace > 0 Then
Centavos = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp "" Then Pesos = Temp & Place(Count) & Pesos
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Pesos
Case ""
Pesos = "No Pesos"
Case "One"
Pesos = "One Peso"
Case Else
Pesos = Pesos & " Pesos"
End Select
Select Case Centavos
Case ""
Centavos = " "
Case "One"
Centavos = " and One Centavo"
Case Else
Centavos = " and " & Centavos & " Centavos"
End Select
SpellNumber = Pesos & Centavos
End Function

Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function

Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function

akshita bhutaiya says:
2018-09-19 at 12:19 pm

hello sir please provide code for Indian currency

example. 100000 = one lac only
1000 = one thousand
100 = one hundred
1000000 = ten lac only
10000000 = one crore only

akshita bhutaiya says:
2018-09-19 at 12:19 pm

hello sir please provide code for Indian currency
example. 100000 = one lac only
1000 = one thousand
100 = one hundred
1000000 = ten lac only
10000000 = one crore only

Jai says:
2018-09-17 at 7:53 am

hi,

I need to know how i can break the lines of number in words as if the word is too long i need to print in the second line of cheque. how i can do that.

Faten says:
2018-09-16 at 7:31 pm

Thank you !
Well done for Dinars and Euros !

Nirmal says:
2018-09-15 at 6:34 am

i got a problem with the excel which is unknown format number, i want to convert that in to 10 digit mobile numbers

EX - ffffec0704fb682e5547b7d0d3da5527

Ehtisham says:
2018-09-12 at 4:09 am

Can someone please help me making such a code that I only get "numbers converted into words",in excel i.e., without dollar or any any currency attached to it. Say I want to convert 2, 34 and 56, can it directly read two, thirty four , and fifty six respectively.

I will appreciate such help.

Eswari says:
2018-08-22 at 12:55 pm

Hi Dear ,

I tried converting into indian rupees .

But CTRL+H function is not working

It is showing the error when I type "Dollars"as ' we couldnt find what you are looking for "

Pls help

hari singh says:
2018-08-20 at 1:08 pm

Hi,
I'm trying to convert nums to words (in indian currency: like 448787 to four lakh fourty eight thousand seven hundred eighty seven only),but its not happening.
Please help me & take me out of the problem.

hari singh says:
2018-08-20 at 1:05 pm

Hi,
I'm trying to convert nums to words (in indian currency: like 448787 to four lakh fourty eight seven hundred eighty seven only),but its not happening.
Please help me & take me out of the problem.

Shubham says:
2018-08-16 at 4:37 am

I want to modify it for Indian system, I changed Dollar to Rupee, but changing Millions to Lakhs wont be correct as Million has 6 zeroes and Lakhs as 5. the array defined has terms differing by 3 zeroes, can you help out with modifying the array to incorporate Lakhs

Sir Daniel says:
2018-08-11 at 5:52 am

How can numbers be converted with no currency and if no cents to ignore cents bit
Example
5,455/= Five thousand Four hundred fifty five only
5,455.50/= Five thousand Four hundred fifty five and Fifty cents only

calven says:
2018-07-22 at 9:07 am

How to convert as follows:-
1. 1,234 = One Thousand Two hundred And Thirty Four
2. 1,200 = One Thousand And Two hundred?
3. 1,202.50 = One Thousand Two hundred Two And Cents Fifty?
4. 11,010 = Eleven Thousand And Ten?

basha says:
2018-09-09 at 11:43 am

Function NumToWord(ByVal N As Currency) As String

Const Ten = 10@
Const Hundred = Ten * Ten
Const Thousand = Ten * Hundred
Const Lakh = Thousand * Hundred
Const Crore = Lakh * Hundred
Const Million = Thousand * Thousand
Const Billion = Thousand * Million
Const Trillion = Thousand * Billion

If (N = 0@) Then NumToWord = "zero": Exit Function

Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""
Dim Frac As Currency: Frac = Abs(N - Fix(N))
If (N < 0@ Or Frac 0@) Then N = Abs(Fix(N))
Dim AtLeastOne As Integer: AtLeastOne = N >= 1

If (N >= Crore) Then
Buf = Buf & NumToWordDigitGroup(Int(N / Crore)) & " Crore"
N = N - Int(N / Crore) * Crore
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Lakh) Then
Buf = Buf & NumToWordDigitGroup(Int(N / Lakh)) & " Lakh"
N = N - Int(N / Lakh) * Lakh
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Thousand) Then
Buf = Buf & NumToWordDigitGroup(N \ Thousand) & " Thousand"
N = N Mod Thousand
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Hundred) Then
Buf = Buf & NumToWordDigitGroup(N \ Hundred) & " hundred"
N = N Mod Hundred
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= 1@) Then
Buf = Buf & NumToWordDigitGroup(N)
End If

NumToWord = Buf
End Function

Private Function NumToWordDigitGroup(ByVal N As Integer) As String

Const Hundred = " hundred"
Const One = "one"
Const Two = "two"
Const Three = "three"
Const Four = "four"
Const Five = "five"
Const Six = "six"
Const Seven = "seven"
Const Eight = "eight"
Const Nine = "nine"
Dim Buf As String: Buf = ""
Dim Flag As Integer: Flag = False

Select Case (N \ 100)
Case 0: Buf = "": Flag = False
Case 1: Buf = One & Hundred: Flag = True
Case 2: Buf = Two & Hundred: Flag = True
Case 3: Buf = Three & Hundred: Flag = True
Case 4: Buf = Four & Hundred: Flag = True
Case 5: Buf = Five & Hundred: Flag = True
Case 6: Buf = Six & Hundred: Flag = True
Case 7: Buf = Seven & Hundred: Flag = True
Case 8: Buf = Eight & Hundred: Flag = True
Case 9: Buf = Nine & Hundred: Flag = True
End Select

If (Flag False) Then N = N Mod 100
If (N > 0) Then
If (Flag False) Then Buf = Buf & " "
Else
NumToWordDigitGroup = Buf
Exit Function
End If

Select Case (N \ 10)
Case 0, 1: Flag = False
Case 2: Buf = Buf & "twenty": Flag = True
Case 3: Buf = Buf & "thirty": Flag = True
Case 4: Buf = Buf & "forty": Flag = True
Case 5: Buf = Buf & "fifty": Flag = True
Case 6: Buf = Buf & "sixty": Flag = True
Case 7: Buf = Buf & "seventy": Flag = True
Case 8: Buf = Buf & "eighty": Flag = True
Case 9: Buf = Buf & "ninety": Flag = True
End Select

If (Flag False) Then N = N Mod 10
If (N > 0) Then
If (Flag False) Then Buf = Buf & "-"
Else
NumToWordDigitGroup = Buf
Exit Function
End If

Select Case (N)
Case 0:
Case 1: Buf = Buf & One
Case 2: Buf = Buf & Two
Case 3: Buf = Buf & Three
Case 4: Buf = Buf & Four
Case 5: Buf = Buf & Five
Case 6: Buf = Buf & Six
Case 7: Buf = Buf & Seven
Case 8: Buf = Buf & Eight
Case 9: Buf = Buf & Nine
Case 10: Buf = Buf & "ten"
Case 11: Buf = Buf & "eleven"
Case 12: Buf = Buf & "twelve"
Case 13: Buf = Buf & "thirteen"
Case 14: Buf = Buf & "fourteen"
Case 15: Buf = Buf & "fifteen"
Case 16: Buf = Buf & "sixteen"
Case 17: Buf = Buf & "seventeen"
Case 18: Buf = Buf & "eighteen"
Case 19: Buf = Buf & "nineteen"
End Select

NumToWordDigitGroup = Buf

End Function

Comments on: Two best ways to convert numbers to words in Excel

Comments page 10. Total comments: 530