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 number format to add text without changing the sorting behavior of the number.

  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
    12 as Product #12 "Product # " 0
    12:00 as 12:00 AM EST h:mm AM/PM "EST"
    -12 as $-12.00 Shortage and 12 as $12.00 Surplus $0.00 "Surplus";$-0.00 "Shortage"

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

To do this task, use the CONCATENATION and TEXT functions and the ampersand (&) operator.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

    Note Do not select the row or column headers.

    selecting an example from helpSelecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
1
2
3
A B
Salesperson Sales
Buchanan 28
Dodsworth 40%
Formula Description (Result)
=A2&" sold "&B2&" units." Combines contents above into a phrase (Buchanan sold 28 units)
=A3&" sold "&TEXT(B3,"0%")&" of the total sales." Combines contents above into a phrase (Dodsworth sold 40% of the total sales)
=CONCATENATE(A2," sold ",B2," units.") Combines contents above into a phrase (Buchanan sold 28 units)

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 format the number. The formula uses the underlying value from the referenced cell (.4 in this example) - not the formatted value you see in the cell (40%). The TEXT function restores the number formatting.

Functions details