If a column 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.
- Select the cells you want to format.
- On the Format menu, click Cells, and then click the Number tab.
- In the Category list, click a category, and then click a built-in format that resembles the one you want.
- In the Category list, click Custom.
- In the Type box, edit the number format codes to create the format 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, using a formula
Use the CONCATENATION, TEXT functions and the ampersand (&) operator to do this task.
Worksheet example
The example may be easier to understand if you copy it to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
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)