How to combine text with numbers and dates in Excel

The problem: formatting is lost when you join

Number formats live on the cell, not in the value. C2 may display as 8.3%, but it holds 0.0825. The & operator and CONCAT join the stored value, so the percent sign, the currency sign, the thousands separators and the date format all vanish.

Currency and thousands separators

TEXT takes a value and a format code, and returns the formatted result as text. In the code, # is an optional digit, 0 is a required digit, the comma adds thousands separators and the .00 forces two decimals. "$#,##0" rounds to whole dollars, which reads better in a sentence.

Percentages

The % in a format code multiplies by 100 and adds the sign. Use 0% for whole percents and 0.0% for one decimal. If you type the % sign yourself after an unformatted number, you get 0.0825%, which is wrong by a factor of 100.

Dates

A date is a number of days since 1900, so joining it to text without TEXT shows that number. Use date codes: d, dd, ddd (Mon) and dddd (Monday) for the day; m, mm, mmm (Oct) and mmmm (October) for the month; yy or yyyy for the year.

Format codes follow your Windows or Mac language settings. In a German or French version of Excel, the year code is not yyyy, so a TEXT formula written in US English can show the wrong result when a colleague abroad opens it.

A full sentence from several cells

Mix as many pieces as you need. Remember the spaces inside the quotes, or words will run together. This kind of formula is useful for dashboard headlines and email text that update with the data.

Keep it a number: a custom format with text

TEXT gives you text, which cannot be summed or charted. If you only want a label next to a number in the same cell, use a custom number format instead. Press Ctrl+1, choose Custom, and type #,##0 "units" or $#,##0" revenue". The cell displays 48,251 units but still holds the number.

Custom formats can also have sections for positive;negative;zero. $#,##0;($#,##0) shows negative values in brackets, as accountants prefer. TEXT accepts the same sections, so =TEXT(B4,"$#,##0;($#,##0)") puts a negative amount in brackets.