- Applies to: Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007.
Imagine you must make a grammatically correct sentence from numerous columns of data to finalise a widespread mailing. Alternatively, you’re in need of formatting numbers with text and in doing so, keeping any formulas which refer to those numbers fully intact. Luckily in Excel, there are multiple ways to integrate 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 arrange includes both numbers and text—such as Product #15, Product #100, Product #200—it may sort differently than from what you anticipate. You can format cells that have 15, 100, and 200 to make them display in the worksheet as Product #15, Product #100, and Product #200.
Apply a custom number format to show the number with text—without altering the sorting programming of the number. Effectively, you modify how the number displays whilst retaining the original value.
Follow these steps:
- Choose the cells that you want to format.
- On the Home tab, in the Number group, select the arrow.
- In the Category list, choose a category like Custom, and then select a built-in format that matches your target one.
- In the Type field, amend the number format codes to produce your ideal format. To present both text and numbers in a cell, input the text characters in double quotation marks (” “), or place the numbers before 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 included in the quotation marks (including a space) is presented before the number in the cell. In the code, “0” symbolises the number enclosed in the cell (such as 12).|
|12:00 as 12:00 AM EST||h:mm AM/PM “EST”||The current time is displayed using the date/time format h:mm AM/PM, and the text “EST” is shown after the time.|
|-12 as $-12.00 Shortage and 12 as $12.00 Surplus||$0.00 “Surplus”;$-0.00 “Shortage”||The value is presented using a currency format. Also, if the cell includes a positive value (or 0), “Surplus” is presented after the value. If the cell has a negative value, “Shortage” is shown as a replacement.|
Combine text and numbers from different cells into the same cell by using a formula
Once you do merge numbers and text in a cell, the numbers convert into text and stops operating as numeric values. In effect, you cannot run any mathematical operations on them.
- In Excel 2016, Excel Mobile, and Excel for the web, CONCATENATE has been updated with the CONCAT function. Despite the CONCATENATE function still being accessible for backward compatibility, your first reference point should always be using CONCAT. This is because CONCATENATE might be removed from future versions of Excel.
- TEXTJOIN merges the text from several ranges and/or strings, and contains a delimiter you define between each text value that will be combined. If the delimiter is an empty text string, this function will subsequently concatenate the ranges. TEXTJOIN is unavailable in Excel 2013 and previous versions.
Combine text and numbers examples
See different examples in the figure below.
Take a closer look at the use of the TEXT function in the second example in the figure. After you join a number to a string of text by using the concatenation operator, employ the TEXT function to manage the way the number is presented. The formula applies the underlying value from the referenced cell (.4 in this example) — not the formatted value you see in the cell (40%). You deploy the TEXT function to restore the number formatting.