Combine text and numbers
Let's say you want to create a grammatically correct sentence from several columns of data for a mass mailing or format numbers with text without affecting formulas that use those numbers. There are several ways to combine text and numbers.
What do you want to do?
Display text before or after a number in a cell by using a number format
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 expected. You can use a custom number format to display the number with text without changing the sorting behavior of the number. Put another way, you can change the way the number is shown without changing the number. For example, 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.
-
Select the cells that you want to format.
-
On the Home tab, in the Number group, click the arrow, and then click More.
-
In the Category list, click a category, and then click a built-in format that resembles the one that you want.
-
In the Category list, click Custom.
-
In the Type box, 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 (\). 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 operate as numbers. That is, you can no longer perform any math operations on them.
To combine numbers, use the CONCATENATION and TEXT functions and the ampersand (&) operator.
Example
Note the use of the TEXT function in the formula. 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.
Functions details
Use the CONCATENATE function as follows: CONCATENATE(text1, text2,…). You can include any combination of up to 255 values (text, numeric, date/time), or cell references.
Use the TEXT function as follows: TEXT(value, format_text). The TEXT function requires two arguments: value and format_text.
value Can be a numeric value, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value.
format_text A numeric format as a text string enclosed in quotation marks, for example "m/d/yyyy" or "#,##0.00".
No comments:
Post a Comment