How to Fix Excel Custom Number Format Displaying Text Incorrectly
Question details
Users experience incorrect text display when adding text directly into an Excel custom number format without proper syntax.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Applying a custom number format to cells to append or prepend text literals alongside numeric values.
- Observed behavior
- Letters and text do not display correctly or cause formatting errors because they are interpreted as formatting codes instead of literal text strings.
Identify the specific cells where you want to apply the custom number format and decide on the exact text prefix or suffix you need to include.
Enclose Text Literals in Quotation Marks
The standard and most effective method to include plain text in a custom number format is wrapping the specific text string in double quotation marks.
Excel requires any literal text inside a custom number format to be enclosed in double quotes. Without them, Excel attempts to interpret the letters as formatting codes (such as 'm' for months, 'd' for days, or 'h' for hours), leading to unintended and incorrect data displays.
Highlight the cell or range of cells in your spreadsheet where you want to apply the custom text formatting.
Right-click the highlighted selection and choose "Format Cells" from the context menu, or simply press the Ctrl+1 shortcut on your keyboard.
In the Format Cells window, go to the "Number" tab and select "Custom" from the Category list on the left side.
In the "Type" field, enter your desired format by wrapping the text in double quotes alongside the number placeholders. For example, to display 'HUDA007' when typing '7', enter "HUDA"000. To display 'HUDA7', enter "HUDA"0.
Click the "OK" button to apply the new formatting rule to your selected cells.
Apply Custom Number Formats Easily with WPS Spreadsheet
WPS Spreadsheet provides a highly intuitive and fully compatible platform for managing complex data formats. You can effortlessly apply custom number formatting, insert text literals, and organize your data just as you would in Microsoft Excel without any steep learning curve.
- 1. Open your document: Launch WPS Spreadsheet and select the target cells containing the numbers you wish to format.
- 2. Access Format Cells: Right-click the selected cells and choose "Format Cells", or use the convenient Ctrl+1 keyboard shortcut.
- 3. Select Custom Category: Under the "Number" tab in the dialog box, click on the "Custom" category at the bottom of the list.
- 4. Input the quoted text format: Type your desired format rule into the input box, ensuring your literal text is wrapped in double quotes (e.g., "ID-"0000), and click "OK".
Frequently Asked Questions
Why does Excel turn my custom text into dates or times?
If you use specific letters like 'm', 'd', 'y', 'h', or 's' without enclosing them in double quotation marks, Excel misinterprets them as date or time formatting codes rather than plain text. Always wrap literal text in quotes to prevent this.
Can I add a text suffix instead of a prefix in my custom format?
Yes, simply place the quotation marks and your text after the number placeholder in the Type box. For example, entering 0.00 "kg" will format the number 5 to display as '5.00 kg'.
Is it possible to display different text for positive and negative numbers?
Absolutely. Custom number formats allow up to four sections separated by semicolons (Positive; Negative; Zero; Text). You can add specific quoted text to each section, such as "Gain" 0; "Loss" -0 to differentiate value types automatically.
