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

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

Ratha says:
2018-07-18 at 7:42 am

Hello

I want convert to Quantity PCS example : one hundred and ten pcs only.

but i don't know how to write down code

Thirumalai Selvan says:
2018-07-10 at 10:49 am

How to convert the 1,234 into One Thousand two hundred and thirty four

Keith says:
2018-06-29 at 9:42 pm

Thank you everyone who made and updated this code. I appreciate it very much.

There was a small glitch if the ones digit was a zero, where $820.00 would read "Eight hundred Twenty and No Cents" (two spaces between "Twenty" and "and"). I remedied this by removing the space in front of Thousand, Million, Billion, Trillion, as well as the statements for cents, and Hundred. Then I added a space to the end of Ten through Nineteen, and Twenty through Ninety in GetTens, and One through Nine in GetDigit.

I am trying to come up with a means to hyphenate a two-digit number like 32 (Thirty-Two) while also leaving off the hyphen from two-digit number ending in 0 like 50 (Fifty). Any suggestions are welcome.

CHNADAN ARYA says:
2018-06-20 at 7:19 am

SIR HOW CUT AND PAST THIS FUNCTION IN EXCEL SPREAD SHEET

Aditha Karunarathna says:
2018-06-14 at 7:22 am

Here are the cordings for Rupee Values

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Rupees, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to Rupee amount.
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 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 Rupee"
Case Else
Rupees = Rupees & " Rupees"
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 = Rupees & Cents
End Function

' Converts a number from 100-999 into text
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

' Converts a number from 10 to 99 into text.
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

' Converts a number from 1 to 9 into text.
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

RichieSalan says:
2018-06-13 at 6:02 am

does anyone here know how to program this:

Php9,876,543.10

Nine Million Eight Hundred Seventy-Six Thousand Five Hundred Forty-Three Pesos and 10/100

How to format this?

Sujan jk says:
2018-06-09 at 12:23 pm

What if I only wanted dollar not the cents.

Gayathri says:
2018-06-09 at 8:24 am

How to convert lakhs into words

Aziz says:
2018-06-05 at 5:04 am

Why i can't get the right word

For example 760.75

Ringgit Malaysia seventy six thousand seventy five only

But if no cents, the word is right

Can u help me

Aziz says:
2018-06-05 at 5:03 am

Why i can't get the right word

For example 760.75

Ringgit Malaysia seventy six thousand seventy five only

Can u help me

malinda says:
2018-05-28 at 6:50 am

i wont to convert amount in block letters. how to do it

Doug says:
2018-05-28 at 12:37 pm

Malinda:
If the "block letters" are text the answer is the VBA code above or a third party tool. If the letters are not text, but a picture or something else, you'll have to type them as text.

ICE LEE says:
2018-05-23 at 12:24 am

if required "27605.02" to Twenty Seven Thousand Six Hundred and Five and 02/100?

Doug says:
2018-05-23 at 12:36 pm

ICE LEE:
The VBA code is in the article above or you need a third party tool.

Chen says:
2018-05-08 at 4:11 am

Can i get rid of the dollar / dollars word?

example: 6,130.20 = Six Thousand One Hundred Thirty and Twenty Cents

Joseph says:
2018-05-07 at 3:12 pm

CAN I HAVE A CODE THAT CONVERTS 11 TO ONE ONE OR 22 TO TWO TWO?

Doug says:
2018-05-08 at 1:34 pm

Joseph:
The VBA code is in the article above or you need a third party tool.
Doug says:
2018-05-07 at 4:26 pm

The VBA code is in the article above.

Kenny Telfer says:
2018-05-04 at 3:01 pm

Some errors in that code for me, I've fixed and changed to UK Pounds, also added "and" between the thousand and the hundreds, and a comma.

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Pounds, Pence, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand, "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert Pence and set MyNumber to Pounds amount.
If DecimalPlace > 0 Then
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp "" Then Pounds = Temp & Place(Count) & Pounds
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Pounds
Case ""
Pounds = "No Pounds"
Case "One"
Pounds = "One Pound"
Case Else
Pounds = Pounds & " Pounds"
End Select
SpellNumber = Pounds
End Function

' Converts a number from 100-999 into text
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 and "
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

' Converts a number from 10 to 99 into text.
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

' Converts a number from 1 to 9 into text.
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

Mian Muazzam says:
2018-04-21 at 7:10 am

Sir, If We Want to Remove Cents What Will We Don Please Reply me

Mian Muazzam says:
2018-04-21 at 7:10 am

Sir, If We Want to Remove Cents What Will We Don Please Reply me

Diana says:
2018-04-19 at 9:08 pm

In spanish we do not say "Three hundred" in two words, we say "Trecientos" in one word. Is there a say to add another clause to list numbers from 100-999
I try to add this but I am getting an error

Else ' If value between 100-999...
Select Case Val(Left(TensText, 1))
Case 20: Result = "Ciento"
Case 21: Result = "Doscientos "
Case 22: Result = "Trescientos "
Case 23: Result = "Cuatrocientos "
Case 24: Result = "Quinientos "
Case 25: Result = "Seicientos "
Case 26: Result = "Setecientos "
Case 27: Result = "Ochocientos "
Case 28: Result = "Novecientos "

Grace says:
2018-04-12 at 8:27 pm

