How to Convert Numbers to Words in Excel Using a Formula?

Sharing is caring!

Have you ever needed to convert numbers to words in Excel? Maybe you’re creating an invoice or financial report and want the total amount to be written out in words for clarity and professionalism. Or perhaps you need to generate a check and want the dollar amount spelled out in words to meet banking requirements.

Whatever the reason, Excel provides a powerful way to convert numbers into their English word equivalents using a formula. In this comprehensive guide, we’ll walk you through the step-by-step process to set up and use this formula in your Excel spreadsheets.

Why Convert Numbers to Words in Excel?

There are several compelling reasons you might want to write out numbers in words in Excel:

1. Enhanced Clarity and Professionalism: In formal business or financial documents, having the numeric amounts also written out in words adds clarity and a level of professionalism. It helps avoid ambiguity and makes your documents look more polished.
2. Meeting Legal or Banking Requirements: Some legal documents or checks may require the amount to be stated in both numeric and word form for validity. Converting numbers to words in Excel helps you meet these requirements efficiently.
3. Improved Accessibility: Having numbers spelled out in word form can make your Excel document more accessible for people using screen readers or other assistive technologies.
4. Reduced Risk of Errors: Showing an amount in words alongside the numeric digits provides an extra layer of verification to ensure accuracy and catch costly typos or errors.
5. Flexibility in Document Creation: Whether you’re generating invoices, financial statements, checks, or other number-heavy documents, having the ability to switch between numbers and words in Excel gives you more flexibility and control over your document’s presentation.

Setting Up the Numbers to Words Formula in Excel

The general approach to converting numbers to words in Excel involves using a formula to break the number into parts (e.g. thousands, hundreds, tens, ones), translating each part into the corresponding English words, and then stitching the translated parts back together into a complete word string. Here’s a detailed breakdown of the steps:

Step 1: Organize Your Excel Spreadsheet

Begin by entering your numbers in a single column in your Excel spreadsheet. For this example, let’s assume the numbers are entered in column A, starting from cell A2.

Leave some empty cells to the right of your numbers column, as this is where we’ll display the numbers in word form using our formula. In this example, we’ll output the words starting in column C.

Step 2: Understand the Logic Behind the Numbers to Words Formula

The complete formula for converting numbers to words is rather long and complex, as it uses a combination of Excel functions including IF, INT, MOD, ROUND, INDEX, LOOKUP, and TRIM. Here’s a high-level overview of what the formula does:

1. Splits the original number into parts (thousands, hundreds, tens, ones)
2. Uses lookup tables to translate each numeric part into the corresponding English words
3. Concatenates the translated word parts back together into a complete string
4. Handles special cases like negative numbers, decimals, and zero

Understanding this general logic will help you customize and troubleshoot the formula as needed.

Step 3: Create the Number-to-Word Lookup Tables

The first part of setting up the formula is to create lookup tables that map number values to their English word equivalents. We’ll create three tables:

1. Ones (0-9)
2. Tens (0-90)
3. Scale (thousand, million, billion, etc.)

Here’s how to set up each table in your spreadsheet:

Ones Lookup Table

Enter the numbers 0-9 in cells G2:G11, and enter the corresponding word equivalents in cells H2:H11.

Tens Lookup Table

Enter the tens numbers 0-90 in cells J2:J11, and enter the corresponding words in cells K2:K11.

Scale Lookup Table

Enter the scale numbers in cells M2:M6, and enter their word equivalents in cells N2:N6.

These lookup tables form the backbone of our numbers to words formula, allowing us to translate each part of a number into its English word equivalent.

Step 4: Build the Numbers to Words Formula

Now that we have our lookup tables set up, we can construct the full numbers to words formula in Excel. Here is the complete formula to enter in cell C2 and drag down:

