How to Use the TEXT Function to Combine and Format Text in Excel

The TEXT function in Excel is a useful tool for combining and formatting text values. It allows you to convert a numeric value into a text value with a specified format. You can also use the TEXT function to join text values from different cells or formulas with a delimiter of your choice.

The syntax of the TEXT function is:

=TEXT(value, format_text)

where:

• value is the numeric value that you want to convert to text.
• format_text is the text string that defines the formatting of the output. You can use any of the standard number formats, such as “0.00”, “#,##0”, “mm/dd/yyyy”, etc. You can also use custom formats, such as “[$-409]d-mmm-yyyy;@”, which displays the date in the format “14-Jan-2024”.

The TEXT function returns a text value, not a number. This means that you cannot use the result for further calculations. However, you can use the VALUE function to convert the text back to a number if needed.

Procedures

To use the TEXT function to combine and format text in Excel, follow these steps:

1. Enter the numeric value that you want to convert to text in a cell, or refer to a cell that contains the value.
2. Enter the format_text argument in another cell, or type it directly in the formula. Make sure to enclose it in double quotes.
3. Type the TEXT function in the cell where you want the output, and use the cell references for the value and format_text arguments. Alternatively, you can use the Insert Function dialog box to enter the arguments.
4. Press Enter to complete the formula and see the result.

Explanation

To illustrate how the TEXT function works, let’s look at an example. Suppose you have a table of sales data for different products and regions, as shown below:

You want to create a summary report that shows the sales amount for each product and region in a text format, with a comma as the thousands separator and two decimal places. You also want to add a prefix of “$” and a suffix of ” USD” to the sales amount. You can use the TEXT function to achieve this.

In cell E2, enter the following formula:

=TEXT(B2,"$#,##0.00 USD")&" sales for "&A2&" in "&C2

This formula uses the TEXT function to convert the sales amount in B2 to a text value with the format “$#,##0.00 USD”. Then, it uses the ampersand (&) operator to join this text value with the product name in A2 and the region name in C2, with some additional text in between. The result is:

$12,345.67 USD sales for A in North

You can copy this formula down to the other cells in column E to get the summary report for each row. The final output is: