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

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


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 Format menu, click Cells, and then click the Number tab.
  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"

Top of Page Top of Page


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.

ShowHow 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 Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

CONCATENATE

TEXT

Top of Page Top of Page

 
 
Applies to:
Excel 2003