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.

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.

  1. Select the cells that you want to format.

  2. On the Home tab, in the Number group, click the arrow, and then click More.

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

  4. In the Category list, click Custom.

  5. 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 CONCATENATE or CONCAT, TEXT or TEXTJOIN functions, and the ampersand (&) operator.

Notes: 

  • In Excel 2016, Excel Mobile, and Excel Online, 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.

Example

Examples pf combining text and numbers

Note the use of the TEXT function in the second example in the above image. 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, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

CONCATENATE function

CONCAT function

TEXT function

TEXTJOIN function

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×