How to Split Text in Excel: Text to Columns, Formulas and TEXTSPLIT

shape
shape
shape
shape
shape
shape
shape
shape
How to Split Text in Excel: Text to Columns, Formulas and TEXTSPLIT - featured image

Imported data often arrives with several values squeezed into one cell, such as a full name, an address with commas, or a code that joins a prefix and a number. Splitting that text into separate columns makes it possible to sort, filter and summarize it. Excel offers several ways to do this: the Text to Columns wizard, Flash Fill, classic formulas with LEFT, MID, RIGHT and FIND, and the newer TEXTSPLIT, TEXTBEFORE and TEXTAFTER functions. This guide shows each method with examples you can check by hand, explains which one to choose, covers names, emails, dates and addresses, and shows how to deal with extra spaces and missing delimiters.

Updated November 2026

In this guide

  1. The sample data
  2. Method 1: Text to Columns
  3. Method 2: Fixed width
  4. Method 3: Flash Fill
  5. Method 4: LEFT, MID, RIGHT and FIND formulas
  6. Method 5: TEXTSPLIT, TEXTBEFORE and TEXTAFTER
  7. Split a code into parts
  8. Split a name with a middle name or a prefix
  9. Split into rows instead of columns
  10. Join the text back together
  11. Fix common splitting problems
  12. Worked example: clean a contact list
  13. Which method should you choose
  14. Quick recap
  15. Practice problems
  16. Common mistakes

The sample data

The examples use these values in A2:A5.

ValueType
Sara KhanFull name
ali@example.comEmail
Lahore, Punjab, PakistanAddress
INV-2026-0045Code

Method 1: Text to Columns

This wizard is the quickest way to split a whole column. Select the cells, go to the Data tab and click Text to Columns. Choose Delimited and click Next. Tick the delimiter that separates your values, such as Space, Comma or Other, and for Other type the character, such as a dash. The preview shows the result. Click Next, choose the format for each column, pick a destination cell so that you do not overwrite the original, and click Finish. For Lahore, Punjab, Pakistan with a comma delimiter, you get three columns: Lahore, Punjab and Pakistan. Tick Treat consecutive delimiters as one if the data has double spaces. The result is static, so it does not update if the source changes.

Method 2: Fixed width

If the pieces always have the same length, such as a product code with 3 letters followed by 5 digits, choose Fixed width in the wizard instead. Click in the preview to set break lines, and drag them to move them. Fixed width is useful for old reports and exports where columns are aligned with spaces. It is risky when lengths vary, because one long value will be cut in the wrong place, so check a few rows at the end of the data before you finish.

Method 3: Flash Fill

Flash Fill learns from an example. In B2 next to Sara Khan, type Sara. Press Enter, then in B3 start typing the first name of the next row, and Excel shows a grey preview of the rest. Press Enter to accept. You can also press Ctrl+E on Windows. Do the same in column C for the last names. Flash Fill works well for simple patterns, but it does not update if the data changes, and it may guess wrongly when the pattern is not consistent, so check the results. Use it for one-off clean-ups, and use formulas for work that you will repeat.

Method 4: LEFT, MID, RIGHT and FIND formulas

These classic functions work in every version of Excel. To get the first name from Sara Khan in A2, find the position of the space with =FIND(" ",A2), which returns 5. Then take the characters before it with =LEFT(A2,FIND(" ",A2)-1), which returns Sara. For the last name, use =MID(A2,FIND(" ",A2)+1,100) or =RIGHT(A2,LEN(A2)-FIND(" ",A2)), which returns Khan. For the part of an email before the at sign, use =LEFT(A3,FIND("@",A3)-1), which returns ali, and for the domain use =MID(A3,FIND("@",A3)+1,100), which returns example.com. The idea is always to find the delimiter and cut around it. Combine with TRIM, as in our guide to removing spaces in Excel, to clear extra spaces.

Method 5: TEXTSPLIT, TEXTBEFORE and TEXTAFTER

In Microsoft 365, TEXTSPLIT splits text in one formula: =TEXTSPLIT(A4,", ") returns Lahore, Punjab and Pakistan in three cells next to each other. The second argument is the column delimiter, and a third argument can set a row delimiter. TEXTBEFORE returns the text before a delimiter, such as =TEXTBEFORE(A2," "), which returns Sara, and TEXTAFTER returns the text after, such as =TEXTAFTER(A2," "), which returns Khan. For the domain in an email, =TEXTAFTER(A3,"@") returns example.com. These functions are shorter and easier to read than FIND-based formulas, and the result updates when the source changes. They are not available in Excel 2019 or earlier.

Split a code into parts

For the code INV-2026-0045, use TEXTSPLIT with a dash: =TEXTSPLIT(A5,"-") returns INV, 2026 and 0045. Note that 0045 is returned as text, which keeps the leading zeros. If you need numbers, wrap with VALUE or multiply by 1, as shown in our guide to converting text to numbers in Excel. With older formulas, the second part uses MID: =MID(A5,5,4) returns 2026, since the first dash is at position 4, and the year starts at position 5. For the last part, use =RIGHT(A5,4), which returns 0045.

