Display text before or after a number in a cell, using a number format
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.
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.
The example may be easier to understand if you copy it to a blank worksheet.
Selecting an example from Help
|
|
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)