=IF(A2=0,"zero",IF(A2<0,"negative "&TRIM(IF(ABS(A2)>=1000000000000,TEXT(ROUNDDOWN(ABS(A2),12)/1000000000000,"#,##")&" trillion ",IF(ROUNDUP(ABS(A2),12)>=1000000000000," "&INDEX(ScaleWords,MATCH(1000000000000,Scale,1),2)&" ",""))&IF(ABS(A2)>=1000000000,TEXT(ROUNDDOWN(ABS(A2),9)/1000000000,"#,##")&" billion ",IF(ROUNDUP(ABS(A2),9)>=1000000000," "&INDEX(ScaleWords,MATCH(1000000000,Scale,1),2)&" ",""))&IF(ABS(A2)>=1000000,TEXT(ROUNDDOWN(ABS(A2),6)/1000000,"#,##")&" million ",IF(ROUNDUP(ABS(A2),6)>=1000000," "&INDEX(ScaleWords,MATCH(1000000,Scale,1),2)&" ",""))&IF(ABS(A2)>=1000,TEXT(ROUNDDOWN(ABS(A2),3)/1000,"#,##")&" thousand ",IF(ROUNDUP(ABS(A2),3)>=1000," "&INDEX(ScaleWords,MATCH(1000,Scale,1),2)&" ",""))&IF(NUMBERVALUE(RIGHT(TEXT(INT(ABS(A2)),REPT("0",9)),3))>0,INDEX(OnesWords,MATCH(LEFT(RIGHT(TEXT(INT(ABS(A2)),REPT("0",9)),3),1)+1,Ones,0),2)&" hundred ","")&IF(ROUNDDOWN(MOD(ABS(A2),100),1)>19,INDEX(TensWords,MATCH(ROUNDDOWN(MOD(ABS(A2),100),10)+1,Tens,0),2)&" ","")&IF(MOD(ROUNDDOWN(MOD(ABS(A2),100),1),10)>0,INDEX(OnesWords,MATCH(MOD(ROUNDDOWN(MOD(ABS(A2),100),1),10)+1,Ones,0),2),"")&IF(MOD(A2,1)>0.00001," and ","")&IF(MOD(A2,1)>0.00001,TEXT(ROUND(MOD(A2,1)*100,),"##")&"/100",""))),A2)

Note: Replace “OnesWords”, “TensWords”, and “ScaleWords” with the actual cell ranges for your lookup tables.

This formula may look intimidating at first glance, but it’s essentially a series of nested IF statements that methodically break down the number, translate the parts, and stitch the words back together. The formula also handles special cases like negative numbers, decimals, and zero.

Step 5: Apply the Formula to Your Numbers

Drag the formula down column C to apply it to the rest of your number values in column A. The numbers should now display in their English word equivalents in column C!

Congratulations, you’ve now set up a robust numbers to words conversion in your Excel spreadsheet.

Potential Limitations and Workarounds

While this numbers to words formula is quite comprehensive, there are a few potential limitations to be aware of:

• Handling Very Large Numbers: The formula as written only goes up to trillions. If you need to handle larger numbers like quadrillions or quintillions, you’ll need to extend the scale lookup table and modify the formula accordingly.
• Customizing Negative Number Output: The current formula handles negative numbers by prepending the word “negative” to the output. If you need a different phrasing, simply tweak that part of the formula to your liking.
• Formatting Decimal Places: Decimals are handled by rounding to the nearest cent and displaying the cents as a fraction (e.g. 73/100). If you need a different decimal format or precision, adjust the rounding and display logic in the formula.
• Dealing with Non-Numeric Data: If your source data contains any non-numeric characters, symbols, or blanks, the formula may return errors or unexpected results. Ensure your number column contains only valid numbers to avoid issues.

By understanding these limitations, you can adapt the numbers to words formula to handle edge cases and fit your specific needs in Excel.

Final Thoughts

Converting numbers to words in Excel is a valuable skill for creating professional-looking invoices, checks, and financial documents. By harnessing Excel’s rich library of functions and setting up custom lookup tables, you can construct a powerful formula to translate numbers into English words automatically.

The step-by-step process outlined in this guide provides a solid starting point you can use and extend to fit your unique requirements. With a little practice and experimentation, you’ll be able to effortlessly set up numbers to words conversion in all your Excel workbooks, saving time and improving your documents’ clarity and impact.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.