How to split text into columns in Excel: names, addresses and codes

It is very common to receive a sheet where everything sits together in a single cell: first and last name, the address with its postcode and city, or a product code that actually holds the family, the model and the colour. As long as it stays that way, you cannot sort by surname, filter by city or count products by family.

In this guide you will see three methods to split that text into columns, when each one makes sense and which mistakes to avoid so you do not lose leading zeros or turn data into dates by accident.

The sample data

We will use three typical columns. Type them from cell A1 to follow the examples:

Before splitting anything, make a copy of the sheet and insert empty columns to the right of the one you are going to split. If there is no room, Excel overwrites whatever it finds.

Method 1: Data › Text to Columns

This is the classic tool, available in every version of Excel. It splits a column by a separator character or by fixed positions, and the result is static values.

Splitting by a delimiter

1. Select the Address column (B2:B4).
2. Go to Data › Text to Columns.
3. Choose Delimited and click Next.
4. Tick Comma and clear the rest. The preview shows how the columns will look.
5. In the last step, select the postcode column in the preview and choose the Text format to keep the leading zero.
6. Check the Destination box: if you enter another cell, for example E2, the original column stays intact.
7. Click Finish.

You will get three columns: street and number, postcode and city. Because the separator was a comma followed by a space, the last two columns start with a space. You can clean it with the TRIM function or use the formula method you will see below.

Splitting by fixed width

If every value has the same structure, like the codes in column C, choose Fixed width instead of Delimited and click in the preview to place the break lines. This is useful with files exported from older systems, where each field always takes up the same positions.

Method 2: Flash Fill (Ctrl+E)

Flash Fill, available since Excel 2013, learns from an example. It works very well with names:

1. In D2, type the first person’s first name by hand: Anna.
2. Move to D3 and press Ctrl+E (or Data › Flash Fill).
3. Excel fills the rest of the column with Luke and Mary.
4. Repeat in the next column by typing the surnames (Smith Brown) so it extracts everything after the first space.

Always check the result. If the pattern is not obvious, Excel may get some rows wrong. Like Text to Columns, Flash Fill produces fixed values: if you change the original name, the extracted column does not update.

Method 3: formulas that update themselves

When the source data changes often, formulas are the better choice. Every time you edit the original cell, the split columns recalculate.

Excel 365: TEXTBEFORE, TEXTAFTER and TEXTSPLIT

These functions are available in Excel for Microsoft 365 and Excel for the web. For the first name (Anna):

=TEXTBEFORE(A2," ")

For everything after the first space (Smith Brown):

=TEXTAFTER(A2," ")

For the last word, the instance number accepts negative values, which count from the end (it returns Brown):

=TEXTAFTER(A2," ",-1)

TEXTSPLIT spreads the text across several columns in one go. It also accepts separators longer than one character, which solves the problem of the space after the comma in the addresses:

=TEXTSPLIT(B2,", ")

It returns 12 Park Lane, 02108 and Boston in three adjacent cells, with no extra spaces and with the postcode as text. Product codes work the same way: =TEXTSPLIT(C2,"-") returns PRD, 2041 and AZ. Leave the cells to the right empty so the result can spill.

Older versions: LEFT, MID, RIGHT and SEARCH

If you use Excel 2019 or earlier, combine the classic text functions. The first name is whatever comes before the first space:

=LEFT(A2,SEARCH(" ",A2)-1)

And the rest is everything from the character after the space:

=MID(A2,SEARCH(" ",A2)+1,LEN(A2))

With fixed-length codes, you only need to give positions. For PRD-2041-AZ:

• Family: =LEFT(C2,3) returns PRD.
• Model: =MID(C2,5,4) returns 2041 (as text).
• Colour: =RIGHT(C2,2) returns AZ.

Which method to choose

As a rule of thumb: for a one-off cleanup, Text to Columns or Flash Fill; for a sheet that receives new data every week, formulas.

Common mistakes when splitting text

• Leading zeros that disappear: a postcode such as 02108 becomes 2108 if the column is left in General format. Choose Text in the wizard.
• Unexpected dates: fragments such as 3-12 or 1/5 may turn into dates. Use the Text format again for those columns.
• Compound names: with “Mary Ann Jones”, splitting at the first space leaves “Ann Jones” as the surname. There is no perfect formula for this; check doubtful cases by hand.
• Double spaces: two consecutive spaces create empty columns. Clean them first with TRIM or tick the option to treat consecutive delimiters as one.
• Cells without a separator: if a cell contains no space, SEARCH returns #VALUE! and TEXTBEFORE returns #N/A. Wrap them in IFERROR, for example =IFERROR(TEXTBEFORE(A2," "),A2).

After splitting: exporting the clean data

Splitting data is usually the step before importing it into another program, such as a CRM, an online shop or an invoicing system, which almost always ask for a CSV. Once every piece of data is in its own column, you can use our free tool to convert Excel to CSV and get a file ready to upload. If the opposite happens and you receive a CSV that opens with everything in one column, you can convert the CSV to Excel or apply Text to Columns with the right separator.

Conclusion

Splitting text into columns is one of the most frequent cleanup tasks. Text to Columns handles most one-off cases, Flash Fill is ideal for names and formulas are the right option when the data changes. Choose the Text format for codes and numbers with leading zeros, and always check the result before signing off the sheet.

Frequently asked questions