Split a column into several in Excel and CSV files

How to split a column manually in Excel

Excel offers several ways to break one column into many. Each has a catch worth knowing before you use it on a real file.

Text to Columns

1. Insert enough empty columns to the right of the column you want to split. Text to Columns writes over whatever is next to it.
2. Select the column, then go to Data › Text to Columns.
3. Choose Delimited if the parts are separated by a character, or Fixed width if every part has the same length. Click Next.
4. Tick the separator (Tab, Semicolon, Comma, Space or Other). Tick Treat consecutive delimiters as one if some cells contain double spaces, otherwise you get empty columns.
5. On the last step, click each column in the preview and set Column data format to Text for anything with leading zeros, such as PIN codes or customer numbers. Set Destination to an empty cell if you do not want to overwrite neighbouring data.
6. Click Finish. If cells to the right already contain data, Excel asks once whether to replace it, and clicking OK overwrites them.

The wizard is a one-off action: if a new row is added later, you run it again.

Flash Fill

Type the first name of the first row in an empty column, for example “Rahul” beside “Rahul Kumar Mehta”, then press Ctrl+E. Excel guesses the pattern and fills the rest. It is fast for tidy lists, but it guesses wrongly when rows have different numbers of parts, so check a few rows by hand.

TEXTSPLIT, TEXTBEFORE and TEXTAFTER

In Microsoft 365, =TEXTSPLIT(A2,", ") spills each part of the cell into the columns to the right. =TEXTBEFORE(A2," ") returns everything before the first space and =TEXTAFTER(A2," ",-1) everything after the last one, which is a neat way to get a first and last name. These formulas are not available in Excel 2016 or 2019, and the result disappears if you delete the source column, so paste the values back before cleaning up.

LEFT, MID and FIND in older versions

Older versions need nested formulas, for example =LEFT(A2,FIND(" ",A2)-1) for the first word and =MID(A2,FIND(" ",A2)+1,LEN(A2)) for the rest. They return #VALUE! for any cell without a space, so most people wrap them in IFERROR.

Power Query

Select the data, choose Data › From Table/Range, then Home › Split Column › By Delimiter or By Number of Characters. Power Query is reliable for repeated imports but heavy for a quick one-time job.

Where this tool helps

Full names into first and last name. CRMs and email platforms usually want separate name fields. Split at a space with “Most columns to create” set to 2 to keep middle names with the surname, or leave it at 0 to get one column per word.

Addresses into city, state and PIN. A column like “Pune, Maharashtra, 411001” splits at a comma into three columns. Because the parts are trimmed, there is no stray space at the start of “Maharashtra”.

Product and customer codes. A code such as “MH-000124” splits at a dash into the state prefix and the number, and the number keeps its zeros. Codes with no separator, like “ABC123XY”, can be split at fixed positions: entering “3, 6” gives “ABC”, “123” and “XY”.

“Last, First” lists. Exports from some HR and school systems write names as “Mehta, Rahul”. Split at a comma and name the new columns Last name, First name.

How the tool handles the details

• New columns go where the old one was, so the rest of the sheet keeps its order. Turn on “Keep the original column” to keep the source next to its parts.
• Unnamed columns are numbered after the source, such as Address 1, Address 2 and Address 3. Names you type are used from left to right.
• Up to 50 columns can be created, enough for the longest cell in almost any file.
• Nothing is overwritten. Unlike Text to Columns, the new columns are inserted, so the data to the right moves along rather than being replaced.
• A plain summary tells you what happened, for example “Split 5 cells in Address at a comma into 3 columns: Address 1, Address 2 and Address 3.” If no cell contains the separator, the file is left unchanged and the summary says so.

The output keeps the format you opened, so a CSV comes back as a CSV and an Excel workbook as an Excel workbook.

Examples

Before you split

Remove extra spaces and fix the case first if the column is messy, so the new columns come out clean. If you later need the parts joined back together in a different order, the Combine columns tool does the reverse.