Formula Errors

How to Fix Excel TEXT and SEQUENCE #VALUE! Error

Huma Ashraf Ch Sep 28, 2026 868 views

Question details

The user needs to resolve a #VALUE! error returned when combining the TEXT and SEQUENCE functions with specific text formats in Excel.

How to Fix Excel TEXT and SEQUENCE #VALUE! Error

Product: Excel
Device & OS: not provided
Scenario: Generating a sequence of formatted text strings, such as UD001 to UD999, using the SEQUENCE function inside the TEXT function.
Observed behavior: The formula evaluates to a #VALUE! error because the custom format code contains unescaped letters with special formatting meanings, such as the letter 'D' for days.

Before you start

Review the exact formula you are using and identify if the format string contains any letters that Excel normally uses for dates or times, such as Y, M, D, H, or S.

Solution 1Recommended

Escape Special Characters Using a Backslash

Use a backslash to instruct Excel to treat a format code letter as literal text rather than a date or time placeholder.

Certain letters like 'D', 'M', or 'Y' are reserved in Excel custom number formats to represent days, months, and years. When you include these in the TEXT function without escaping them, Excel attempts to parse a date, causing a #VALUE! error if the input isn't a valid date format.

Select the error cell

Click on the cell displaying the #VALUE! error to make it active.

Edit the formula

Click into the formula bar at the top to edit the TEXT function's arguments.

Locate the format text

Find the format text argument containing the special letter, for example, "UD000".

Add a backslash

Insert a backslash (\) directly before the special letter to escape it. For example, change "UD000" to "U\D000".

Apply the update

Press Enter to apply the updated formula and successfully generate the sequence.

Escape Special Characters Using a Backslash

Formula Example: Using =TEXT(SEQUENCE(999,1,1,1),"U\D000") will correctly output U001, U002, and so on without throwing an error.

Generate Formatted Sequences Easily

Generate Formatted Sequences with WPS Spreadsheet

WPS Spreadsheet seamlessly supports advanced array functions like SEQUENCE and TEXT. You can easily generate custom numbering and formatted lists without worrying about compatibility issues.

1. Open your workbook: Launch WPS Spreadsheet and open the document where you need to generate your list.
2. Select the starting cell: Click on the cell where you want the sequence to begin.
3. Enter the escaped formula: Type your formula using backslashes to escape literal characters, such as =TEXT(SEQUENCE(100), "\D000").
4. Generate the sequence: Hit Enter, and WPS Spreadsheet will instantly spill the formatted sequence down the column.

Fully compatible with Microsoft Excel formulas and functionsSupports Dynamic Arrays including the SEQUENCE functionAdvanced custom cell formatting made simpleLightweight, fast, and completely free to use

microsoft office alternative - wps office

Frequently Asked Questions

What other letters require a backslash in the TEXT function?

In Excel and WPS Spreadsheet, letters used for date and time formatting, such as 'Y' (Year), 'M' (Month), 'D' (Day), 'H' (Hour), 'M' (Minute), and 'S' (Second), must be escaped with a backslash if you want to display them as literal text within a custom format code.

Can I use double quotes instead of a backslash to escape letters?

Yes, you can enclose literal text within the format string in additional double quotes, though this requires careful syntax inside the formula. For example, =TEXT(SEQUENCE(10),"""D""00") is an alternative to using the backslash method, but the backslash is generally easier to read.

Why does the SEQUENCE function show a #SPILL! error instead of #VALUE!?

A #SPILL! error means the SEQUENCE function calculated correctly, but there isn't enough empty space below the formula cell to display the generated array. Clear the cells blocking the output path to resolve this issue.

Excel & Data Problems