Function Problems

How to Fix TEXTSPLIT #SPILL! Error in an Excel Table

Emma Brown Oct 7, 2026 871 views

Question details

The user encounters a #SPILL! error when attempting to use the TEXTSPLIT function inside an Excel table.

How to Fix TEXTSPLIT #SPILL! Error in an Excel Table

Product: Spreadsheet
Device & OS: not provided
Scenario: Splitting text into multiple columns using the TEXTSPLIT formula inside a formatted table structure.
Observed behavior: The TEXTSPLIT function fails and produces a #SPILL! error because dynamic array formulas cannot spill into neighboring cells within a calculated column of a table.

Before you start

Verify if your data strictly needs to remain formatted as an official 'Table' object, as dynamic array formulas like TEXTSPLIT work seamlessly in standard data ranges without causing spill errors.

Solution 1Recommended

Convert the Table to a Normal Range

This is the most straightforward method. By converting the table back into a standard range, dynamic arrays can spill freely into adjacent cells.

Excel's structured tables are designed with calculated columns that expect a single result per row. Because TEXTSPLIT outputs multiple values across columns, it violates this structure. Converting to a normal range removes this limitation while keeping your data visually intact.

Select a cell in the table

Click on any cell inside the Excel table that is currently restricting your formula.

Convert to Range

Navigate to the 'Table Design' tab on the ribbon and click on 'Convert to Range' in the Tools group.

Confirm the conversion

Click 'Yes' on the prompt. Your TEXTSPLIT formula will now instantly execute and spill the split text across the adjacent cells.

Formatting Retained: Converting to a range removes the structured table functionality but retains all the cell colors, borders, and formatting you previously applied.

Solution 2

Place the TEXTSPLIT Formula Outside the Table

Use this solution if you must keep the original data inside an official Excel Table structure.

Solution 3

Use Alternative Single-Cell Extraction Formulas

Ideal if you absolutely must have the split data inside the same structured table.

Efficient Data Management

Split Text Seamlessly with WPS Spreadsheet Text to Columns

Instead of wrestling with complex TEXTSPLIT formulas and troubleshooting #SPILL! errors, you can easily divide text into separate cells using the built-in Text to Columns feature in WPS Office.

1. Select Your Data: Highlight the column or specific cells containing the text you want to split.
2. Open Text to Columns: Navigate to the 'Data' tab on the top ribbon and click on 'Text to Columns'.
3. Choose Delimited Option: Select 'Delimited' in the wizard and click Next, then choose the character separating your text (like Space, Comma, or Semicolon).
4. Finish Splitting: Select a destination cell if needed, and click 'Finish'. Your text is instantly split across columns without any formula errors.

Avoids dynamic array #SPILL! errors entirelyHighly compatible with all Microsoft Excel file formats (.xlsx)Intuitive wizard interface makes splitting text by any delimiter fast and easyFree and lightweight alternative to heavy spreadsheet applications

microsoft office alternative - wps office

Frequently Asked Questions

Why do other dynamic array functions also return #SPILL! in tables?

Excel tables are structurally designed to use calculated columns, meaning one formula calculates exactly one result per row. Dynamic array functions like FILTER, SORT, UNIQUE, and TEXTSPLIT output multiple cells, which violates this rule, resulting in a #SPILL! error.

Can I force an Excel table to accept spilled arrays?

No. It is a fundamental structural limitation in Excel. To use spilled arrays, you must either place the formula in a standard range outside the table or convert the table back to a normal range.

What is the best alternative to TEXTSPLIT if I must keep my table intact?

The best non-formula alternative is to use Power Query to split the column or to use the built-in 'Text to Columns' tool on the Data tab. Both methods modify the data directly rather than relying on dynamic array functions.

Excel & Data Problems