Catalog

How to Convert Numbers to Words In Excel

August 15, 2023 7.7K views

If you have a column of numeric values in your worksheet, and you need to convert them to their equivalent English words, you may be wondering how to do it. Excel doesn't have a formula that can do this directly, but there are a few workarounds that you can use.In this article, we will show you how to convert numbers to words in Excel.

Part 1: How to Convert Numbers to Words In Excel Using VBA

Convert Numbers to Words



Your Excel sheet is filled with numbers, and now envision these numbers magically transforming into words. That's the power of the SpellNumber macro in Excel's enchanting world. Let's take a simple peek at how to add this macro, setting the stage for the forthcoming enchantment.

Example: Transforming Numbers to Words

You have a column of numbers in column A, ranging from 1 to 10. You wish to convert these numbers into words, adding an elegant touch to your data. Let's take a look at how to add the SpellNumber macro to achieve this in preparation for the next step.

Step 1. Open Excel and navigate to the "Developer" tab. If you don't see this tab, fear not! It can be easily enabled in the Excel options.

Add Developer tab



Developer tab



Step 2. Click on "Visual Basic" to open the VBA editor.

VBA editor



Step 3. In the VBA editor, navigate to "Insert" and select "Module." This is where the magic will unfold.

Insert



Step 4. Copy and paste the SpellNumber macro code from our reference links below into the module.

Function SpellNumber(ByVal MyNumber)

'Update by Extendoffice

Dim xStr As String

Dim xFNum As Integer

Dim xStrPoint

Dim xStrNumber

Dim xPoint As String

Dim xNumber As String

Dim xP() As Variant

Dim xDP

Dim xCnt As Integer

Dim xResult, xT As String

Dim xLen As Integer

On Error Resume Next

xP = Array("", "Thousand ", "Million ", "Billion ", "Trillion ", " ", " ", " ", " ")

xNumber = Trim(str(MyNumber))

xDP = InStr(xNumber, ".")

xPoint = ""

xStrNumber = ""

If xDP > 0 Then

xPoint = " point "

xStr = Mid(xNumber, xDP + 1)

xStrPoint = Left(xStr, Len(xNumber) - xDP)

For xFNum = 1 To Len(xStrPoint)

xStr = Mid(xStrPoint, xFNum, 1)

xPoint = xPoint & GetDigits(xStr) & " "

Next xFNum

xNumber = Trim(Left(xNumber, xDP - 1))

End If

xCnt = 0

xResult = ""

xT = ""

xLen = 0

xLen = Int(Len(str(xNumber)) / 3)

If (Len(str(xNumber)) Mod 3) = 0 Then xLen = xLen - 1

Do While xNumber <> ""

If xLen = xCnt Then

xT = GetHundredsDigits(Right(xNumber, 3), False)

Else

If xCnt = 0 Then

xT = GetHundredsDigits(Right(xNumber, 3), True)

Else

xT = GetHundredsDigits(Right(xNumber, 3), False)

End If

End If

If xT <> "" Then

xResult = xT & xP(xCnt) & xResult

End If

If Len(xNumber) > 3 Then

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

Else

xNumber = ""

End If

xCnt = xCnt + 1

Loop

xResult = xResult & xPoint

SpellNumber = xResult

End Function

Function GetHundredsDigits(xHDgt, xB As Boolean)

Dim xRStr As String

Dim xStrNum As String

Dim xStr As String

Dim xI As Integer

Dim xBB As Boolean

xStrNum = xHDgt

xRStr = ""

On Error Resume Next

xBB = True

If Val(xStrNum) = 0 Then Exit Function

xStrNum = Right("000" & xStrNum, 3)

xStr = Mid(xStrNum, 1, 1)

If xStr <> "0" Then

xRStr = GetDigits(Mid(xStrNum, 1, 1)) & "Hundred "

Else

If xB Then

xRStr = "and "

xBB = False

Else

xRStr = " "

xBB = False

End If

End If

If Mid(xStrNum, 2, 2) <> "00" Then

xRStr = xRStr & GetTenDigits(Mid(xStrNum, 2, 2), xBB)

End If

GetHundredsDigits = xRStr

End Function

Function GetTenDigits(xTDgt, xB As Boolean)

Dim xStr As String

Dim xI As Integer

Dim xArr_1() As Variant

Dim xArr_2() As Variant

Dim xT As Boolean

xArr_1 = Array("Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen ")

xArr_2 = Array("", "", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety ")

xStr = ""

xT = True

On Error Resume Next

If Val(Left(xTDgt, 1)) = 1 Then

xI = Val(Right(xTDgt, 1))

If xB Then xStr = "and "

xStr = xStr & xArr_1(xI)

Else

xI = Val(Left(xTDgt, 1))

If Val(Left(xTDgt, 1)) > 1 Then

If xB Then xStr = "and "

xStr = xStr & xArr_2(Val(Left(xTDgt, 1)))

xT = False

End If

If xStr = "" Then

If xB Then

xStr = "and "

End If

End If

If Right(xTDgt, 1) <> "0" Then

xStr = xStr & GetDigits(Right(xTDgt, 1))

End If

End If

GetTenDigits = xStr

End Function

Function GetDigits(xDgt)

Dim xStr As String

Dim xArr_1() As Variant

xArr_1 = Array("Zero ", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ")

xStr = ""

On Error Resume Next

xStr = xArr_1(Val(xDgt))

GetDigits = xStr

End Function

Step 5. Close the VBA editor and return to your Excel worksheet.

Add VBA code



My Experience with Adding the SpellNumber Macro:

Integrating the SpellNumber macro in Excel was surprisingly easy. I followed steps to the Developer tab and the VBA editor, where I added the macro code. It intrigued me how numbers could turn into text. Still, I learned to be cautious; macros need accuracy to avoid disrupting the spreadsheet. Having a backup and understanding VBA's structure helped. This experience opens the door to more Excel possibilities, mixing logic with creativity.

Your Excel worksheets become a canvas for linguistic magic. In Part 2, we hand you the key to unlock the SpellNumber macro, a potent tool that elevates your numeric data into an eloquent symphony of words. Navigate through practical examples that illuminate the path to mastery, and watch as your data transforms before your very eyes, guided by the artistry of this remarkable macro.

Example: Transforming Numeric Data into Words

Step 1. Open your Excel worksheet with the numeric data you want to transform.

Step 2. Select a nearby column. Let's use column B for the transformed words.

Step 3. In the first cell of column B, enter =SpellNumber(A1).

SpellNumber Formula



Step 4. Press Enter, and watch the number in cell A1 elegantly transform into words in cell B1.

Step 5. To transform more numbers, drag the formula down, letting the SpellNumber macro work its charm.

drag the formula down



My Experience Using the SpellNumber Macro:

Trying out the SpellNumber macro brought a fresh perspective as numbers transformed into words, lending a novel touch to my data. The process was straightforward, revealing its potential to enhance communication. However, a word of caution is in order; precision matters. A minor misstep could lead to unexpected outcomes. Familiarity with the macro and careful input validation are essential for a seamless experience.

Part 2: How to Convert Numbers to Words In Excel without VBA

Navigating the intricate landscape of VBA might seem daunting, but fear not—for there exists a simpler route to achieve the enchanting feat of converting numbers into words. This alternative approach, utilizing a user-defined function, offers a streamlined method that retains the same alluring transformation. By delving into the following steps, we uncover this innovative technique, allowing you to seamlessly infuse your data with the lyrical charm of words while sidestepping the complexities of VBA.

Benefits of Using the SpellNumber Macro:

1. Ease of Use: The user-defined function offers simplicity. No VBA knowledge is required, making it accessible to a wider audience.

2. Universal Accessibility: Unlike macros that need to be integrated into each workbook, the function travels with the worksheet, ensuring seamless sharing.

3. Zero Macro Alerts: Bid farewell to pesky macro alerts when sharing your workbooks. The function operates in the background, elegantly transforming numbers to words.

Disadvantages of Using Macro to SpellNumber in Excel:

1. VBA Proficiency: Macros require VBA skills for customization, potentially limiting those unfamiliar with programming.

2. Workbook Specific: Macros must be copied or built into each workbook, adding complexity and maintenance efforts.

3. Macro Alerts: Sharing a workbook with macros may trigger alerts, potentially raising security concerns.

Now, let's take the scenic route and witness the artistry of this method(Use combinations of formula available in Excel) through a practical example.

How to Convert Numbers to Words In Excel without VBA

Example: Transforming Numbers into Words Using a Formula

Step 1. Open your Excel worksheet with the numeric data you wish to transform.

example data



Step 2. Select a column adjacent to the numbers. Let's use column B for the transformed words.

Step 3. In the first cell of column B, enter the formula

=IF(OR(LEN(FLOOR(A1,1))=13,FLOOR(A1,1)<=0),"out of="" range="">1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),3,1)+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(A1>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),9,1)+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(A1,1)>1," "," "))&IF(ISERROR(FIND(".",A1,1)),""," point "&PROPER(IF(LEN(LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!A1,".",REPT(" ",255)),255,200)),2))=1,CHOOSE(1*LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!A1,".",REPT(" ",255)),255,200)),2),"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&" ","")&CONCATENATE(CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"One","One One","One Two","One Three","One Four","One Five","One Six","One Seven","One Eight","One Nine")&" ","Two","Three","Four","Five","Six","Seven","Eight","Nine"),IF(VALUE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,""," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine")&" ",IF(LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!A1,".",REPT(" ",255)),255,200)),2)="01","one ",IF(LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!A1,".",REPT(" ",255)),255,200)),1)="0",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&" ","")))))))

Step 4. Press Enter, and observe as the number in cell A2 gracefully evolves into words in cell B2.

Numbers into Words Using a Formula



Step 5. To transform additional numbers, drag the formula down, unveiling the magical metamorphosis.

Drag the formula down



My Experience Using the User-Defined Function:

Exploring user-defined functions, I appreciated the simplicity they brought—no intricate VBA coding or complex setups. Just a straightforward formula to transform numbers into words, adding a unique touch to Excel. Yet, a heads-up: understanding the function well is key. Test it across scenarios to ensure accuracy. This practical insight underlines the function's value while highlighting the importance of careful usage for the best results.

Part 3: Why Use WPS Office?

WPS Office logo



It's important to note that the methods we've explored thus far are applicable not only to Microsoft Excel but also to WPS Office Spreadsheet. However, we invite you to explore the distinct advantages that WPS Office brings to the table. Here's why we recommend you consider WPS Office for your transformative Excel undertakings:

1. Price: WPS Office offers an affordable alternative without compromising on features, making it a cost-effective choice for both personal and professional use.

2. Compatibility: Seamlessly exchange documents between WPS Office and Microsoft Office, ensuring smooth collaboration and document sharing.

3. Features: WPS Office boasts a comprehensive suite of features, rivaling industry standards and enabling you to accomplish diverse tasks with ease.

4. User-Friendly Interface: Navigating WPS Office is a breeze, even for newcomers. The intuitive interface ensures a smooth learning curve and efficient workflow.

5. Unveil Hidden Gems: Delve into the WPS Blog to unearth hidden gems that will amplify your skills. From advanced techniques to innovative solutions, each tutorial is a step towards mastery.

Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
avator
Algirdas Jasaitis
logo

How to Convert Numbers to Words in WPS Office

There are two ways to convert numbers to words in WPS Office:

Use the TEXT function. 

The TEXT function in WPS Office can be used to convert numbers to text in a variety of formats, including spelled-out numbers, currency, and dates. The syntax for the TEXT function is:

Text Formula



=TEXT(number, format)

  • number: The number that you want to convert to text.

  • format: The format that you want to use for the text. For example, you could use "0" to spell out the number, "$" to format the number as currency, or "mm/dd/yyyy" to format the number as a date.

For example, the following formula would convert the number 100 to the spelled-out text "One hundred":

=TEXT(100, "0")

text result



Change the cell format to text.

Another way to convert numbers to words in WPS Office is to change the cell format to text. To do this, follow these steps:

Step 1. Select the cells that contain the numbers that you want to convert to text.

Step 2. Click on the Home tab.

Home tab



Step 3. In the Number group, click on the Format button.

Step 4. In the Format Cells dialog box, select the Text option from the Category list.

the Format Cells dialog box



Step 5. Click on the OK button.

the Format Cells result



The numbers in the selected cells will be converted to text.

Which method you use to convert numbers to words in WPS Office depends on your specific needs. If you need to convert numbers to text in a variety of formats, the TEXT function is the best option. However, if you simply need to convert numbers to text in the default format, changing the cell format to text is a simpler option.

FAQs About How to Convert Number to Words in Excel

Can I Turn Words to Numbers Reversely?

Absolutely! Reversing the conversion, from words to numbers, is indeed possible in WPS Office by using VBA code. While the focus of this discussion has been on transforming numbers to words, it's important to note that WPS Office provides versatile tools to handle both directions of conversion.

Can I Turn Numbers to Words with One Click?

Absolutely, turning numbers to words with just one click is a breeze with the remarkable capabilities of WPS Office(using VBA or combinations of formulas available). This streamlined process simplifies your tasks, saving you valuable time and effort.

What Is VBA and How to Quickly Learn It?

VBA is a programming language by Microsoft used to automate tasks and create custom solutions in Office applications like Excel, Word, and PowerPoint.

Quickly Learning VBA:

  • Basics: Understand programming concepts like variables and loops.

  • VBA Editor: Open it in Office apps to write code.

  • Examples: Study simple code examples.

  • Record Macros: Learn by recording actions.

  • Online Resources: Use tutorials and courses.

  • Practice: Regularly code small projects.

  • Documentation: Explore Microsoft's VBA documentation.

  • Communities: Engage in online forums.

  • Build: Create your macros for real tasks.

  • Debugging: Learn to fix code errors.

  • Curiosity: Keep exploring and experimenting.

Mastering VBA enhances productivity and offers tailored solutions.

Summary

In this guide, we explored different ways to convert numbers to words in Excel, focusing on both VBA macros and user-defined functions. We provided clear step-by-step instructions and examples for each method. Emphasizing the benefits of WPS Office, we highlighted its compatibility with Excel, user-friendly features, and affordability. Whether you choose VBA or user-defined functions, this guide equips you to seamlessly achieve number-to-word conversion while recognizing the advantages of using WPS Office for enhanced office tasks.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.