*In this article I will show you two quick and free ways to convert currency numbers into English words in Excel 2010, 2013 and other versions.*

Microsoft Excel is a great program to calculate this and that. It was initially developed to process large data arrays. However, it also lets creating accounting records like invoices, evaluation or balance sheets quickly and effectively.

In more or less solid payment documents it is necessary to duplicate numeric values with their word form. It is much harder to falsify typed numbers than those written by hand. Some swindler can try to make 8000 out of 3000, while it is almost impossible to secretly replace "three" with "eight".

So what you need is not just convert numbers to words in Excel (е.g. 123.45 to "one hundred and twenty three, forty five"), but spell out dollars and cents (е.g. $29.95 as "twenty nine dollars and ninety nine cents" ), pounds and pence for GBP, euros and eurocents for EUR, etc.

Even Excel 2013 doesn't have a built-in tool for spelling numbers, not to mention earlier versions. But that is when Excel is really good. You can always improve its functionality using formulas in all their

combinations, VBA macros, or third-party add-ins.

**Below you'll find two ways to convert numbers from figures to words**

And, possibly, you may need to convert Words to Numbers in Excel

**Note.**If you are looking for the

**number to text conversion**, which means you want Excel to see your number as text, it's a bit different thing. Usually, you simply need to change the cell format in Excel (select your range with numbers, press

*Ctrl+1*on the

*Number*tab, and select "

*Text*" in the

*Category*field). You can also use the formula

*=text()*.

Please find the details in the article Ways to convert numbers to text in Excel.

### SpellNumber VBA macro to convert numbers to words

As I have already mentioned, Microsoft didn't want to add a tool for this task. However, when they saw how many users needed it, they created and published the special VBA macro on their website. The macro does what its name SpellNumber suggests. All other macros I came across are based on the Microsoft code.

You can find the macro mentioned as "spellnumber formula". However, it is not a formula, but a macro function, or to be more precise *Excel User defined function* (UDF).

The spellnumber option is able to write dollars and cents. If you need a different currency, you can change "*dollar*" and "*cent*" with the name of your one.

If you are not a VBA savvy guy, below you will find a copy of the code. If you still don't want or haven't time to sort this out, please use this solution.

**1.** Open the workbook where you need to spell the numbers.

**2.** Press *ALT+F11* to open the Visual Basic editor window.

**3.** If you have several books opened, check that the needed workbook is active using the list of projects in the upper left corner of the editor (one of the workbook elements is highlighted with blue).

**4.** In the editor menu go to *Insert*-> *Module*.

**5.** You should see a window named YourBook - Module1. Select all of the code in the frame below and paste it to this window.

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

**6.** Press *Ctrl+S* to save the updated workbook.

You will need to resave your workbook. When you try to save the workbook with a macro you'll get the message "*The following features cannot be saved in macro-free workbook*"

Click No. When you see a new dialog, chose the Save as option. In the field "*Save as type*" pick the option "*Excel macro-enabled workbook*".

#### Use SpellNumber macro in your worksheets

Now you can use the function *SpellNumber* in your Excel documents. Enter *=SpellNumber(A2)* into the cell where you need to get the number written in words. Here A2 is the address of the cell with the number or amount.

Here you can see the result:

**Voila!**

#### Quickly copy the SpellNumber function to other cells.

If you need to convert the entire table, not just 1 cell, place your mouse cursor to the lower right corner of the cell with the formula until it turns into a small black cross:

Left-click and drag it across the column to fill in the formula. Release the button to see the results:

**Note.**Please keep in mind that if you use SpellNumber with a link to another cell, the written sum will be updated each time the number in the source cell is changed.

You can also enter the number directly into the function, for example,

**=SpellNumber(29.95)**(29.95 - without quotation marks and the Dollar sign).

#### Disadvantages of using macro to spell numbers in Excel

First off, you must know VBA to modify the code according to your needs. It is necessary to paste the code for each workbook, where you plan to change it. Otherwise, you will need to create a template file with macros and configure Excel to load this file at each start.

The main disadvantage of using a macro is if you send the workbook to somebody else, this person will not see the text unless the macro is built into the workbook. And even if it's built-in, they will get an alert that there are macros in the workbook.

### Free ready-to-use add-in to spell numbers into words

For active Excel users who need to quickly spell sums but don't want to learn VBA or other workarounds, one kind developer created a free add-in - POPUP SPELL NUMBER for Microsoft Excel

Besides being ready for use, the tool is really flexible in converting numbers. You can choose the text case of the result (lower case, Title Case, Sentence case, UPPER CASE ), also you can select how you want to see the fraction:

- Spells the fraction in cent/cents, оr penny/pennies, оr penny/pence, or centavo/centavos
- Spells decimal point, each zero and the rest of the number as an integer.
- Doesn't spell the fraction but writes it as a fraction with denominator of 100, 1000, 1000000

Feel free to explore its possibilities on the product's home page.

The author states that it's tested and works in Excel for Windows 2000, 2002(XP), 2003, 2007, 2010 (32-bit), and 2013 (32-bit) in MSI-based and Click-To-Run installations of Office 365 cloud-based services.

#### And all is wonderful about POPUP SPELL NUMBER, but...

I hate to say that the **add-in doesn't work with 64-bit versions of Excel** 2010, 2013, 2007. And it is a considerable obstacle since these versions are becoming more and more popular. So lucky you are if you have Excel 32-bit :) Feel free to download the add-in and check it out.

If you are the one with Excel 64-bit like me, drop the author a line and ask him for a special version.

### Reverse conversion - English words into numbers

Frankly, I can't imagine why you may need it. Just in case... :)

It appears that Excel MVP, Jerry Latham, created such Excel User defined function (UDF) as **WordsToDigits**. It converts English words back to number.

You can download Jerry's WordsToDigits workbook to see the UDF code. Here you'll also find his examples of how to use the function.

You can see how the function works on the sheet "*Sample Entries*", where you will also be able to enter your own examples. If you plan to employ WordsToDigits in your documents, please be informed that this function has restrictions. For example, it doesn't recognize fractions entered in words. You will find all the details on the "*Information*" sheet.

### See also

- Merge Excel worksheets by matching data
- Combine Excel sheets with the same name into one
- 20+ smart tools for Excel 2013-2003 to do your daily work

You can also do it by Formula:

