Number Format in Concatenate Excel: Keep Dates & Decimals

Formulas & functions · Updated November 20, 2026

Excel concatenation turns numbers into text, but it does not carry over the number format shown in the source cell. To keep a date, currency amount, percentage, or decimal readable, wrap the value in TEXT(value, format_text) before joining it to other text. With an order number in A2 and a date in B2, for example, use ="Order " & A2 & " ships " & TEXT(B2,"mmm d, yyyy"); if A2 is 1047 and B2 contains April 7, 2026, the result is Order 1047 ships Apr 7, 2026.

Why concatenation changes number formats

A cell can store a number while displaying it in a friendlier way. For example, a cell may contain the date serial value for a day in April but display it as Apr 7, 2026 because its cell format is set to a date style. When you join that value to text, Excel uses the underlying value rather than the cell’s display format. The result can therefore look like a large serial number instead of a date.

The same distinction applies to currency symbols, decimal places, percentages, and leading zeros. A cell containing 0.376 might display as 37.6% when formatted as a percentage. Concatenation sees the numeric value, not the percent sign or the chosen number of decimal places. Formatting the source cell differently does not change the value stored in it, and it usually will not change the text produced by a concatenation formula.

This is not a bug in the ampersand operator or in Excel’s CONCAT functions. Concatenation builds text from values; cell formatting controls how values appear in cells. Use TEXT when you want to specify how a numeric value should look inside the new text.

How TEXT works with CONCAT, CONCATENATE, and &

TEXT takes two arguments: the value to convert and a format code in quotation marks. It returns text, so you can place it inside a larger expression. In Microsoft 365 for Windows, the ampersand (&) is a simple way to join pieces of text, while CONCAT joins multiple items and CONCATENATE is the older function name retained for compatibility. The number-format solution is the same with each: format the value with TEXT first.

With a price in A2, where A2 contains 1234.5, this formula uses the ampersand and returns Total: $1,234.50: ="Total: " & TEXT(A2,"$#,##0.00"). The dollar sign is part of the format code, the comma requests a thousands separator, and the two zeros after the decimal point require two displayed decimal places.

With that same value in A2, this version uses CONCAT and returns the same text: =CONCAT("Total: ",TEXT(A2,"$#,##0.00")). The CONCAT function is available in Microsoft 365. If a workbook needs to work in older Excel versions, the ampersand operator or CONCATENATE may be a more compatible choice. In every case, TEXT controls the appearance of the number.

Useful number format codes

The second argument of TEXT uses Excel’s custom number-format code conventions. Choose a code based on what the result needs to communicate, not just what looks decorative. A few common patterns cover many everyday cases:

  • 0.00 displays two decimal places, including trailing zeros. A value of 4.5 appears as 4.50.
  • #,##0.00 adds thousands separators and displays two decimal places. A value of 1234.5 appears as 1,234.50.
  • 0% displays a proportion as a percentage with no decimal places. A value of 0.376 appears as 38% after rounding.
  • 0.0% displays one decimal place in the percentage. A value of 0.376 appears as 37.6%.
  • mmm d, yyyy displays a date such as Apr 7, 2026. mmmm d, yyyy uses the full month name.
  • 00000 displays a number with at least five digits, adding leading zeros when needed. A value of 73 appears as 00073.

In these codes, 0 forces a digit to appear, while # displays a digit only when needed. The comma can mark thousands grouping, and the period marks the decimal position in the format pattern. The visible punctuation can depend on Excel’s regional settings, so the displayed separators may differ by locale even when the formula uses the same pattern.

Examples for dates, money, percentages, and IDs

For a date in B2, the format code determines whether the output is brief or descriptive. If B2 contains April 7, 2026, the following formula returns Date: Apr 7, 2026: ="Date: " & TEXT(B2,"mmm d, yyyy"). Change the code to dddd, mmmm d for a result such as Tuesday, April 7. The underlying date remains unchanged; only the text generated by this formula takes on the chosen appearance.

For a percentage in C2, where C2 contains 0.376, this formula returns Completion: 37.6%: ="Completion: " & TEXT(C2,"0.0%"). Excel stores a percentage as a proportion, so 0.376 means 37.6%. If C2 already contains 37.6 rather than 0.376, applying a percent format would display 3760%; confirm how the source value is stored before choosing the code.

