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.
| To display | Use this code | How it works |
|---|---|---|
| 12 as Product #12 | "Product # " 0 | 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 | h:mm AM/PM "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.
Notes:
-
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.
No comments:
Post a Comment