Free Excel workbook
Get the Text to Columns Workbook
Download the ready-to-use workbook and practice the exact steps from the tutorial.
- Practice splitting names and serial numbers with real examples
- See both delimited and fixed-width setups already built in
- Includes the quick fix for VLOOKUP errors caused by number-text mismatches
Enter your email below for instant access.
Free. Instant access.
Text to Columns in Excel easily splits cells. For example, it can separate data in a cell like first names from last names. Moreover, it’s especially handy when you often need to deal with data from many sources. These sources include text files and databases.
The Text to Columns tool also fixes common Excel issues. For instance, it helps when VLOOKUP can’t find data due to number formatting errors. By correcting these errors, it ensures accurate and reliable searches.
This keeps your spreadsheet tidy and functional, allowing you to work more efficiently.
How do I separate first and last names in Excel?
A common problem is that you have a list of names where both first and last names are in the same column. We’ll show you how to separate first and last name in Excel with Text to Columns.
But you want to separate the names into separate columns. Text to Columns in Excel can do just that, making your list much easier to manage.
Here’s a quick guide on how to split first and last name in Excel:
- Select Your Data: Highlight the column that contains the full names.
- Access the Tool: Click on the ‘Data‘ tab, then select ‘Text to Columns’.
- Choose Split Type: In the dialog box, you’ll see two options: ‘Delimited’ and ‘Fixed Width’. Choose ‘Delimited‘ if your text is separated by characters such as spaces, commas, or semicolons. Select ‘Fixed Width‘ to divide your data at certain spots, like after a set number of characters. For our names example, we select ‘Delimited’ because the names are separated by spaces.
- Select the Delimiter: After choosing ‘Delimited’, click ‘Next’ and select the delimiter that corresponds to how your names are separated. For example, use ‘Space’ for names separated by spaces, or choose commas or semicolons for other types of separations. In the data preview window below, you can immediately see if the split works correctly.
- Specify the Destination: Decide where you want the separated names to go. If you don’t pick a specific cell for the output, Excel will replace your existing data. The first part of the data will appear in the original column, and the next part will go into the column right next to it.
❗ To keep your original data unchanged, make sure to choose a new destination cell. For instance, in this example, we started with cell B2 to keep the original names.
- Complete the Process: Click ‘Finish’, and it will separate names in Excel into two columns.
- Capitalize Names: For a polished look, you can use the Excel PROPER function to capitalize each name properly.
= PROPER(B2:C13)
This way, each name in the range from D2 to C13 appears capitalized in two separate columns D and E.
How do I split cells by fixed width in Excel?
A helpful way to use Excel’s Text to Columns tool with the Fixed Width feature is when you want to split complex numbers into easier-to-manage pieces. Let’s say you have a list of serial numbers where each one includes a type, a year, and a unique identifier.
The goal is to separate this data into three columns:
- Product Type: The first 2 letters (e.g., TV, PH, LA)
- Manufacture Year: The following 4 digits (e.g., 2018, 2019, 2010)
- Unique Identifier: The final 5 digits (e.g., 12345, 56789, 45678)
How to Split cells in Excel:
- Highlight the column that contains the serial numbers.
- Navigate to the ‘Data’ tab, and select ‘Text to Columns.’
- Choose ‘Fixed Width.’ Excel won’t automatically know where to split your data, so you’ll need to set this manually in the next step.
Set Your Breaks: In the preview area, adjust the column breaks:
- After the 2nd character to isolate the product type.
- After the 6th character to isolate the manufacture year.
Any remaining characters will be recognized as the product ID.
- Specify the Destination: Choose where you want the split data to appear in your sheet. If you don’t select a new destination, Excel will overwrite the existing data.
- Finish: Click ‘Finish’, and Excel will apply the splits.
Using the technique to split cells in Excel neatly organizes each part of the serial number into its own column, enhancing your data’s clarity and accessibility for analysis or record-keeping.
How does Text to Columns fix VLOOKUP #N/A errors?
If you’ve ever used Excel’s VLOOKUP function and received a #N/A error, it might be due to a mismatch in data formats.
Often, this happens when numbers you’re looking up the result of a copy/paste action from some external data source.
Example of the VLOOKUP Problem:
Imagine you have a table with product codes in column A (formatted as text) and descriptions in column B.
You want to find the description for a product code you enter in cell D2 using VLOOKUP in cell D3:
=VLOOKUP(D2, A2:B11, 2, False)
But instead of a description, the VLOOKUP function returns a #N/A error because the format of D2 (number) doesn’t match the text format of the codes in column A.
Simple Solution Using Text to Columns:
- Select the Data: Click to highlight column A, which has your product codes.
- Open Text to Columns: Go to the Data tab, and click ‘Text to Columns’.
- Convert: Choose ‘Fixed Width’ (since we’re not splitting based on delimiters) and click ‘Finish’ right away without setting any breaks.
This changes the format from text to numbers, matching your VLOOKUP search criteria to the table.
After this quick fix, running the VLOOKUP formula again should give you the correct results, not errors. This method ensures your data types are aligned, allowing Excel’s functions to work smoothly.
Excel Text to Columns transformed all our numbers stored as text into actual numbers.
What is the difference between Text to Columns and TEXTSPLIT?
Text to Columns is a one-time action that splits data and stops updating. TEXTSPLIT is a dynamic array function in Microsoft 365 that recalculates every time source data changes.
Use TEXTSPLIT when your source list keeps growing or changing. Use Text to Columns when you just need a one-off cleanup.
TEXTSPLIT example
To split “Jane Doe” in cell A2 into first and last name:
=TEXTSPLIT(A2, " ")The result spills across two cells automatically. Add a new name to column A, and the split updates instantly. No wizard, no re-running the tool.
Quick comparison
| Syntax | Meaning | Example |
|---|---|---|
| (abc) | Capture group | Use with REGEXEXTRACT return_mode 2 to extract specific portions |
| a|b | OR (alternation) | cat|dog matches “cat” or “dog” |
| [abc] | Character class (any one of a, b, or c) | [aeiou] matches any vowel |
| [^abc] | Negated class (any character except a, b, c) | [^0-9] matches any non-digit |
| [A-Z] | Range (any uppercase letter) | [A-Za-z] matches any letter |
For multi-character delimiters, empty-cell handling, and 2D splitting with both row and column delimiters, see our full guide on how to use the TEXTSPLIT function.
💡 Pro tip: Wrap TEXTSPLIT in TRIM to handle extra spaces: =TRIM(TEXTSPLIT(A2, " ")). This catches double spaces that would otherwise create empty cells in the result.
Can I use Flash Fill instead of Text to Columns?
Yes. Flash Fill (Ctrl+E) is often the fastest way to split data in Excel when the pattern is obvious. It watches what you type, detects the pattern, and fills the rest.
How to use Flash Fill to split names
- In cell B2, type the first name from A2 (for example, “Jane”).
- Press Ctrl+E or go to Data > Flash Fill.
- Excel fills the rest of column B with first names automatically.
- Repeat in column C for last names.
Flash Fill can do more than split names. Use it to create initials, build email addresses from names, reformat phone numbers, or extract years and months from dates. For a full walkthrough with examples, see our guide on how to use Flash Fill in Excel.
When Flash Fill beats Text to Columns
- No wizard. One keystroke instead of three wizard screens.
- Works with irregular patterns. Extract middle initials, strip titles like “Dr.” or “Mr.”, or split “LastName, FirstName” without changing the logic.
- Clean, capitalized output. Flash Fill applies casing from your example, so no PROPER function is needed. See our guide on changing the case of text in Excel for more case-conversion methods.
When Flash Fill fails
- Inconsistent source data. If half your rows have middle names and half don’t, Flash Fill picks one pattern and ignores the rest.
- Large datasets. Past about 10,000 rows, Flash Fill slows down. Text to Columns or TEXTSPLIT handle volume better.
- Complex delimiters. Multi-character delimiters (like ” | “) confuse Flash Fill more often than Text to Columns.
Rule of thumb: Try Flash Fill first. If it does not catch your pattern after two or three example entries, switch to Text to Columns or TEXTSPLIT.
Can Copilot split cells in Excel?
Yes. With Edit with Copilot in Microsoft 365, you can split cells using plain-language prompts. Copilot writes the TEXTSPLIT formula, applies Flash Fill, or runs Text to Columns for you, depending on the request.
Example prompts
Open the Copilot pane in Excel and type any of these:
“Split column A into first name and last name in columns B and C.”
“Separate the product code, year, and ID from column A. The code is 2 letters, the year is 4 digits, the ID is 5 digits.”
“Clean up column A so the numbers stored as text become real numbers.”
Copilot recognizes the pattern and applies the right method. For name splitting it usually writes a TEXTSPLIT formula. For fixed-width serial numbers it uses LEFT, MID, and RIGHT. For format fixes it runs a Text to Columns-style conversion.
Requirements
- Subscription: Microsoft 365 Personal, Family, or Premium with AI credits; or a commercial Microsoft 365 Copilot license
- File location: Workbook must be an .xlsx file saved to OneDrive or SharePoint
- Version: Microsoft 365 desktop (Windows or Mac) or Excel for the web
When Copilot beats Text to Columns
- Irregular patterns. Copilot handles mixed formats that break Flash Fill and Text to Columns
- Complex multi-step splits. Split, clean, and format in one prompt
- You do not know the function name. Describe the outcome and let Copilot pick the tool
When Text to Columns still wins
- No subscription required beyond Excel. Text to Columns ships with every version
- Works offline. No cloud dependency
- Transparent. You see exactly what happens in the wizard. Copilot output is harder to audit when the result looks off
For one-time cleanups on a workbook you trust, Text to Columns is still the fastest and most reliable tool. For messier, repeatable jobs where the data keeps changing, Power Query for text transformation is still the most reliable option when you need something more powerful than Copilot or TEXTSPLIT.
Text to Columns tips and limitations
A few things to know before you run the wizard on important data:
- Back up before overwriting. Text to Columns replaces the source column by default. Copy your data to a new sheet or duplicate the column first if you are not sure
- Leading zeros disappear on numbers. Codes like “00123” will become “123” after splitting unless you set the column data type to Text in step 3 of the wizard
- Dates can flip format. If Excel detects dates during splitting, it may reinterpret DD/MM/YYYY as MM/DD/YYYY based on your regional settings. Set the column to Text in the wizard to preserve the original format
- Merged cells will break the split. Unmerge cells in your source column before running the wizard, or Excel will only process the first cell in each merged group
- The Undo stack is your friend. If the result looks wrong, Ctrl+Z reverses the split cleanly. You have one chance before saving
Text to Columns FAQ
How do I split one column into multiple in Excel?
Select the column, go to Data > Text to Columns, pick Delimited for spaces or commas or Fixed Width for set character positions, then click Finish. Choose a destination cell to keep your original data intact.
What is the difference between Text to Columns and TEXTSPLIT?
Text to Columns is a one-time action that splits data and stops updating. TEXTSPLIT is a dynamic array function in Microsoft 365 that recalculates when source data changes. Use TEXTSPLIT for live updates, Text to Columns for static cleanup.
Why does Text to Columns overwrite my data?
By default, Excel places split results in the original column and the column to its right. If those cells contain data, they get overwritten without warning. To prevent this, pick an empty destination cell in step 3 of the wizard.
Can I use Text to Columns to fix VLOOKUP #N/A errors?
Yes. If your lookup values are stored as text but the lookup column is numeric (or vice versa), VLOOKUP returns #N/A. Select the problem column, open Text to Columns, pick Fixed Width, and click Finish without setting breaks. This forces Excel to re-evaluate the format.
Does Text to Columns update automatically when source data changes?
No. Text to Columns is a one-time operation. If the source column changes, you need to run the wizard again. For automatic updates, use TEXTSPLIT (Microsoft 365) or Power Query.
Can Copilot split cells in Excel?
Yes. With Edit with Copilot in Microsoft 365, you can prompt Copilot to split a column by description. For example: “Split column A into first name and last name.” Copilot writes the TEXTSPLIT formula or uses Flash Fill automatically.
Download the Workbook
Get the Text to Columns Practice Workbook
Follow along with names, serial numbers, and the VLOOKUP fix example.
Download the Free Workbook
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
