Excel Custom Number Format with Text: How It Works

How-to basics · Updated November 19, 2026

Yes. In Excel, a custom number format can display text before or after a number without changing the value stored in the cell: for example, the format 0 "items" displays the number 12 as “12 items,” while the cell still contains the numeric value 12. To add a format, select the cells, press Ctrl+1 on Windows, choose Number > Custom, and enter the format in the Type box.

What a custom number format does

A number format controls how Excel displays a cell’s value. It does not normally change the underlying value, turn a number into text, or alter the value used in calculations. This makes custom formats useful when a worksheet needs clearer labels but should remain suitable for sorting, arithmetic, charts, and formulas.

For example, a cell containing 12 can display as 12 items, and a cell containing 0.75 can display as 75.0% complete. In both cases, Excel retains the original numeric value. A formula that adds the first cell to another number still treats it as 12, not as a string containing the word “items.”

The distinction matters when deciding between formatting and a formula. Use a custom format when you want a different appearance while keeping the cell numeric. Use a formula or enter text when the words must become part of the actual cell result—for example, when exporting a text label to another system.

How to add a custom format in Excel

In Excel for Microsoft 365 on Windows, apply a custom format with these steps:

  1. Select the cells you want to format.
  2. Press Ctrl+1 to open the Format Cells dialog.
  3. On the Number tab, choose Custom in the Category list.
  4. Enter the format in the Type box, or select a listed format and edit it.
  5. Choose OK and check how the selected values appear.

You can also open the Format Cells dialog from the small dialog box launcher in the Home tab’s Number group. On Excel for Mac, select the cells and press Command+1; the Format Cells dialog also provides a Custom category. Menu details can vary slightly between releases. Excel for the web can display many formats, but its options for creating or editing custom formats may differ from desktop Excel; use the desktop app when you need the full custom Type box.

To reuse a format, select a cell that already has it and use Format Painter, or copy the cell and use Paste Special to paste formats. Applying a format to a cell does not change the value already entered there. If a value looks unchanged, check whether the format was applied to the intended cells and whether the format is appropriate for the value’s type.

Number placeholders and literal text

Custom formats combine digit placeholders with literal characters. The most useful placeholders are:

  • 0 displays a digit when one exists and adds a zero when needed. The format 00000 displays 123 as 00123.
  • # displays a digit when one exists but does not add unnecessary zeros. The format ## displays 7 as 7, not 07.
  • ? reserves space for a digit that is not present, which can help align numbers or fractions.
  • @ stands for text in a text section of a format; it is not a general-purpose number placeholder.

To include ordinary words in a numeric format, place the literal text in double quotation marks. For instance, 0 "boxes" displays 4 as “4 boxes,” while "Approx. "0 displays it as “Approx. 4.” You can add a prefix to a zero-padded identifier with "ID-"00000; a numeric value of 123 then displays as ID-00123.

Some characters have special meaning in a format. Quotation marks tell Excel to treat enclosed characters as literal text. A backslash can escape a single character, as in 0\%, which displays a literal percent sign without scaling the number. By contrast, 0% treats the percent sign as a percentage instruction: a stored value of 0.25 displays as 25%. For readability, quote labels that contain punctuation or characters that might be interpreted as format codes.

Using the four format sections

A custom number format can contain as many as four sections separated by semicolons. In order, they apply to positive numbers, negative numbers, zero, and text. For example, 0;[Red](0);"—";"Note: "@ displays positive numbers as ordinary integers, negative numbers in red parentheses, zero as a dash, and text with “Note: ” in front.

Sections are optional, but their position matters. With one section, the format generally applies to all numbers. With two sections, the first applies to positive numbers and zero, and the second to negative numbers. With three sections, the sections handle positive numbers, negative numbers, and zero. A fourth section is for text. If you leave out the text section, existing text usually displays as entered rather than using a text-specific pattern.

In the fourth section, @ marks where the original text appears. For example, 0;0;0;"Code: "@ displays a text entry such as “AB7” as “Code: AB7.” The text is still text; the format only changes its display. A custom number format is therefore not a way to convert words into numbers or to add permanent characters to the stored contents.

Useful examples for labels, units, and percentages

