Function Problems

How to Fix Excel TEXTSPLIT Column Shifts for Multiple-Word Names

WPS Editor Sep 25, 2026 870 views

Question details

The user needs a formula to extract and split imported text containing names with a variable number of words without misaligning the subsequent data columns.

How to Prevent Data Column Shifts When Using Excel TEXTSPLIT on Multiple-Word Names

Product: Excel
Device & OS: not provided
Scenario: Importing delimited data into Excel where some rows contain three-word names, causing the standard split function to push roles, hours, and payment data into incorrect columns.
Observed behavior: When using a standard TEXTSPLIT function, rows with multiple-word names output more columns than standard rows, causing the trailing data columns to shift to the right and misalign.

Before you start

Ensure you are using a modern version of your spreadsheet software (like Microsoft 365) that supports dynamic array functions such as TEXTSPLIT, LET, CHOOSECOLS, and HSTACK.

Solution 1Recommended

Use LET and TEXTSPLIT to Conditionally Combine Extra Name Fields

By wrapping the TEXTSPLIT function inside a LET function, you can count the generated columns and use an IF statement to merge the extra name words back together.

This formula works by splitting the data into a temporary array variable 'd'. It then checks if the resulting array has extra columns due to a multiple-word name.

If extra columns are detected, it uses HSTACK and TEXTJOIN to merge the name columns back into one, keeping the remaining data columns (role, hours, payment) in their correct positions.

Select the destination cell

Click on the first cell in your worksheet where you want the aligned split data to appear.

Enter the dynamic LET formula

Type the following formula, adjusting the cell reference to match your imported data: =LET(d,TEXTSPLIT('Import Data'!$N2," ",-3),IF(COLUMNS(d)=8,CHOOSECOLS(d,1,2,3,4,5,6,8),HSTACK(CHOOSECOLS(d,1,2),TEXTJOIN(" ",TRUE,CHOOSECOLS(d,3,4)),CHOOSECOLS(d,5,6,7,9))))

Apply and drag

Press Enter to execute the formula. The data will spill into the adjacent columns. Drag the fill handle down to apply this logic to the rest of your imported rows.

Use LET and TEXTSPLIT to Conditionally Combine Extra Name Fields

Adjusting Column Index Numbers: The CHOOSECOLS index numbers (1,2,3, etc.) depend entirely on the structure of your specific data. You will need to change these numbers to match which columns contain the first name, middle name, last name, and trailing data.

Solution 2

Clean and Merge Splitted Data Using Power Query

If complex array formulas are difficult to maintain, Power Query provides a visual method to split delimiters and merge variable-length names.

Handle Complex Data Splitting Seamlessly in WPS Spreadsheet

WPS Spreadsheet offers powerful data processing tools and fully supports modern array calculations, making it incredibly easy to parse, clean, and analyze complex imported data sets.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your unformatted import data.
2. Apply dynamic array formulas: Select a blank cell and input your dynamic text manipulation formulas. WPS will automatically handle the spilled arrays.
3. Use data tools: Alternatively, navigate to the Data tab to utilize the intuitive Text to Columns feature for quick, visual data separation.

Fully compatible with Microsoft Excel formulas, functions, and .xlsx formatsSupports advanced data tools like Text to Columns and Power Query alternativesLightweight, fast-loading, and free to use for everyday office tasksFamiliar user interface requires no learning curve for Excel users

microsoft office alternative - wps office

Frequently Asked Questions

Why does TEXTSPLIT shift my adjacent columns?

TEXTSPLIT outputs an array of values that occupy as many columns as there are delimited items in your text string. If a specific row contains more words (like a 3-word name instead of a 2-word name), it outputs an extra column, which pushes all subsequent data in that row further to the right.

What is the purpose of the LET function in this formula?

The LET function allows you to assign the result of the TEXTSPLIT calculation to a defined variable (in this case, 'd'). This makes the formula significantly shorter and improves calculation performance, as Excel does not have to re-evaluate the TEXTSPLIT operation multiple times for the IF condition.

Can I use Text to Columns instead of the TEXTSPLIT formula?

Yes. The 'Text to Columns' feature under the Data tab is a great visual way to separate delimited data. However, unlike a dynamic TEXTSPLIT formula, Text to Columns is a static, one-time operation. You will also need to manually merge the extra name columns together after the split.

Why am I getting a #NAME? error when using TEXTSPLIT?

The #NAME? error typically occurs if you are using an older version of Excel (like Excel 2016 or 2019) that does not support modern dynamic array functions. TEXTSPLIT, HSTACK, and CHOOSECOLS require Microsoft 365, Excel for the Web, or a compatible modern spreadsheet software.

Excel & Data Problems