=TRIM(IF(A2=10^5,CHOOSE(FLOOR(MOD(A2,10^6)/10^5,1)+1,"","One","Two",Three","Four","Five","Six", "Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen",

"Seventeen","Eighteen","Nineteen")&" Hundred","")&IF(MOD(A2,10^5)>=20000," "&CHOOSE(FLOOR(MOD(A2,10^5)/10^4,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(A2,10^5),10^4)>=10^3," "&CHOOSE(FLOOR(MOD(MOD(A2,10^5),10^4)/10^3,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen", "Seventeen","Eighteen","Nineteen"),""),IF(MOD(A2,10^5)>=10^3," "&CHOOSE(FLOOR(MOD(A2,10^5)/10^3,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&" Thousand") & IF(A2=10^2," "&CHOOSE(FLOOR(MOD(A2,10^3)/10^2,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen", "Seventeen","Eighteen","Nineteen")&" Hundred","")&IF(MOD(A2,10^2)>=20," "&CHOOSE(FLOOR(MOD(A2,10^2)/10,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety"

)&IF(MOD(MOD(A2,10^2),10)>=1," "&CHOOSE(FLOOR(MOD(MOD(A2,10^2),10),1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen", "Seventeen","Eighteen","Nineteen"),""),IF(MOD(A2,10^2)>=1," "&CHOOSE(FLOOR(MOD(A2,10^2),1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&""))

I'm really sorry, when you posted the formula, an error occurred. So and pasting it to Excel directly doesn't work. Please send us the working copy in an Excel file. I will be happy to add your formula to the article and sign it with your name.

Please let me have your email so that I can send you my excel file for you to insert your formula to convert number to words

Thanks a million

Stephen

Please send us the working copy in an Excel file. I will be happy to add your formula. My email address is

digital851@gmail.com

Email sent.

I shall be very thankful to you if you send me excel file

Please send me same excel sheet with formula,

Many Thanks

Dear Stephen Please if you can send me same excel file in my email.I will be thank full to your great support.Thanks Ali Khan

Dear Stephen,

please send me excel sheet for converting same

Please send me the formula!

Sir Please send me excel File.

I Shall be thankful to You for this act of kindness.

Kindly send me also a copy of that formula thanks

Please need it asap

Email ?

How would you trim this formula down if you're only using whole numbers between 1 and 12?

Hi! Thanks for the formula. It works, I have created the VB formula. But what should I do if I only want the words "Six Only" rather than "Six Dollars and None Cents?"

My currency happens to be different so I prefer without the currency in words. Thanks!

There is some Error in this formula becouse the every time after close Excel file then we open next time file we have need to microsoft visual basic for application and save the same seting again and agian. we want to that after one time save the setting the formula contuning in work.

hi sir

please help me to do spellnumber code in check format

Ex. if i type $1,000.15

it will be ***One Thousand Dollar and 15/100 Only***

im so glad if you help me

thank you :D

Hello Raz,

Please check out the POPUP SPELL NUMBER addin.

http://orlando.mvps.org/ExcelSpellNumberMore.asp?IdC=OrlMoreWin2

Option Explicit

'Main Function

Function SpellNumber(ByVal MyNumber)

Dim Riyals, Halalas, 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

Halalas = 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 Riyals = Temp & Place(Count) & Riyals

If Len(MyNumber) > 3 Then

MyNumber = Left(MyNumber, Len(MyNumber) - 3)

Else

MyNumber = ""

End If

Count = Count + 1

Loop

Select Case Riyals

Case ""

Riyals = "No Riyals"

Case "One"

Riyals = "One Riyal"

Case Else

Riyals = Riyals & " Riyals"

End Select

Select Case Halalas

Case ""

Halalas = " and 0 Halalas"

Case "One"

Halalas = " and One Cent"

Case Else

Halalas = " and " & Halalas & " Halalas"

End Select

SpellNumber = Riyals & Halalas

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

very helpful thanks indeed

can you please provide me the easiest way to find spellnumber to convert numbers to words

if my number is $500.07 and my word want to convert to "Say Dollars Five Hundreds and cents Seven Only). What is the format for this?

You need to customize the macro a little bit. I am sorry all our technicians are overloaded with work at the moment and can't help you with this. Please look for the solution on these forums: http://www.Mrexcel.com or http://www.excelforum.com

Thanks for your help...

your solution is amazing but i wonder if i just want the number as it is without dollars without saudi rayal without any currency name , how can i do that in the module

I came across an article on another site, to embed this function permanently into Excel, without it being a macro and creating opening and saving nightmares.

Similar process as listed above, and as you may have seen before, but with a twist...

>> Open a blank workbook in Excel

>> Hit ALT-F11

>> On the top menu, select Insert>Module

>> Copy and paste the above mentioned VBA code (from the original post, way up top)

>> Back in VBA, on the top menu, select File>Save Book#

>> Once in the Save As menu, go down to "Save as type:", click the down arrow, and select "Excel Add-In"

>> Type a file name of your choosing, (I chose SpellNumber) and choose a file location you'll remember. (the predefined location will work just fine, but you may want to note the location, just in case.

>> Close out of the VBA editor

>> On the top main Excel menu, select File>Options

>> In the Excel Options menu, on the left side, select "Add-Ins"

>> In the Add-ins menu, at the bottom, under "Manage:" click the down arrow, and select "Excel Add-ins", and then click "Go..."

>> In the subsequent Add-ins menu, on the right side, click "Browse..."

>> In the Browse pop-up, you may or may not see the add-in that you previously created and saved. If you see it, select it and click OK. If you do not see it, navigate to the location where you saved it, then select the file and click OK.

>> Now back in the previous Add-ins menu, you will see your Add-in in the list. If it's box is not checked, click to check it at this time, then on the right, click OK.

>> Your Add-in will now load every time you open Excel, allowing you to use and perform this function all the time. Also, it is now an add-in, not a macro, so will not create the hassles macro-enabled workbooks carry.

>> To actually use the function, simply start typing =Spellnu the function should auto populate. Double-click the popup, select the cell you want spelled out, close the parenthesis ), and voila!

Thnku.. Nick T...It works..

nick how can i change the currency into peso..

thank you.

Hello change all "dollars" into pesos

works for me

thanks Nick T

If you have added the add in like Nick T instructed above, but you say you are filling a checque in excel and you would want

e.g 4000 to be "four thousand dollars only" and not "four thousand dollars and no cents" but you would also want 4000.36 to be "four thousand dollars and thirty six cents only" when there are cents added.

lets say the number you want to convert is in cell "B" use the following formula

'=IF(NUMBERVALUE(RIGHT(B6,2),".",",")>0,CONCATENATE(UPPER(SpellNumber(B6))," ONLY"),CONCATENATE(LEFT(UPPER(SpellNumber(B6)),LEN(UPPER(SpellNumber(B6)))-12)," ONLY"))

how if we don't want the words "dollars" in our converted sentences.. Pls show me the module, Thank you so much.

Superb

we convert in indian currency e.g 12(tweleve) in this way not dollar

Hi Kudzi

Tried yr formula but was fail. What do you mean by "NUMBERVALUE"?

Best Regards.

thanks it works

Dear, can anyone help me to convert numbers into words like "Nine Thousand and Eighty Five Rupees" instead of "Nine Thousand Eighty Five Rupees and No Cents"? means i don't need cents in conversion.

Hi there,

I really need to fix the format on the big numbers to say "thiry thousand five hundred AND Ninty dollars and fifty four cents"

I need the AND after the five hundred... please help, I just what to know where to fix the code...

I need to convert on checks a number without the dollar sign to words with a dollar sign. Can you send me a spread sheet with a working module. I copied and pasted but get a pop up on the VBA. Thanks

USE Rahim Zulfiqar AlI'S FORMAUL AND COPY PASTE THE RESULT AS TEXT AND MATCH IT WITH THE ADDITIONAL THAT YOU WANT IT BE REDA WITH

=TRIM(IF(A2=10^5,CHOOSE(FLOOR(MOD(A2,10^6)/10^5,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")&" Hundred","")&IF(MOD(A2,10^5)>=20000," "&CHOOSE(FLOOR(MOD(A2,10^5)/10^4,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety")&IF(MOD(MOD(A2,10^5),10^4)>=10^3," "&CHOOSE(FLOOR(MOD(MOD(A2,10^5),10^4)/10^3,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"),""),IF(MOD(A2,10^5)>=10^3," "&CHOOSE(FLOOR(MOD(A2,10^5)/10^3,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"),""))&" Thousand")&IF(A2=10^2," "&CHOOSE(FLOOR(MOD(A2,10^3)/10^2,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")&" Hundred","")&IF(MOD(A2,10^2)>=20," "&CHOOSE(FLOOR(MOD(A2,10^2)/10,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"))&IF(MOD(MOD(A2,10^2),10)>=1," "&CHOOSE(FLOOR(MOD(MOD(A2,10^2),10),1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine")) just TRIED TO FIX SOME OF THE ERRORS ON Rahim Zulfiqar AlI'S FORMAUL BUT STILL IT AIN'T SPELL OUT DIGITS AFTER A POINT; IF SOME ONE COULD HELP ON THAT THAT WOULD BE perfect!! THANK YOU Rahim Zulfiqar AlI and Alexander Frolov thank you TOO using VBA ur formal works perfect but in MY office VBA is deactivated

plsss....I tried it.... but I got error message... LOOP WITHOUT DO.....plss..kindly send the excel file to my mail..... ifelekods @gmail.com. thanks

help me on below codes,to be faster it's taking to wrong to execute .i have tried all in vain

Sub CheckoutBook()

Application.ScreenUpdating = False

Application.DisplayAlerts = False

Application.DisplayStatusBar = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False

Dim studentID As String

studentID = Worksheets("main").Cells(4, 4).Value

Dim studentFirstName As String

studentFirstName = Worksheets("main").Cells(5, 4).Value

Dim studentLastName As String

studentLastName = Worksheets("main").Cells(6, 4).Value

Dim bookID As String

bookID = Worksheets("main").Cells(9, 4).Value

Dim bookTitle As String

bookTitle = Worksheets("main").Cells(10, 4).Value

checkoutDuration = Worksheets("main").Cells(4, 8).Value

If checkoutDuration 365 Then

MsgBox ("Checkout duration cannot be more than 365 days")

ElseIf studentFirstName = "" Then

MsgBox ("Student first name is empty")

ElseIf studentLastName = "" Then

MsgBox ("Student last name is empty")

ElseIf bookTitle = "" Then

MsgBox ("Book title is empty")

ElseIf bookID = "" Then

MsgBox ("Book ID is empty")

Else

Dim checkOutID As String

checkOutID = bookID & studentID

With Sheets("checkouts").Range("K:K")

Set DupRng = .Find(What:=checkOutID, _

After:=.Cells(.Cells.Count), _

LookIn:=xlValues, _

LookAt:=xlWhole, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False)

End With

If DupRng Is Nothing Then

With Sheets("checkouts").Range("A:A")

Set rng = .Find(What:="", _

After:=.Cells(.Cells.Count), _

LookIn:=xlValues, _

LookAt:=xlWhole, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False)

rng.Cells(1, 1).Value = bookID

rng.Cells(1, 2).Value = bookTitle

rng.Cells(1, 3).Value = studentID

rng.Cells(1, 4).Value = studentFirstName

rng.Cells(1, 5).Value = studentLastName

rng.Cells(1, 6).Value = Now()

rng.Cells(1, 7).Value = Now() + checkoutDuration

Application.ScreenUpdating = True

Application.DisplayAlerts = True

Application.DisplayStatusBar = statusBarState

Application.Calculation = calcState

Application.EnableEvents = eventsState

ActiveSheet.DisplayPageBreaks = displayPageBreaksState

MsgBox ("Book successfully checked out")

End With

Else

MsgBox ("This student has already checked out this book. Checkout failed.")

End If

End If

End Sub

Hello Dan,

I am sorry, I can't help you with your task. Please try to find the solution on these forums: http://www.mrexcel / http://www.excelforum.com.

how can i change from Dollar to Saudi Riyal

JUST SEARCH IN VB ""DOLLARS""""" REPLACE WITH """SAUDI RAYAL"""

SIMPLE

GREAT HELP . THANKS

THE WORD """DOLLARS""" AND """CENTS"""

YOU CAN ALSO USE THE CURRENCY OF YOUR COUNTRY LIKE

I USE................. """RUPEES""" AND """PAISA"""

AND ITS WORKING FINE..............

FOR PAKISTANI CURRENCY..............

Very great. Kudos to all who contributed to this knowledge sharing medium. However, it can be improved upon by adding seperators in form of commas ','. For example, converting $1,500.00 into words would be 'One thousand five hundred dollars' rather than 'One thousand, five hundred dollars'.

Hi, how can I convert 1,547.50 into "One thousand five hundred forty seven and cents fifty only"?

I need help from some excel masters. I need excel convert from numbers like $1234.50 ...to text, but writing the cents in the form of 50/100 and word USD at the end...I would really apreciate somebody share this trick please.

Hi! Thanks for the formula. It works, I have created the VB formula. But I Just need "Ten Thousand" rather than "Ten Thousand Dollors And None Cents?"

FOR IN RUPEES CONVERTER ONLY NO PAISA

USE ALT+F11

AND INSERT FUNCTION GO MOUDEL

PASTE THIS FORMULA

Option Explicit

'Main Function

Function SpellNumber(ByVal MyNumber)

Dim Rupees, 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, ".")

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 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

SpellNumber = Rupees

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

AND THEN

=CONCATENATE(TEXT((spellnumber(B19)),0)," Only")

THANKING YOU

VINAYAK(BF.TECH)

how to the change the format to Lakh & Crores instead of Million & Trillion

I need your help,

Please know me Lac & Croers formula,

very urgent your co operation.

Best Regards

Samir.

Dhaka.

THANKS

DEAR FRIENDS, CAN I HAVE THE SAME THAT CAN AUTO SELECT CURRENCY ,,

SOMETIME IT MAY BE IN USD, EUR, AED, SAR, KWD

AND SO ON . SO HOW TO ENABLE THE FORMULA FOR MULTIPLE CURRENCY ..

THANKS

Dear

We are working in Oman .SO can you please send us the VBA MICRO code for Oman Currency.We write omani currency in three decimal palce

e.g 450.560 four hundred fifty Rials and five hundred sixty Baiza.

Pease help me to convert nuemerical Omani currencyin word in excel.

WITH REGARDS

MOHAMMED IFTEKHAR AHMED

You can also do it by my function :

Option Explicit

Option Base 0

'Author: thaipv@live.com

'Date: 30/11/2014

'Reference: http://support.microsoft.com/kb/95640

'More: http://www.techonthenet.com/excel/formulas/curr_to_words.php

Function READNUMBER(ByVal CNUMBER, Optional UPPER As Boolean = True) As String

Dim DIGIT, ATEEN, ATENS, PLACE(13), READ1__$, READ_23$, READ123$

Dim NUMBER1 As Byte, NUMBER2 As Byte, NUMBER3 As Byte, V As Byte

DIGIT = Array("", " one", " two", " three", " four", " five", " six", " seven", " eight", " nine")

ATEEN = Array(" ten", " elevent", " twelve", " thirteen", " fourteen", " fifteen", " sixteen", " seventeen", " eighteen", " nineteen")

ATENS = Array("", "", " twenty", " thirty", " forty", " fifty", " sixty", " seventy", " eighty", " ninety")

PLACE(1) = " trillion,": PLACE(4) = " billion,"

PLACE(7) = " million,": PLACE(10) = " thousand,"

If Trim$(CNUMBER) = "" Then

READNUMBER = ""

ElseIf Not IsNumeric(CNUMBER) Then

READNUMBER = "(ò_ó) Hmm, It is too embarrassing! XA can not read this number: " & "'" & CNUMBER & "'"

ElseIf CNUMBER 10 ^ 15 Then

READNUMBER = "(ò_ó) Hmm, It is too embarrassing! This number is too large to read."

Else

'// Doc dau am/duong cua so tien va xy ly so tien truoc khi doc//

READNUMBER = IIf(Round(CNUMBER, 0) < 0, "minus", "")

CNUMBER = Round(Abs(CNUMBER), 0): CNUMBER = "000000000000000" & CNUMBER

CNUMBER = Replace$(CNUMBER, ",", ""): CNUMBER = Right$(CNUMBER, 15)

'// Bat dau doc so //

For V = 1 To 13 Step 3

NUMBER1 = Mid$(CNUMBER, V, 1)

NUMBER2 = Mid$(CNUMBER, V + 1, 1)

NUMBER3 = Mid$(CNUMBER, V + 2, 1)

READ123 = ""

READ1__ = IIf(NUMBER1 = 0, "", DIGIT(NUMBER1) & " hundread")

Select Case NUMBER2

Case 0: READ_23 = DIGIT(NUMBER3)

Case 1: READ_23 = ATEEN(NUMBER3)

Case Else: READ_23 = ATENS(NUMBER2) & DIGIT(NUMBER3)

End Select

READ123 = READ1__ & READ_23

READNUMBER = Trim$(READNUMBER & IIf(Len(READ123) = 0, "", READ123 & PLACE(V)))

Next V

'// Xu ly (nhung) dau phay (",") du thua va VIET HOA chu dau tien //

READNUMBER = IIf(Right$(READNUMBER, 1) = ",", Left$(READNUMBER, Len(READNUMBER) - 1), READNUMBER)

READNUMBER = IIf(UPPER = True, UCase$(Left$(READNUMBER, 1)) & Mid$(READNUMBER, 2), READNUMBER)

End If

End Function

I want to convert numeric value with word form without any currency.

Pl help me

HI, im unable to understand and work the formula pls help..

great it worked for me perfectly

By this Indian rupees and paise is not convert in words.Plz solve this problem.

How to insert Rupees instead of Dollar and Paise instead of Cent in the outcome from above =spellnumber() function ?

in Indian Format (Excel Formula)

=TRIM(IF(C6=1," Rupee","")&IF(C6>1," Rupees","")&IF(MOD(C6,10^15)>=(2*(10^14))," "&CHOOSE(FLOOR(MOD(C6,10^15)/10^14,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C6,10^15),10^14)>=10^13," "&CHOOSE(FLOOR(MOD(MOD(C6,10^15),10^14)/10^13,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C6,10^15)>=10^13," "&CHOOSE(FLOOR(MOD(C6,10^15)/10^13,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(C6,10^15)>=10^13," Neel"," ")&IF(MOD(C6,10^13)>=(2*(10^12))," "&CHOOSE(FLOOR(MOD(C6,10^13)/10^12,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C6,10^13),10^12)>=10^11," "&CHOOSE(FLOOR(MOD(MOD(C6,10^13),10^12)/10^11,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C6,10^13)>=10^11," "&CHOOSE(FLOOR(MOD(C6,10^13)/10^11,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(C6,10^13)>=10^11," Kharab"," ")&IF(MOD(C6,10^11)>=(2*(10^10))," "&CHOOSE(FLOOR(MOD(C6,10^11)/10^10,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C6,10^11),10^10)>=10^9," "&CHOOSE(FLOOR(MOD(MOD(C6,10^11),10^10)/10^9,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C6,10^11)>=10^9," "&CHOOSE(FLOOR(MOD(C6,10^11)/10^9,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(C6,10^11)>=10^9," Arab"," ")&IF(MOD(C6,10^9)>=200000000," "&CHOOSE(FLOOR(MOD(C6,10^9)/10^8,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C6,10^9),10^8)>=10^7," "&CHOOSE(FLOOR(MOD(MOD(C6,10^9),10^8)/10^7,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C6,10^9)>=10^7," "&CHOOSE(FLOOR(MOD(C6,10^9)/10^7,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(C6,10^9)>=10^7," Crore"," ")&IF(MOD(C6,10^7)>=2000000," "&CHOOSE(FLOOR(MOD(C6,10^7)/10^6,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C6,10^7),10^6)>=10^5," "&CHOOSE(FLOOR(MOD(MOD(C6,10^7),10^6)/10^5,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C6,10^7)>=10^5," "&CHOOSE(FLOOR(MOD(C6,10^7)/10^5,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(C6,10^7)>=10^5," Lakh"," ")&IF(MOD(C6,10^5)>=20000," "&CHOOSE(FLOOR(MOD(C6,10^5)/10^4,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C6,10^5),10^4)>=10^3," "&CHOOSE(FLOOR(MOD(MOD(C6,10^5),10^4)/10^3,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C6,10^5)>=10^3," "&CHOOSE(FLOOR(MOD(C6,10^5)/10^3,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(C6,10^5)>=10^3," Thousand"," ")&IF(C6>=10^2," "&CHOOSE(FLOOR(MOD(C6,10^3)/10^2,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),"")&IF(MOD(C6,10^3)>=10^2," Hundred"," ")&IF(MOD(C6,10^2)>=20," "&CHOOSE(FLOOR(MOD(C6,10^2)/10,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C6,10^2),10)>=1," "&CHOOSE(FLOOR(MOD(MOD(C6,10^2),10),1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C6,10^2)>=1," "&CHOOSE(FLOOR(MOD(C6,10^2),1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(AND(C6>0.99,MOD(RIGHT((ROUND(C6,2))*100,2),10^2)>0)," and","")&IF(MOD(RIGHT((ROUND(C6,2))*100,2),10^2)>0," Paise","")&IF(MOD(RIGHT((ROUND(C6,2))*100,2),10^2)>=20," "&CHOOSE(FLOOR(MOD(RIGHT((ROUND(C6,2))*100,2),10^2)/10,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(RIGHT((ROUND(C6,2))*100,2),10^2),10)>=1," "&CHOOSE(FLOOR(MOD(MOD(RIGHT((ROUND(C6,2))*100,2),10^2),10),1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(RIGHT((ROUND(C6,2))*100,2),10^2)>=1," "&CHOOSE(FLOOR(MOD(RIGHT((ROUND(C6,2))*100,2),10^2),1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(C6>0.01," Only",""))

Thank you very much for creation of Currency in to word Formula.

it helps me 100% in my profession life..... which solution was searching last 2 years.

Today, i achieved solution.

Wah yar Zabardast.

in Million Format (Excel Formula)

=TRIM(IF(C5>=10^14," "&CHOOSE(FLOOR(MOD(C5,10^15)/10^14,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),"")&IF(MOD(C5,10^15)>=10^14," Hundred"," ")&" "&IF(MOD(C5,10^14)>=2*10^13," "&CHOOSE(FLOOR(MOD(C5,10^14)/10^13,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C5,10^14),10^13)>=10^12," "&CHOOSE(FLOOR(MOD(MOD(C5,10^14),10^13)/10^12,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C5,10^14)>=10^12," "&CHOOSE(FLOOR(MOD(C5,10^14)/10^12,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(C5,10^15)>=10^12," Trillion"," ")&" "&IF(C5>=10^11," "&CHOOSE(FLOOR(MOD(C5,10^12)/10^11,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),"")&IF(MOD(C5,10^12)>=10^11," Hundred"," ")&" "&IF(MOD(C5,10^11)>=2*10^10," "&CHOOSE(FLOOR(MOD(C5,10^11)/10^10,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C5,10^11),10^10)>=10^9," "&CHOOSE(FLOOR(MOD(MOD(C5,10^11),10^10)/10^9,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C5,10^11)>=10^9," "&CHOOSE(FLOOR(MOD(C5,10^11)/10^9,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(C5,10^12)>=10^9," Billion"," ")&" "&IF(C5>=10^8," "&CHOOSE(FLOOR(MOD(C5,10^9)/10^8,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),"")&IF(MOD(C5,10^9)>=10^8," Hundred"," ")&" "&IF(MOD(C5,10^8)>=2*10^7," "&CHOOSE(FLOOR(MOD(C5,10^8)/10^7,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C5,10^8),10^7)>=10^6," "&CHOOSE(FLOOR(MOD(MOD(C5,10^8),10^7)/10^6,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C5,10^8)>=10^6," "&CHOOSE(FLOOR(MOD(C5,10^8)/10^6,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(C5,10^9)>=10^6," Million"," ")&" "&IF(C5>=10^5," "&CHOOSE(FLOOR(MOD(C5,10^6)/10^5,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),"")&IF(MOD(C5,10^6)>=10^5," Hundred"," ")&" "&IF(MOD(C5,10^5)>=20000," "&CHOOSE(FLOOR(MOD(C5,10^5)/10^4,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C5,10^5),10^4)>=10^3," "&CHOOSE(FLOOR(MOD(MOD(C5,10^5),10^4)/10^3,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C5,10^5)>=10^3," "&CHOOSE(FLOOR(MOD(C5,10^5)/10^3,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(C5,10^6)>=10^3," Thousand"," ")&IF(C5>=10^2," "&CHOOSE(FLOOR(MOD(C5,10^3)/10^2,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),"")&IF(MOD(C5,10^3)>=10^2," Hundred"," ")&IF(MOD(C5,10^2)>=20," "&CHOOSE(FLOOR(MOD(C5,10^2)/10,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C5,10^2),10)>=1," "&CHOOSE(FLOOR(MOD(MOD(C5,10^2),10),1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C5,10^2)>=1," "&CHOOSE(FLOOR(MOD(C5,10^2),1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(C5=1," Dollar","")&IF(C5>1," Dollars","")&IF(AND(C5>0.99,MOD(RIGHT((ROUND(C5,2))*100,2),10^2)>0)," and","")&IF(MOD(RIGHT((ROUND(C5,2))*100,2),10^2)>=20," "&CHOOSE(FLOOR(MOD(RIGHT((ROUND(C5,2))*100,2),10^2)/10,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(RIGHT((ROUND(C5,2))*100,2),10^2),10)>=1," "&CHOOSE(FLOOR(MOD(MOD(RIGHT((ROUND(C5,2))*100,2),10^2),10),1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(RIGHT((ROUND(C5,2))*100,2),10^2)>=1," "&CHOOSE(FLOOR(MOD(RIGHT((ROUND(C5,2))*100,2),10^2),1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(RIGHT((ROUND(C5,2))*100,2),10^2)=1," Cent","")&IF(MOD(RIGHT((ROUND(C5,2))*100,2),10^2)>1," Cents",""))

In Indian Format you can convert up to neel rupee

&

In million format you can convert up to trillion dollar

You can change your currency by find and replace rupee-paise or dollar-cent to any other currency

Or

You can also use it without any currency just find and replace rupee-paise or dollar-cent with blank (spacebar)

sir i got that formula and its works at once.but when i close excel and reopen then #name error comes and format formula has been out

hi how will i convert 100.47 into "one hundred & 47/100 Only"

i tried suggested response but all of them are not working did i missed something. can i have the detailed steps

thank you

Hi,

I have an Problem. the Number convert into word the following example. I want (two lakh four hundred seventy four dollars and 24 Cents). Please help me.

2,00,474.25

Two Hundred Thousand Four Hundred Seventy Four Dollars and Twenty Five Cents

sir i got that formula and its works at once.but when i close excel and reopen then #name error comes and format formula has been out

I would like complete this using the formula only (no macro) and also have the text read the cents as "55/100 DOLLARS". So, for example, $7,547.55 = "SEVEN THOUSAND FIVE HUNDRED FORTY-SEVEN AND 55/100 DOLLARS" Can anyone help with that? I'd prefer not to use a macro, if possible.

I want amount 500.260 as " Five hundered Riyal and 260 Baisa Only" How can i get do it decimals till 3 digits and also in numeric.

Thank you so much! Works like a charm!!!!!! (=spellnumber) - Just what the doctor ordered!

these formulas are not working

all formulas showing error

Alexandre,

Thank you for your help! This article is excellent!

I have only one question. The formula is working perfect when I have the spreadsheet saved in my own hard disk, but when I save the file on my share drive the formula doesn't work. Is there any way to fix it?

Greetings Sir.

I want code for text of spell number in Indian Rupees and Paisas with shoing Rupees at start of text figure. for example

1000.50 is to text as

Rupees One Thousand and fifty paisas only.

Thanks for your urgent reply

Hi Sir,

Please send me formula for write only for the words without dollars ans cents

Hi!

I still am not able to convert figures in words. Can you please send me the formula in excel sheet without any currency.

Thanks

"Reverse conversion - English words into numbers

Frankly, I can't imagine why you may need it. Just in case... :)"

Thank you so much - this was exactly what we were looking for!!! Kudos!

Need this sample spellnumber, please help anybody me, for share spell number in my email, exp: 125000=Twelve milion five thousand this formula

(100000=one Million) need this formula (No need currency)

This is to inform you that I m using Ms office 7 . The issue is that when i use any amount in excel , it gets converted into words only in the file which i have saved it.

When I open a new fle, it doesn't automatically accept it. I have to use the formula again in the new sheet to convert it. So, is there any setting in Ms office 7 that I can convert numbers into alphabetical while writing amount in excel in each and every file.

For Eg:

10000 - Ten Thousand.

Plz help.

I want to convert number to word in nepali format .But I don't know how to create formula in excel sheet.

Hello. I want to calculate numeric to inward and linking in The Excel2010. How to possible please inform me by sending mail.

Very Very help full this method thank you very much

Hi, I have made a slight change to the code and it allows me to spellnumber as eg.Two hundreds and Cents Thirteen Only. However, i am facing a problem when the amount turns out with 3 decimal places e.g. $200.137. I need the spell number to be $200.14 and not spelled as $200.13. Could you help? Thank you very much.

Good evening Sir,

How to Covert date of birth into worlds

Exm: 02/02/1966

Second february ninteen sixty six

is any formula pl

In Indian currency format we write Rupees One crore ninety nine lac ninety nine thousand nine hundred ninety nine and ninety nine paisas. In your formula we need little change. Million, billion and trillion should be replaced with Lacs and Crores in proper places. Here is some tips:

1 million = 10 lacs

10 million = 1 crore.

We can write lacs limited up to ninety nine lac but crore is ulimited up to some extent. E.g. 100 crores, 10000 crores etc.

Can you please take some burden to upgrade your version for indian format. Thanks in advance.

how to convert 1,342.50 in to words as per indian currency

When I wrote =spellnumber(A2)

It shows name error.

How I can change numeric to word in rupees please solve my problem thanks

I am from INDIA and i use my own Function. My currency is Rupees and Paise(coins)

Function SPELL(amt As Variant) As Variant

Dim FIGURE As Variant

Dim FIGLEN As Integer

Dim i As Integer, Iden(4) As String

Dim WORDs(19) As String

Dim tens(9) As String

WORDs(1) = "One"

WORDs(2) = "Two"

WORDs(3) = "Three"

WORDs(4) = "Four"

WORDs(5) = "Five"

WORDs(6) = "Six"

WORDs(7) = "Seven"

WORDs(8) = "Eight"

WORDs(9) = "Nine"

WORDs(10) = "Ten"

WORDs(11) = "Eleven"

WORDs(12) = "Twelve"

WORDs(13) = "Thirteen"

WORDs(14) = "Fourteen"

WORDs(15) = "Fifteen"

WORDs(16) = "Sixteen"

WORDs(17) = "Seventeen"

WORDs(18) = "Eighteen"

WORDs(19) = "Nineteen"

tens(2) = "Twenty"

tens(3) = "Thirty"

tens(4) = "Fourty"

tens(5) = "Fifty"

tens(6) = "Sixty"

tens(7) = "Seventy"

tens(8) = "Eighty"

tens(9) = "Ninety"

Iden(1) = "Crore"

Iden(2) = "Lakh"

Iden(3) = "Thousand"

Iden(4) = "Hundred"

FIGURE = amt

FIGURE = Format(FIGURE, "FIXED")

FIGLEN = Len(FIGURE)

'Rupees/Rupee/Paise Indentifier

If Val(Left(FIGURE, FIGLEN)) = 1 Then

SPELL = "Rupee"

ElseIf Val(Left(FIGURE, FIGLEN)) < 1 Then

SPELL = "Paise"

Else

SPELL = "Rupees"

End If

'@@@@@@@@@ BIG LOOP @@@@@@@@@

Do

'##### Value Identifier #####

Select Case FIGLEN

Case 12, 10, 8, 5

If Val(Left(FIGURE, 2)) = 1 Then

SPELL = SPELL & " " & WORDs(Val(Left(FIGURE, 2)))

ElseIf Val(Left(FIGURE, 2)) >= 20 And Val(Left(FIGURE, 2)) < 100 Then

SPELL = SPELL & " " & tens(Val(Left(FIGURE, 1))) & " " & WORDs(Mid(FIGURE, 2, 1))

End If

Case 11, 9, 7, 6, 4

SPELL = SPELL & " " & WORDs(Val(Left(FIGURE, 1)))

End Select

'##### Identor Identifier #####

Select Case FIGLEN

Case 12, 11

SPELL = SPELL & " " & Iden(1)

Case 10, 9

SPELL = SPELL & " " & Iden(2)

Case 8, 7

SPELL = SPELL & " " & Iden(3)

Case 6

SPELL = SPELL & " " & Iden(4)

Case 5, 4

Exit Do

End Select

'##### For Next Case #####

Select Case FIGLEN

Case 12, 10, 8, 5

FIGURE = Right(FIGURE, FIGLEN - 2)

FIGLEN = Len(FIGURE)

Case 11, 9, 7, 6

FIGURE = Right(FIGURE, FIGLEN - 1)

FIGLEN = Len(FIGURE)

Case 4, 3

Exit Do

End Select

If Val(Left(FIGURE, FIGLEN - 3)) = 0 Then Exit Do

'@@@@@@@@ LOOP CONDITION @@@@@@@@

Loop Until FIGLEN = 4

'##### PAISE VALUE #####

If Val(Right(FIGURE, 2)) = 1 Then

SPELL = SPELL & " " & "and" & " " & WORDs(Val(Right(FIGURE, 2))) & " " & "Paise"

ElseIf Val(Right(FIGURE, 2)) >= 20 And Val(Right(FIGURE, 2)) < 100 Then

SPELL = SPELL & " " & "and" & " " & tens(Val(Left(Right(FIGURE, 2), 1))) & " " & WORDs(Right(FIGURE, 1)) & " " & "Paise"

End If

'##### ENDING WITH ONLY #####

SPELL = SPELL & " " & "Only."

End Function

Try Function by typing without quotes "=UPPER(SPELL(A1))"

How to Use TRIM formula Please Help Me

is ther eany way to dynamically change the currency depending on the currency format in the reference cell ..

Ah...that's so good for share your such a cool informative method. It benefits for me to apply to my job.

Although, I have been using Excel program for my office work, yet I think I still know it too little if compare to yours.

Thanks,

Dear Sir

I have been working in Oman and I Need the VBA code for Omani currency. First I would like to give some information.

Currency name=Omani Rial

1 Omani Rial=1000 Baiza

So I need it in international system. I have been facing the problem like -1256475.256. how to spell after decimal sir? Please send me the vba code in my mail id-purushottamthapa568@gmail.com. I would you be very grateful .

Please sir help me to spell number to words in Omani rial.

1 OMR=1000 Baiza

so I need to change like this

234.345

I mean i need to spell 3 digit after decimal as Omani Rial is prounced like this.

Thanks in advance.

I wrote this.

It works with numbers 0 Then

Dim G(5, 3) As Integer

For j = 1 To 5

For k = 3 To 1 Step -1

I = I / 10

G(j, k) = (I - Int(I)) * 10

I = Int(I)

Next

Next

For j = 5 To 1 Step -1

If G(j, 1) + G(j, 2) + G(j, 3) = 0 Then

Else

If G(j, 1) > 0 Then L$ = L$ & Words(G(j, 1)) & Words(30)

If G(j, 2) = 1 Then

L$ = L$ & Words(G(j, 3) + 10)

Else

L$ = L$ & Words(G(j, 2) + 20) & Words(G(j, 3))

End If

L$ = L$ & Words(j + 30)

End If

Next

End If

If D(1) = 1 Then

F$ = Words(D(2) + 10)

Else

If D(1) > 0 Then F$ = F$ & Words(D(1) + 18)

If D(2) > 0 Then F$ = F$ & Words(D(2))

If D(1) = 0 And D(2) = 0 Then F$ = "no"

End If

NumToDol = Trim(L$) & " Dollars" & " and " & F$ & " Cents"

End Function

Sorry,the previous message was published incomplete.

I retry to post it.

It works with numbers big as 999.999.999.999.999,19

For example

137.895,19 results as

One Hundred Thirty Seven Thousand Eight Hundred Ninety Five Dollars and Nineteen Cents

Public Function NumToDol(N) As String

Words = Array("", " One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", " Ten", " Eleven", " Twelve", " Thirteen", " Fourteen", " Fifteen", " Sixteen", " Seventeen", " Eighteen", " Nineteen", "", "", " Twenty", " Thirty", " Forty", " Fifty", " Sixty", " Seventy", " Eighty", " Ninety", " Hundred", "", " Thousand", " Million", " Billion", " Trillion")

I = Int(N)

dd = Round(N - I, 2)

Dim D(1 To 2)

For j = 1 To 2

dd = dd * 10

D(j) = Int(dd)

dd = dd - Int(dd)

Next

If I = 0 Then L$ = "Zero"

If I > 0 Then

Dim G(5, 3) As Integer

For j = 1 To 5

For k = 3 To 1 Step -1

I = I / 10

G(j, k) = (I - Int(I)) * 10

I = Int(I)

Next

Next

For j = 5 To 1 Step -1

If G(j, 1) + G(j, 2) + G(j, 3) = 0 Then

Else

If G(j, 1) > 0 Then L$ = L$ & Words(G(j, 1)) & Words(30)

If G(j, 2) = 1 Then

L$ = L$ & Words(G(j, 3) + 10)

Else

L$ = L$ & Words(G(j, 2) + 20) & Words(G(j, 3))

End If

L$ = L$ & Words(j + 30)

End If

Next

End If

If D(1) = 1 Then

F$ = Words(D(2) + 10)

Else

If D(1) > 0 Then F$ = F$ & Words(D(1) + 18)

If D(2) > 0 Then F$ = F$ & Words(D(2))

If D(1) = 0 And D(2) = 0 Then F$ = "no"

End If

NumToDol = Trim(L$) & " Dollars" & " and " & F$ & " Cents"

End Function

I want to convert 11 into " ONE ONE ".

12 into " ONE TWO " AND SO ON.

It's possible or not????

IF ITS POSSIBLE THEN WHAT IS THE FORMULA????

Hi Ronak,

Follow the steps below to split the numbers you have mentioned and any other number.

1. Highlight the column you want the numbers to be split.

2. On the formula tab select Data and then select Text to columns. Step 1 of 3: Of the two option in the dialogue box select Fixed width and click on Next.

3. Step 2 of 3:In the lower section of the dialogue box, Data preview, section place your cursor where you want the numbers to be split and click on Next.

4. Step 3 of 3: Select "General" in the column data format section and in the Destination option select the cell you want the data to be split.

When you click on Finish you will see the data split in the number of columns depending on the number of digits.

Word of caution - The original data will no longer be available so if you need to see the data in its original form then retain the original column and split the numbers from a copied column next to the original to for verifying purposes.

Regards.

Please use following formulas and change cell references according to your sheet and dont forget to add a 1-99 numbers and their words in sheet1 like A1=1,B1=One and up to 99. This works and dont need micros also.

=CONCATENATE(IF(IF(QUOTIENT($A$3,10000000)0,VLOOKUP(QUOTIENT($A$3,10000000),Sheet1!$A$1:$B$99,2),"")"",CONCATENATE(IF(QUOTIENT($A$3,10000000)0,VLOOKUP(QUOTIENT($A$3,10000000),Sheet1!$A$1:$B$99,2),"")," Cror "),""),IF(IF(QUOTIENT(MOD($A$3,10000000),100000)0,VLOOKUP(QUOTIENT(MOD($A$3,10000000),100000),Sheet1!$A$1:$B$99,2),"")"",CONCATENATE(IF(QUOTIENT(MOD($A$3,10000000),100000)0,VLOOKUP(QUOTIENT(MOD($A$3,10000000),100000),Sheet1!$A$1:$B$99,2),"")," Lac "),""),IF(IF(QUOTIENT(MOD($A$3,100000),1000)0,VLOOKUP(QUOTIENT(MOD($A$3,100000),1000),Sheet1!$A$1:$B$99,2),"")"", CONCATENATE(IF(QUOTIENT(MOD($A$3,100000),1000)0,VLOOKUP(QUOTIENT(MOD($A$3,100000),1000),Sheet1!$A$1:$B$99,2),"")," Thousand "),""),IF(IF(QUOTIENT(MOD(MOD($A$3,100000),1000),100)0,VLOOKUP(QUOTIENT(MOD(MOD($A$3,100000),1000),100),Sheet1!$A$1:$B$99,2),"")"",CONCATENATE(IF(QUOTIENT(MOD(MOD($A$3,100000),1000),100)0,VLOOKUP(QUOTIENT(MOD(MOD($A$3,100000),1000),100),Sheet1!$A$1:$B$99,2),"")," Hundred "),""),IF(MOD(MOD(MOD($A$3,100000),1000),100)0,VLOOKUP(MOD(MOD(MOD($A$3,100000),1000),100),Sheet1!$A$1:$B$99,2)))

=CONCATENATE(IF(IF(QUOTIENT($A$3,10000000)0,VLOOKUP(QUOTIENT($A$3,10000000),Sheet1!$A$1:$B$99,2),"")"",CONCATENATE(IF(QUOTIENT($A$3,10000000)0,VLOOKUP(QUOTIENT($A$3,10000000),Sheet1!$A$1:$B$99,2),"")," Cror "),""),IF(IF(QUOTIENT(MOD($A$3,10000000),100000)0,VLOOKUP(QUOTIENT(MOD($A$3,10000000),100000),Sheet1!$A$1:$B$99,2),"")"",CONCATENATE(IF(QUOTIENT(MOD($A$3,10000000),100000)0,VLOOKUP(QUOTIENT(MOD($A$3,10000000),100000),Sheet1!$A$1:$B$99,2),"")," Lac "),""),IF(IF(QUOTIENT(MOD($A$3,100000),1000)0,VLOOKUP(QUOTIENT(MOD($A$3,100000),1000),Sheet1!$A$1:$B$99,2),"")"", CONCATENATE(IF(QUOTIENT(MOD($A$3,100000),1000)0,VLOOKUP(QUOTIENT(MOD($A$3,100000),1000),Sheet1!$A$1:$B$99,2),"")," Thousand "),""),IF(IF(QUOTIENT(MOD(MOD($A$3,100000),1000),100)0,VLOOKUP(QUOTIENT(MOD(MOD($A$3,100000),1000),100),Sheet1!$A$1:$B$99,2),"")"",CONCATENATE(IF(QUOTIENT(MOD(MOD($A$3,100000),1000),100)0,VLOOKUP(QUOTIENT(MOD(MOD($A$3,100000),1000),100),Sheet1!$A$1:$B$99,2),"")," Hundred "),""),IF(MOD(MOD(MOD($A$3,100000),1000),100)0,VLOOKUP(MOD(MOD(MOD($A$3,100000),1000),100),Sheet1!$A$1:$B$99,2)))

Please send me a formula.

i'm using Microsoft 2010.

hoping for your immediate reply.

Thank you,

Please send me a formula for the number to word.

i'm using Microsoft 2010.

hoping for your immediate reply.

Thank you,

I convert degree s for student therfore I want to appear only the word with out any other add.

ex: 0 appear zero

no thing appear no thing

41 appear forty one

Thank u very much it's works perfectly nice job.

Hi,

is it possible to get this formula for use in french.

Great job

Regards

There IS An Error In Formula

Dear sir/Madam

Kindly request to send excel file - to convert numbers to words in Excel