For a product code in D2, where D2 contains the number 73, this formula returns Code 00073: ="Code " & TEXT(D2,"00000"). This is useful when the zeros are meaningful for presentation, such as when a report requires a fixed-width identifier. If the code is an identifier rather than a quantity, it may be better to store it as text from the start, especially if it can contain letters or exceed Excel’s reliable numeric precision.

For a measurement in E2, where E2 contains 12.345, this formula returns Length: 12.35 cm: ="Length: " & TEXT(E2,"0.00") & " cm". TEXT rounds the displayed result to two decimal places; it does not change the original value in E2. That matters if the number is also used in later calculations.

Choosing a format that communicates clearly

The right format depends on the audience and purpose of the combined text. A customer-facing sentence may need a currency symbol and two decimal places, while an internal summary might use a whole-dollar amount. Dates can be ambiguous across regions: 04/07/2026 can mean April 7 or July 4 depending on the convention. A month-name format such as mmm d, yyyy is often clearer when the text will be read internationally.

Consider these factors before writing the formula:

  • Precision: Decide how many decimal places are informative. TEXT rounds the displayed text, so avoid hiding precision that readers need.
  • Units: Add a clear unit such as %, kg, or cm when the number alone could be misunderstood.
  • Locale: Check how date names, decimal marks, grouping separators, and currency symbols will appear for the workbook’s users.
  • Identifiers: Preserve leading zeros and avoid treating codes as quantities if arithmetic is not relevant.
  • Consistency: Apply the same pattern to every value in a report so similar amounts or dates do not appear in mixed styles.

Common problems and how to fix them

The result shows a number such as 46119 instead of a date. The formula is likely joining the date’s stored serial value. Wrap the date reference in TEXT and choose an explicit date code. Also check that the referenced cell actually contains a valid Excel date rather than text that merely looks like one.

The result has too many or too few decimal places. Adjust the zeros and number signs in the format code. For fixed two-place output, use 0.00; for up to two places without unnecessary trailing zeros, 0.## can be appropriate. Remember that the displayed text may be rounded even though the source value retains its full precision.

A percentage is 100 times too large. A value of 37.6 is not the same stored value as 37.6%. For the standard percentage format, Excel expects a proportion such as 0.376. Check the source data and decide whether it is stored as a proportion or as a number already multiplied by 100 before applying a percent code.

A currency symbol or separator looks different on another computer. Excel’s language and regional settings can affect how format codes are interpreted or displayed. If the workbook will be shared across regions, test the output in the intended environment and prefer unambiguous date text when possible. TEXT returns text, so the resulting string will not behave like a numeric currency cell in later calculations.

Changing the source cell’s format did not fix the formula. That is expected. The concatenation formula does not inherit the source cell’s number format. Put the desired format code inside TEXT in the formula itself.

Concatenated text is not a substitute for numeric data

Once a value has been combined with words, a symbol, or other text, the result is generally text rather than a number that Excel can directly sum or use in numeric comparisons. For example, a result such as Total: $1,234.50 is intended for display. Keep the original amount in its own numeric cell for calculations, sorting, charting, and analysis.

A useful workbook design is to separate calculation from presentation: retain dates and amounts in their original columns, calculate with those values, and create a separate label or report sentence with TEXT only where a readable text output is needed. This avoids having to extract numbers back out of sentences later. It also reduces the risk that a rounded display value is mistaken for the exact value used in a calculation.

Frequently Asked Questions

How do I concatenate a date in Excel without getting a number?
Wrap the date cell in TEXT and provide a date format, such as TEXT(B2,"mmm d, yyyy"). Then join that result to the rest of the sentence with & or CONCAT.

Does CONCAT preserve the cell’s formatting?
No. CONCAT, CONCATENATE, and the ampersand operator combine values as text; they do not copy the source cell’s display format. Use TEXT to specify the format in the formula.

How can I keep zeros at the start of a number?
For a numeric value in A2 that should display with five digits, use TEXT(A2,"00000"). If the value is an identifier that should never be used for arithmetic, storing it as text may be more suitable.

Can I concatenate a number with a currency format?
Yes. Apply a currency-style format code inside TEXT, such as TEXT(A2,"$#,##0.00"), then join the result to a label. The resulting combined string is text, not a numeric amount.

Can I change the number format after concatenating?
Not as a cell number format, because the combined result is text. Change the TEXT format code in the formula instead. You can format the whole output cell’s font, fill, or alignment, but that will not reformat the number embedded in the text.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.