When you attempt to combine text and numbers in a single Microsoft Excel cell using an ampersand (like ="Total Profit: " & A1), Excel will aggressively strip away all of your beautiful formatting. If cell A1 contained a perfectly formatted currency (like “$1,250.00”), the resulting combined string will output as “Total Profit: 1250”, completely destroying your dollar signs and commas. To force Excel to respect your formatting during combinations, you must use the TEXT() function.
How the TEXT Function Works
The TEXT() function is a specialized tool that mathematically converts a raw number or date into a hard-coded text string, while simultaneously applying a very specific visual format to it. Once the number is converted to text via this function, Excel cannot strip the formatting away.
Formatting Currency
Assume cell A1 contains the raw number 1250, and you want to combine it with a sentence.
- Click on an empty cell (like B1).
- Type the following formula:
="Total Profit: " & TEXT(A1, "$#,##0.00")
- Press Enter.
The cell will perfectly output: Total Profit: $1,250.00.
The second argument (the string of symbols inside the quotation marks) is the formatting code. The $ forces the dollar sign, the #,##0 forces comma separation for thousands, and the .00 forces exactly two decimal places.
Formatting Dates
This function is even more critical when working with dates. If cell A1 contains the date 12/31/2024, and you attempt to combine it with text (="The deadline is " & A1), Excel will bizarrely output “The deadline is 45657” because it converts the date into its raw underlying serial number.
To fix this, you use the TEXT() function to force a date format.
="The deadline is " & TEXT(A1, "mmmm d, yyyy")
This will perfectly output: The deadline is December 31, 2024.
You can adjust the formatting code to achieve different results:
"mm/dd/yy"outputs 12/31/24"ddd"outputs only the day of the week (e.g., Tue)"mmmm"outputs only the full month (e.g., December)
By mastering the TEXT() function, you can build dynamic, perfectly formatted sentences that instantly update when the underlying data changes.