I was able to use the SpellNumber successfully for all numbers except for numbers that are only in the thousands with any other numbers (e.g. 54,000 or 30,000). I get two spaces before the word Dollars. I had a similar problem with numbers that ended in the hundred dollars and numbers that ended with 20, 30, 40, 50, 60, 70, 80 and 90. To fix the problem, I changed the GetHundreds = Result to GetHundreds = Trim(Result) and GetTens = Result to GetTens = Trim(Result). I don't know how to change the code for numbers that are only in the Thousands.

meharoof says:
2018-03-12 at 9:37 am

i need to display my currency in riyal instead of dollars ...

alicia says:
2018-03-09 at 8:16 am

I write this formula

=CONCATENATE("US ",spellnumber(cell),"***")

I don't know what's wrong it issue "Name?" Anyone can answer me?

Many Thanks,
Alicia

Bharath says:
2018-03-07 at 6:21 am

This is awesome! I know nothing about VBA but it still made it happen with this so Thanks a ton. however, how do I exclude cents/paisa from the code?i.e. will not need any decimals.

Thanks again.

Mozammel says:
2018-02-10 at 12:12 pm

Hello Prem,
Thanks,man. it helped me out.

Regards:
Mozammel
Chittagong, Bangladesh.

harry says:
2018-02-05 at 10:31 am

Please send details of excel sheet in permanent add for automatically spellnumber on MS excel- 2007

paras says:
2018-01-29 at 11:33 am

modules after what are the doing 1

paras says:
2018-01-29 at 11:33 am

modules after what are the doing

Prem says:
2018-01-21 at 9:24 am

Hi, Congrats, It is very helpful. Thank you so much.
How to make change the words starting with Dollars & Cents first.
Eg. Rupees One hundred and Paisa Fifty Only instead of One hundred Rupees and Fifty Paisa Only.

Br//
Prem

Muhammad Ashfaq says:
2018-01-18 at 12:07 pm

Please send details of excel sheet in permanent add for automatically spellnumber on MS excel- 2007.

Also please suggest me how to use spellnumber in excel ,i fallowed your steps as u say ,the results came" #name?,",like that,so plz show me right usage
I am waiting for your reply Thanks Muhammad Ashfaq from Pakistan Karachi.....

Naeem Rajwal says:
2018-01-09 at 5:21 am

Hi Dear, Can you pleases help to fix the decimal up to 3 Digit. Like below:

45.975 -Forty Five Dollar and Nine Hundred seventy Five Cents.

Akki says:
2017-11-20 at 7:41 am

Please send details of excel sheet in permanent add for automatically spellnumber on MS excel- 2016

Awaiting for reply...

Ceen says:
2017-11-02 at 10:37 pm

Dear David ,
In reference to Irina's chat string the same sheet is required by me .I am unable to poke in hours in preparing the formulation ready sheet
Much oblighed
Sincerely 'very faithfully
CEEN

Bhuraram says:
2017-11-01 at 6:40 pm

UpTo 1 Lac Not Convert To In Word Please Help

David says:
2017-10-26 at 8:58 am

Hi Irena

I have emailed you the document as requested. Hope you can help :)

Irina Pozniakova (Ablebits Team) says:
2017-11-01 at 12:30 pm

Thank you for the document, David.

The problem is that the formula results are rounded in Excel, while VBA gets a string and converts only the first 2 characters after the separator. To avoid this issue, please use the following formula instead:
=spellnumber(ROUND(N24,2))

David says:
2017-10-23 at 1:35 pm

Hi, I have an issue where i am trying to spellnumber a figure in a summed cell. I have changed the currency to GBP and Pence, but the spell number keeps minusing 1 Pence from the written number. If i do it on an un-summed cell it works fine.

PLEASE HELP!?

Irina Pozniakova (Ablebits Team) says:
2017-10-23 at 2:55 pm

Hello David,
I'm sorry, but we can't reproduce this issue on our side, the function spells numbers from a summed cell correctly. Could you try to follow the steps from the post again and check if it works for you?
1. David says:
  2017-10-24 at 5:03 pm
  
  HI Irina
  It was only happening between numbers involving 40-70. Very strange as other than that it works perfectly other than not putting an 'and' in after hundred but we can add that in no probs.
  
  Quite happy to send the doc if required.
  1. Irina Pozniakova (Ablebits Team) says:
    2017-10-25 at 3:45 pm
    
    Hi David,
    Thank you for the details. It would be great to have a look at the document, please send it to support@ablebits.com with a link to this post, it may help us understand what is causing this strange behaviour.

sunil kumar yadav says:
2017-08-21 at 2:30 am

I have a file containing multiple sheets the first of which is an index
sheet. The other sheets are hidden. I want to be able to click on a
hyperlink in the index sheet that will send me to the hidden sheet and
open it.
Is this possible ?

Glenn says:
2017-08-15 at 7:11 am

I have tried and it was successful. But it was all in dollars.. How about in Qatar Riyals? How can i do it? Can you feed the module formula?

Bikash singh says:
2017-07-26 at 6:57 am

Please email formula convert number to word

Tamizi says:
2017-07-11 at 4:09 am

Many thanks to admin

Tamizi says:
2017-07-11 at 4:08 am

Please email formula convert to Ringgit Malaysia (RM)

Thu Hien Nguyen says:
2017-06-22 at 5:20 am

Please kindly send me the formula also.
Many thanks.

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

Comments page 12. Total comments: 530