Numbers and words in excel

Combine text and numbers

Let’s say you need to create a grammatically correct sentence from several columns of data to prepare a mass mailing. Or, maybe you need to format numbers with text without affecting formulas that use those numbers. In Excel, there are several ways to combine text and numbers.

Use a number format to display text before or after a number in a cell

If a column that you want to sort contains both numbers and text—such as Product #15, Product #100, Product #200—it may not sort as you expect. You can format cells that contain 15, 100, and 200 so that they appear in the worksheet as Product #15, Product #100, and Product #200.

Use a custom number format to display the number with text—without changing the sorting behavior of the number. In this way, you change how the number appears without changing the value.

Follow these steps:

Select the cells that you want to format.

On the Home tab, in the Number group, click the arrow .

In the Category list, click a category such as Custom, and then click a built-in format that resembles the one that you want.

In the Type field, edit the number format codes to create the format that you want.

To display both text and numbers in a cell, enclose the text characters in double quotation marks (» «), or precede the numbers with a backslash (\).

NOTE: Editing a built-in format does not remove the format.

12 as Product #12

The text enclosed in the quotation marks (including a space) is displayed before the number in the cell. In the code, «0» represents the number contained in the cell (such as 12).

12:00 as 12:00 AM EST

The current time is shown using the date/time format h:mm AM/PM, and the text «EST» is displayed after the time.

-12 as $-12.00 Shortage and 12 as $12.00 Surplus

$0.00 «Surplus»;$-0.00 «Shortage»

The value is shown using a currency format. In addition, if the cell contains a positive value (or 0), «Surplus» is displayed after the value. If the cell contains a negative value, «Shortage» is displayed instead.

Combine text and numbers from different cells into the same cell by using a formula

When you do combine numbers and text in a cell, the numbers become text and no longer function as numeric values. This means that you can no longer perform any math operations on them.

To combine numbers, use the CONCATENATE or CONCAT, TEXT or TEXTJOIN functions, and the ampersand (&) operator.

In Excel 2016, Excel Mobile, and Excel for the web, CONCATENATE has been replaced with the CONCAT function. Although the CONCATENATE function is still available for backward compatibility, you should consider using CONCAT, because CONCATENATE may not be available in future versions of Excel.

TEXTJOIN combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges. TEXTJOIN is not available in Excel 2013 and previous versions.

Examples

See various examples in the figure below.

Look closely at the use of the TEXT function in the second example in the figure. When you join a number to a string of text by using the concatenation operator, use the TEXT function to control the way the number is shown. The formula uses the underlying value from the referenced cell (.4 in this example) — not the formatted value you see in the cell (40%). You use the TEXT function to restore the number formatting.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Источник

Convert numbers into words

Excel doesn’t have a default function that displays numbers as English words in a worksheet, but you can add this capability by pasting the following SpellNumber function code into a VBA (Visual Basic for Applications) module. This function lets you convert dollar and cent amounts to words with a formula, so 22.50 would read as Twenty-Two Dollars and Fifty Cents. This can be very useful if you’re using Excel as a template to print checks.

If you want to convert numeric values to text format without displaying them as words, use the TEXT function instead.

Note: Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the VBA programming language, and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality, or construct procedures to meet your specific requirements.

Create the SpellNumber function to convert numbers to words

Use the keyboard shortcut, Alt + F11 to open the Visual Basic Editor (VBE).

Note: You can also access the Visual Basic Editor by showing the Developer tab in your ribbon.

Click the Insert tab, and click Module.

Copy the following lines of code.

Note: Known as a User Defined Function (UDF), this code automates the task of converting numbers to text throughout your worksheet.

Paste the lines of code into the Module1 (Code) box.

Press Alt + Q to return to Excel. The SpellNumber function is now ready to use.

Note: This function works only for the current workbook. To use this function in another workbook, you must repeat the steps to copy and paste the code in that workbook.

Use the SpellNumber function in individual cells

Type the formula =SpellNumber( A1) into the cell where you want to display a written number, where A1 is the cell containing the number you want to convert. You can also manually type the value like =SpellNumber(22.50).

Press Enter to confirm the formula.

Save your SpellNumber function workbook

Excel cannot save a workbook with macro functions in the standard macro-free workbook format (.xlsx). If you click File > Save. A VB project dialog box opens. Click No.

You can save your file as an Excel Macro-Enabled Workbook (.xlsm) to keep your file in its current format.

Click File > Save As.

Click the Save as type drop-down menu, and select Excel Macro-Enabled Workbook.

Источник

How to Convert Number to Words in Excel

In this tutorial, you will learn completely how to convert a number to words in excel.

When processing numbers in excel, especially if they are financial related numbers, we sometimes need the numbers’ words form. Unfortunately, there isn’t a built-in excel formula that can help us to convert our numbers to their word form directly. However, there are some alternative methods we can implement which will give us similar results.

Want to know what are those methods in excel to convert our numbers to words properly? Read all parts of this tutorial.

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more

Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!

Why We Need to Learn About Excel Convert Number to Words?

If the numbers you want to convert to words are small numbers, then you can use VLOOKUP to do it.

However, before you write the VLOOKUP, you need to create a table that consists of numbers and their word forms. The table will then become the reference where VLOOKUP will find the word form of your number.

Generally, the writing form of your VLOOKUP for this number conversion process will become like this.

We input our number as the lookup reference value and the column index of the words form in our reference table. We input FALSE as the VLOOKUP search mode because we want the exact word form for our number (if we input TRUE, then VLOOKUP may take the wrong word form for our number because of its approximate search mode).

To better understand the VLOOKUP method implementation, see the example below.

Let’s say we want to convert this number into words.

How to do the conversion? Using the VLOOKUP method, we need to create a number-words table first as the VLOOKUP reference table.

To make the display cleaner, it is better if we create the table on a separate sheet. For the example, we create a reference table like this for our VLOOKUP.

If you need to, you can create a much bigger reference table to be able to convert more numbers!

After you have the table ready, just write VLOOKUP in the cell where you want the words form of your number. Input the number you want to convert, the reference table, the words column index, and FALSE to the VLOOKUP.

Here is the VLOOKUP writing to convert the number in the example.

As you have already created the reference table, you can use it later when you have other numbers to convert!

Admittedly, this method can only convert a small selection of numbers. What if you have numbers with the value of millions or more? If that is the case, then you should use one of the two methods we will discuss next instead.

How to Convert Number to Words in Excel 2: Various Formulas Combination

Using multiple formulas in one writing, we can also convert our number to its word form in excel. The formula writing, however, is quite long so you might need to copy the formula from here instead of writing it yourself 🙂

Just so you know, we don’t write the formula we will show below ourselves. The credit all goes to an Excel Forum user with the Id HaroonSid who managed to get this gem out. We edit the formula a little bit so it will translate a number into pure words without currency terms.

Here is the formula that HaroonSid has formulated to solve the number to words conversion problem in excel. This assumes your number is in the B2 cell. Therefore, you need to change the B2 cell coordinate there if you put your number in another cell.

Here, what we can tell is the formula uses CHOOSE as its core to converts the number in B2 to words. Mostly, it convert the number writing format using the TEXT formula first before separating its parts using MID.

From the MID result, CHOOSE will determine the correct word form for the part of the number. It uses the logic with other formulas help like SUBSTITUTE and IF to convert each part of our number into words.

For the implementation example of the formula writing, take a look at the screenshot below.

As you can see, we manage to convert our number to words using the formula!

How to Convert Number to Words in Excel 3: Custom Formula (VBA)

If you don’t use the two methods we have already discussed, then there is one more method you can try. For this method, we use VBA code to create a custom formula that can convert our numbers into words easily.

To use this method, however, you need to enable Macro first in your excel workbook so we can use VBA. From there, you must copy the VBA code we have prepared below to your workbook before you can utilize the formula.

To make it easier for you, we divide the discussion for this method into three parts. Those parts are the macro enablement, the VBA code copy process, and the usage of the formula. We will discuss each part in a step-by-step style.

Enable Macro by Checking and Setting Macro Security Setting

To be able to use a custom formula from a VBA code in excel, you need to permit macro. Therefore, we need to ensure its correct permission setting first by doing the following steps.

Click File from the menu tab (In Mac, click Excel on the top left of your screen)

Click Options (In Mac, click Preferences)

Click Trust Center (In Mac, click Security. After that, please proceed to step 6)

Click Trust Center Settings.

Click Macro Settings

Choose Enable All Macros

Click OK and OK

Click File and then Save As

On the Save as Type:dropdown, choose Excel Macro-Enabled Workbook

Name your file in the File Name: text box

Click Save

Create the Custom Formula to Convert Number to Words in Excel Using VBA Code

After setting our macro permission, it is time to create the formula using VBA.

1. Press Alt and F11 (Option + F11 atau Option + Fn + F11 di Mac) at the same time on your keyboard
2. Right-click on “VBA Project…”

Highlight Insert with your pointer and click Module

Double Click on the Module that comes up. Paste this code on the right side of the VBA editor screen

Function NUMBERTOTEXT(ByVal n As Currency) As String В В

Const Thousand = 1000@ В В
Const Million = Thousand * Thousand В В
Const Billion = Thousand * Million В В
Const Trillion = Thousand * Billion В В

If (n = 0@) Then NUMBERTOTEXT = «zero»: Exit Function В В

Dim Buf As String: If (n 0@) Then n = Abs(Fix(n)) В В
Dim AtLeastOne As Integer: AtLeastOne = n >= 1 В В

If (n >= Trillion) Then В В В
Buf = Buf & EnglishDigitGroup(Int(n / Trillion)) & » trillion» В В В
n = n — Int(n / Trillion) * Trillion В В В
If (n >= 1@) Then Buf = Buf & » » В В
End If В В

If (n >= Billion) Then В В В
Buf = Buf & EnglishDigitGroup(Int(n / Billion)) & » billion» В В В
n = n — Int(n / Billion) * Billion В В В
If (n >= 1@) Then Buf = Buf & » » В В
End If В В

If (n >= Million) Then В В В
Buf = Buf & EnglishDigitGroup(n \ Million) & » million» В В В
n = n Mod Million В В В
If (n >= 1@) Then Buf = Buf & » » В В
End If В В

If (n >= Thousand) Then В В В
Buf = Buf & EnglishDigitGroup(n \ Thousand) & » thousand» В В В
n = n Mod Thousand В В В
If (n >= 1@) Then Buf = Buf & » » В В
End If В В

If (n >= 1@) Then В В В
Buf = Buf & EnglishDigitGroup(n) В В
End If В В

If (Frac = 0@) Then В В В
Buf = Buf В В
ElseIf (Int(Frac * 100@) = Frac * 100@) Then В В В
If AtLeastOne Then Buf = Buf & » » В В В
Buf = Buf & Format$(Frac * 100@, «00») & «/100» В В
Else В В В
If AtLeastOne Then Buf = Buf & » » В В В
Buf = Buf & Format$(Frac * 10000@, «0000») & «/10000» В В
End If В В

NUMBERTOTEXT = Buf
End Function

Private Function EnglishDigitGroup(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 В В В
EnglishDigitGroup = 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 В В В
EnglishDigitGroup = 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 В В

Click on the X mark in the red box on the top right to close your VBA editor. The formula is now ready to use!

Use the Formula

After inputting the VBA code to create the formula, it is time to use it!

Type an equal sign ( = ) in the cell where you want to put the words form of your number

Type NUMBERTOTEXT (can be with large and small letters) and an open bracket sign after =

Input the number you want to convert to words/cell coordinate where it is

Type a close bracket sign

Press Enter

Done!

Add-In Download for the Formula to Convert Number to Words in Excel

Got confused when you need to paste the VBA code for the formula to convert your number to words? Then, you can just download the excel add-in we have created from the code here!

After you download it, just activate the add-in in your excel workbook. For that, you need to display the Developer tab first in your excel if you haven’t.

To display the Developer tab, go to Files > Options > Customize Ribbon (In Mac, go to Excel > Preferences > Ribbon & Toolbar > Ribbon Tab). Then, on the right box, make sure the Developer check box is checked.

After the Developer tab is there in your excel workbook, follow these steps. Make sure you have downloaded the add-in we provide earlier and put it somewhere on your computer.

Go to the Developer tab

Click the Excel Add-Ins button

In the dialog box that shows up, browse to where the add-in file you download is to open it

Check the checkbox of the Numbertotext add-in and click OK

Done! Now, you can use the NUMBERTOTEXT formula to convert your number into words!

How to Convert Number to Words with Currency in Excel

As number conversion to words is usually done for financial numbers, you may need to add currency in the conversion result. For that, you can use CONCATENATE, CONCAT, or the ampersand symbol (&) to add the currency word.

For example, if you use the NUMBERTOTEXT custom formula to convert your number, then the writing will be like this. This assumes you want to add the “dollars” currency word behind the conversion result. We use the ampersand symbol to add the currency word for this.

Don’t forget to add a space in front of the “dollars” word.

Here is the implementation example of this method in excel.

Quite easy, isn’t it?

How to Capitalize Each First Letter in the Result from Converting a Number to Words in Excel: PROPER

If you need to capitalize the first letter of each word from the number conversion, then you need a specific formula. That formula is PROPER.

You can envelop the number conversion formula you use with PROPER to do the capitalization. Generally, here is the general writing form of that, assuming you use the NUMBERTOTEXT formula.

Simple, isn’t it? And here is the example of the PROPER usage for the conversion result.

How to Capitalize All Letters in the Result from Converting a Number to Words in Excel: UPPER

What about if we want to capitalize all the letters instead? For that, you should use UPPER!

The way to use UPPER here is the same as the way we use PROPER earlier. Here is the general writing form of the UPPER implementation to our NUMBERTOTEXT formula.

And here is the implementation example of that writing.

Exercise

After you have learned how to convert numbers to words in excel using various methods, let’s do an exercise! We make this exercise so you can understand more practically about this tutorial topic.

Download the exercise file from the following link and do the instructions. Download the answer if you have done the exercise and sure about the results!

Link to the exercise file:
Download here

Instruction:

Additional Note

• The NUMBERTOTEXT formula cannot be used to convert decimal digits you have in your number to words
• If you input the VBA code manually, then you won’t find the NUMBERTOTEXT formula if you create a new workbook. You must use the add-in for that or you must paste the VBA code to create the NUMBERTOTEXT formula again

Related tutorials you should learn too:

Источник