Choose a format that describes the value without disguising what the value means. These examples assume ordinary numeric values:

  • 0 "visits" displays 24 as “24 visits.”
  • 0.00 "kg" displays 3.5 as “3.50 kg.” The stored value remains 3.5.
  • 0.0% "complete" displays 0.75 as “75.0% complete.” Excel scales the stored decimal for percentage display.
  • "Batch "000 displays 8 as “Batch 008.” This is a visual label, not text stored in the cell.
  • +#,##0;-#,##0;0 displays positive numbers with a leading plus sign, negative numbers with a minus sign, and zero as 0.

Be careful with identifiers such as postal codes, account codes, and product numbers. If the identifier must preserve leading zeros as part of its actual content, store it as text when entering or importing it. A numeric value formatted as 00000 may look like a five-character code, but its stored value is still a number; another program or an export may not preserve the display format.

Units also require judgment. A format that adds “kg” does not convert pounds to kilograms, and a format that adds “hours” does not change minutes into hours. The format supplies a visual suffix only. Make sure the underlying values are already expressed in the unit shown, or readers could mistake a label for a conversion.

Conditions, colors, and regional settings

Custom formats can use conditions and colors in brackets. For example, a format can show negative values in red or use different display patterns above and below a threshold. Conditions are useful for visual emphasis, but they do not validate data, change a value, or replace conditional formatting when the rule needs to be more elaborate.

Colors and conditions belong in format sections, so check the semicolon structure carefully when adding them. A mistyped bracket, misplaced semicolon, or unquoted literal can produce an error or a display different from the one intended. Keep a copy of the original format before editing a complicated pattern.

Decimal and thousands separators can depend on Excel’s regional settings. A format written with a comma and period may appear differently on a computer configured to use different separators. When a workbook will be shared across regions, test the displayed result on the intended systems, and avoid assuming that the visible punctuation is a literal character. Quote a separator when you specifically want that character displayed as text.

Common problems and how to fix them

  • The word appears, but calculations fail. Check whether the value was entered as text or generated by a formula that returns text. A number format cannot turn text that looks numeric into a true number.
  • The percentage is 100 times too large. The percent sign in a number format scales a decimal for display. If the stored value is 75, 0% displays 7500%; for 75%, the stored value should ordinarily be 0.75.
  • The prefix or suffix shows as a format code. Put literal words in quotation marks, and escape special characters where appropriate. Reopen Format Cells and inspect the exact Type entry.
  • Leading zeros disappear after export. A custom format affects Excel’s display, not the underlying numeric value. Exported data may contain 123 rather than the displayed 00123. Store the identifier as text if the zeros are essential data.
  • The format seems to do nothing. Confirm the target cells are selected, that the value is numeric rather than text, and that the format section matches the value. Also check whether another operation, such as pasting values or importing data, replaced the format.

If you need to inspect the stored value, click the cell and look at the formula bar. The formula bar generally shows the underlying entry rather than the extra label supplied by a number format. This is a quick way to distinguish a display-only suffix from text that is actually part of a cell’s contents.

Custom format or TEXT formula?

A custom format is the better choice when people need to see a label but formulas should continue to use the numeric value. A TEXT formula is useful when you deliberately need a text result to combine with other text. The trade-off is that a formula using TEXT returns text, which may not behave like the original number in later arithmetic.

Assuming A2 contains the number 2.5, enter this formula in B2: =TEXT(A2,"0.0")&" kg". It returns the text “2.5 kg.” The result is not a numeric value with a display format. If you need to add values, keep the numbers in their own cells and apply a custom format such as 0.0 "kg" to those cells instead.

For reports, a common practical approach is to preserve raw numeric data in one column and use custom formats for presentation. If a receiving system requires a specific text string, create a separate text output deliberately and verify its contents before exporting. This avoids sacrificing numeric sorting or calculations just to make a worksheet look descriptive.

Frequently Asked Questions

Can I add words after a number in Excel without changing the value?
Yes. Apply a custom format such as 0 "days". The cell displays the number followed by “days,” while the stored value remains numeric.

How do I add text before a number?
Put the literal text in quotation marks before the digit placeholder. For example, "Order "0000 displays 26 as “Order 0026.”

What does @ mean in a custom number format?
@ is the text placeholder in the fourth section of a format. It marks where existing text appears, so a section such as "Ref: "@ can display a prefix before a text entry.

Can a custom format turn a number into text?
No. It changes how a value is displayed, not its underlying type. Use a text entry or a formula such as TEXT when the result itself must be text.

Why does my custom format disappear when I share or export a file?
The format is workbook display information, and some export methods or destination programs do not carry it over. If a label or leading zero must be part of the exported data, create and check an appropriate text value rather than relying only on the cell’s appearance.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.