Fix Excel TEXTSPLIT Returning One Column with Arrays
Question details
Users need a workaround for Excel's TEXTSPLIT function, which fails to spill data into multiple columns and rows when an array is supplied as the text argument.
- Product
- Excel
- Device & OS
- Windows and Mac
- Scenario
- Attempting to split an array of text strings into a two-dimensional range (both rows and columns) using the TEXTSPLIT function.
- Observed behavior
- TEXTSPLIT evaluates the provided array as a single-column input, returning only one column and failing to produce the expected two-dimensional spill range.
Ensure your version of Excel supports Dynamic Arrays and the TEXTSPLIT function, which are primarily available in Microsoft 365 and Excel for the Web.
Combine the Array Using TEXTJOIN Before Splitting
This is the best workaround to convert the entire array into a single text string so TEXTSPLIT can process it as a 2D spill range.
TEXTSPLIT is natively designed to handle a single text string rather than an array. Passing an array forces Excel to evaluate it as a single-column input, preventing the function from spilling properly.
By using TEXTJOIN first, you merge the array into one delimited string, which allows TEXTSPLIT to correctly divide the data into multiple rows and columns.
Click on the cell where you want the split data to begin spilling.
Type the formula =TEXTSPLIT(TEXTJOIN(";",TRUE,Table1[Column]),"/",";") and adjust the delimiters and column reference to match your specific dataset.
Press Enter. The formula will now evaluate the joined string and correctly spill the results across both rows and columns.
Apply TEXTSPLIT to Individual Cells and Fill Down
Ideal if you prefer not to use TEXTJOIN and want to process the data row-by-row manually.
Looking for a Better Way to Manage Spreadsheets? Try WPS Office
If complex array formulas and function limitations in Excel are slowing you down, WPS Office provides a lightweight, highly compatible, and free alternative. Enjoy a familiar interface and robust spreadsheet capabilities without the subscription fees.
- 1. Download WPS Office: Visit the official WPS website and download the free installation package for your operating system.
- 2. Install the software: Run the installer and follow the simple on-screen instructions to set up WPS Office on your device.
- 3. Open your Excel files: Launch WPS Spreadsheet and open your existing .xlsx files to continue working seamlessly.
Frequently Asked Questions
Is it a bug that TEXTSPLIT only returns one column with an array?
No, this is not a bug. TEXTSPLIT is specifically designed to operate on a single text string. When an array is passed, Excel evaluates it into a single column first, which causes the output to remain a single column while discarding other results.
Can I use the Text to Columns feature instead of TEXTSPLIT?
Yes. For static data, you can bypass complex formulas entirely by selecting your column, navigating to the Data tab, and choosing 'Text to Columns' to split your data based on your preferred delimiters.
Does the TEXTJOIN workaround work on Excel for Mac?
Yes, combining your array with TEXTJOIN before applying TEXTSPLIT works exactly the same on Excel for Mac as it does on Excel for Windows, provided your version supports dynamic array functions.
