Formatting the Numbers in a Text String in Excel Formula

Sometimes, you may need to join text and numbers in Excel, such as creating a report that shows a salesperson’s name and sales amount, or a summary that displays a date and a percentage. However, when you concatenate text and numbers, the number formatting may not be preserved. For example, if you join a date with a text string, you may get a large serial number instead of a readable date.

To solve this problem, you can use the TEXT function in Excel, which allows you to convert a number to text in a given number format. The TEXT function can also be used to change the way a number appears, such as adding currency symbols, percentage signs, decimals, fractions, scientific notation, etc.

In this article, we will explain the basic theory and the procedures of using the TEXT function in Excel. We will also provide a comprehensive explanation with a scenario and a real data example. Finally, we will discuss some other approaches that can achieve similar results.

The TEXT function in Excel has the following syntax:

=TEXT (value, format_text)

where:

• value is the number that you want to format as text. It can be a cell reference, a formula, or a constant.
• format_text is the number format that you want to apply to the value. It must be a text string enclosed in double quotes (“”). You can use any of the predefined number formats in Excel, or create your own custom number formats.

The TEXT function returns a text string that represents the value in the specified number format. Note that the output of the TEXT function is always text, not a number. This means that you cannot use the result for further calculations, unless you convert it back to a number.

Some examples of using the TEXT function are:

• =TEXT (1234.567, “$#,##0.00”) returns “$1,234.57”
• =TEXT (TODAY (), “MM/DD/YY”) returns “02/01/24”
• =TEXT (NOW (), “H:MM AM/PM”) returns “7:40 PM”
• =TEXT (0.285, “0.0%”) returns “28.5%”
• =TEXT (4.34 , “# ?/?”) returns “4 1/3”
• =TEXT (12200000, “0.00E+00”) returns “1.22E+07”
• =TEXT (1234567898, “[<=9999999]###-####; (###) ###-####”) returns “(123) 456-7898”
• =TEXT (1234, “0000000”) returns “0001234”
• =TEXT (123456, “##0° 00’ 00’’”) returns “123° 27’ 36’’”

Procedures

To use the TEXT function in Excel, you can follow these steps:

1. Select a cell where you want to enter the formula.
2. Type =TEXT ( and then select or type the value that you want to format as text.
3. Type a comma (,) and then type the format_text that you want to apply to the value. Make sure to enclose the format_text in double quotes (“”).
4. Type a closing parenthesis ()) and press Enter to complete the formula.
5. Adjust the cell width and alignment if needed.

Alternatively, you can use the Insert Function dialog box to enter the TEXT function. To do this, follow these steps:

1. Select a cell where you want to enter the formula.
2. Click the Insert Function button on the Formulas tab, or press Shift + F3 on your keyboard.
3. In the Insert Function dialog box, select Text from the Category list, and then select TEXT from the Function list. Click OK.
4. In the Function Arguments dialog box, enter or select the value and the format_text for the TEXT function. Click OK.
5. Adjust the cell width and alignment if needed.

Explanation

To demonstrate how the TEXT function works in Excel, let’s use a scenario and a real data example. Suppose we have a table that shows the sales data of four products in January 2024, as shown below:

We want to create a summary that shows the total sales, the average sales, and the date of the report, using the TEXT function to format the numbers and join them with text. The summary should look like this:

• The total sales in January 2024 were $13,500.00
• The average sales per product were $3,375.00
• The report was generated on 02/01/24

To create the summary, we can use the following formulas:

• For the total sales, we can use =SUM (D2:D5) to calculate the sum of the sales column, and then use the TEXT function to format the result as currency with two decimals: =”The total sales in January 2024 were “&TEXT (SUM (D2:D5), “$#,##0.00”)
• For the average sales, we can use =AVERAGE (D2:D5) to calculate the average of the sales column, and then use the TEXT function to format the result as currency with two decimals: =”The average sales per product were “&TEXT (AVERAGE (D2:D5), “$#,##0.00”)
• For the date of the report, we can use =TODAY () to get the current date, and then use the TEXT function to format the result as MM/DD/YY: =”The report was generated on “&TEXT (TODAY (), “MM/DD/YY”)

The final result is shown below: