How to Apply a Formula to a Range and Split Full Names in Excel
Question details
The user needs to apply a text manipulation formula to a large range of cells (B3:B1873) and separate a single column of full names into two distinct columns for first and last names.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Data cleaning and formatting in a spreadsheet where bulk text extraction and name separation are required simultaneously.
- Observed behavior
- The dataset contains full names combined in one column, requiring an efficient method to process the entire range to extract specific string portions and split the names without manual entry.
Ensure your dataset has empty columns immediately to the right of the names column, as splitting the text will require adjacent blank cells to accommodate the separated data without overwriting existing information.
Use Dynamic Array Formulas and TEXTSPLIT
Utilize Microsoft 365's dynamic array capabilities to spill formulas across the range and the TEXTSPLIT function to divide names automatically.
If you are using Microsoft 365 or Excel for the Web, you can apply a single formula that automatically 'spills' the results down the entire range without needing to drag the fill handle.
Click an empty cell adjacent to your data and enter =LEFT(B3:B1873,LEN(B3:B1873)-10). Press Enter to spill the results automatically for the entire range of B3:B1873.
In another empty column next to your full names (e.g., cell C2), enter =TEXTSPLIT(A2, " "). This will separate the first and last names based on the space character.
Select the cell containing the TEXTSPLIT formula, then double-click the fill handle (the small square at the bottom-right of the cell) to apply the split to all names in your dataset.
Use the Text to Columns Feature
A reliable built-in wizard available in all versions of Excel for separating text into multiple columns based on a delimiter like a space.
Use Flash Fill for Quick Separation
Train the spreadsheet to recognize your formatting pattern and automatically fill in the separated names.
Split Full Names and Apply Bulk Formulas in WPS Office
WPS Spreadsheet offers powerful data processing tools including an intuitive Text to Columns wizard, intelligent Flash Fill, and advanced functions to clean and organize your datasets effortlessly.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your Excel workbook containing the full names.
- 2. Launch Text to Columns: Highlight the column with the names, navigate to the 'Data' tab, and select 'Text to Columns'.
- 3. Apply space delimiter: Choose 'Delimited', set the delimiter to 'Space', and click 'Finish' to instantly separate first and last names across adjacent columns.
Frequently Asked Questions
Why is the TEXTSPLIT function returning a #NAME? error?
The TEXTSPLIT function is only available in Microsoft 365 and Excel for the web. If you are using an older desktop version like Excel 2019 or Excel 2016, the function will not be recognized. In this case, rely on the Text to Columns feature or Flash Fill instead.
How do I prevent my split data from overwriting existing columns?
Before using Text to Columns or array formulas, ensure there are enough completely empty columns immediately to the right of your target column. You can right-click the column header next to your data and select 'Insert' to create new blank columns.
Can I split names that are separated by a comma instead of a space?
Yes. If you are using the Text to Columns wizard, simply check the 'Comma' option in the Delimiters menu. If using the TEXTSPLIT function, adjust the formula to use a comma as the delimiter, like this: =TEXTSPLIT(A2, ",").
What does the formula =LEFT(B3:B1873,LEN(B3:B1873)-10) actually do?
This is a dynamic array formula that removes the last 10 characters from every cell in the range B3:B1873 simultaneously. The LEN function calculates the total number of characters in each cell, and the LEFT function extracts the remaining characters from the left side of the string.