Split a name with a middle name or a prefix

Real data is messy. A name like Mary Ann Smith has two spaces. The first name is the text before the first space, and the last name is the text after the last space. For the last word, use =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)). This formula replaces every space with 100 spaces, takes the last 100 characters, and trims the extra spaces, which leaves the last word. In Microsoft 365, =TEXTAFTER(A2," ",-1) returns the text after the last space, and a negative number in the instance argument counts from the end. For prefixes such as Dr or Mr, remove them first with SUBSTITUTE, or use a lookup list.

Split into rows instead of columns

If a cell holds a list, such as red;green;blue, you may want separate rows. =TEXTSPLIT(A2,,";") leaves the column delimiter empty and uses a row delimiter, so the three values appear in three rows. The older method uses Text to Columns, and then you copy the result and use Paste Special with Transpose, as covered in our guide to transposing in Excel. For a large list, Power Query is a good option: choose Split Column, then By Delimiter, and select Split into Rows.

Join the text back together

The reverse of splitting is combining. Use =A2&" "&B2 or the CONCAT and TEXTJOIN functions to join the pieces. Our guide to combining text in Excel shows each option. A good habit is to keep the original column and add the split columns next to it, so that you can check the results and repeat the process if the source data changes.

Fix common splitting problems

If the split leaves extra spaces at the start of a value, wrap the formula with TRIM or choose Space as an extra delimiter with consecutive delimiters treated as one. If numbers lose leading zeros after Text to Columns, set the column data format to Text in step 3 of the wizard. If dates change format, set the column format to Date with the correct order, such as DMY. If a formula returns #VALUE!, the delimiter was not found in that row, so wrap it with IFERROR, such as =IFERROR(LEFT(A2,FIND(" ",A2)-1),A2), which returns the whole text when there is no space. Hidden non-breaking spaces from web pages need SUBSTITUTE with CHAR(160).

Worked example: clean a contact list

A client sends 500 contacts in one column, with entries such as Sara Khan, sara@example.com, Lahore. Start by copying the original column to a safe sheet. Use Text to Columns with a comma delimiter to separate the three parts. Then trim each column with =TRIM(B2) to remove spaces that follow the commas. Use TEXTBEFORE or LEFT with FIND to split the name into first and last name, and TEXTAFTER on the email to get the domain. Add a column with =COUNTIF(D:D,D2) on the domain to see which companies appear most often. Finally, remove duplicate rows with the steps in our duplicates guide, sort by last name as shown in our sorting guide, and freeze the header row. The result is a clean table with one fact in each column, which can be filtered, imported into a CRM or used for a mail merge without further work.

Which method should you choose

Use Text to Columns for a quick one-time split of a whole column. Use Flash Fill for a simple pattern and a small list. Use LEFT, MID, RIGHT and FIND when the file must work in older versions of Excel, or when other people will edit it. Use TEXTSPLIT, TEXTBEFORE and TEXTAFTER in Microsoft 365 when you want short formulas that update with the data. Use Power Query when you import a new file every week and want the steps saved and repeated. If you are unsure, start with Text to Columns on a copy and compare the result with a formula, so you know what the data looks like before you commit.

Quick recap

Split text with Text to Columns, Flash Fill, formulas built from FIND, LEFT, MID and RIGHT, or the newer TEXTSPLIT, TEXTBEFORE and TEXTAFTER. Keep the original data, trim spaces, protect leading zeros with the Text format, handle missing delimiters with IFERROR and check the first, last and longest rows before you rely on the result.

Practice problems

Try these. Problem 1: split Sara Khan into first and last names with Text to Columns. Problem 2: extract the domain from ali@example.com with a formula. The answer is example.com. Problem 3: use a formula to get the city, Lahore, from the address in A4. The answer is =LEFT(A4,FIND(",",A4)-1). Problem 4: get the year 2026 from the code INV-2026-0045. Problem 5: use Flash Fill to get the initials of five names. Check each result, and then try the same tasks with TEXTSPLIT if your version supports it.

Common mistakes

The most common mistake is to run Text to Columns without choosing a destination, which overwrites the cells to the right. Another is to trust Flash Fill without checking all rows. A third is to forget that FIND is case sensitive and SEARCH is not. A fourth is to split text that contains the delimiter inside a value, such as a comma inside a quoted company name. A fifth is to forget that split results from Text to Columns are static. Always keep a copy of the original data, and test your method on the longest and the strangest values in the list.

Frequently asked questions

How do I split text in Excel?

Use Data, Text to Columns, or a formula such as TEXTSPLIT, or LEFT and FIND in older versions.

How do I split a full name into first and last name?

Use Text to Columns with a space, Flash Fill, or LEFT and MID with FIND.

What is TEXTSPLIT?

A function in Microsoft 365, Excel 2021 and Excel 2024 that splits text by a delimiter into columns or rows.

Why did my leading zeros disappear?

The column was converted to numbers. Set the format to Text in the Text to Columns wizard.

Does Flash Fill update automatically?

No. It gives static results.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *