Fix TEXTSPLIT, TOCOL, and TOROW Functions Missing in Excel 2021
Question details
The user is trying to use new dynamic array formulas but cannot find them in Excel 2021, even after installing all available updates.
- Product
- Microsoft Excel 2021
- Device & OS
- not provided
- Scenario
- Attempting to use advanced dynamic array functions for text manipulation and array reshaping in a standalone version of Excel.
- Observed behavior
- The functions are unrecognized and return a #NAME? error. The software says it is 'up to date', but the features remain unavailable.
Verify your exact Excel version by navigating to File > Account to confirm whether you are using a perpetual license like Office 2021 or a Microsoft 365 subscription.
Use Power Query as a Native Alternative
Power Query provides robust built-in data transformation tools that can easily split text and unpivot data without needing Microsoft 365 exclusive formulas.
Since Excel 2021 does not receive feature updates, the most powerful and reliable built-in workaround for data reshaping and text splitting is Power Query. It handles complex data manipulations natively.
Select your data range, go to the Data tab on the Excel ribbon, and click 'From Table/Range'.
In the Power Query Editor, select the column you want to split. Click 'Split Column' in the Transform tab, choose 'By Delimiter', and specify the character separating your text.
To reshape your data into a single column or row, highlight the columns and use the 'Unpivot Columns' or 'Transpose' features found in the Transform tab.
Click 'Close & Load' on the Home tab to output the newly restructured data back into a fresh worksheet in Excel.
Upgrade to Microsoft 365 or a Newer Version
Since Excel 2021 is a perpetual license that only receives security patches, upgrading to Microsoft 365 is the official method to acquire new dynamic array features.
Use WPS Office for Advanced Spreadsheet Features
Avoid expensive subscription fees just to access modern spreadsheet capabilities. WPS Office provides a powerful, frequently updated spreadsheet application with a highly familiar interface, ensuring you can process data efficiently without the recurring costs.
Frequently Asked Questions
Why does Excel 2021 say 'You're up to date' but I still don't have TEXTSPLIT?
The 'up to date' message simply confirms that you have the latest security and stability patches for Excel 2021. Microsoft does not add new features to standalone, perpetual versions of Office after their initial release.
Can I download an official add-in to get TEXTSPLIT, TOCOL, and TOROW in Excel 2021?
No, there is no official Microsoft add-in that retroactively brings these specific dynamic array functions to Excel 2021. You must either upgrade your version, use alternative nested formulas, or leverage Power Query.
Are there older formulas that can replicate TEXTSPLIT?
Yes, but they are more complex. You can combine traditional text functions like MID, FIND, LEFT, RIGHT, and LEN to extract specific strings, or use the 'Text to Columns' tool in the Data tab for a manual split.
Will Excel 2024 include TEXTSPLIT?
Yes, the newer perpetual release, Excel 2024, includes many dynamic array functions that were previously exclusive to Microsoft 365 subscriptions, including TEXTSPLIT, TOCOL, and TOROW.
