How to split text by a delimiter in Excel

Method 1: TEXTSPLIT (Excel 365 and 2024)

This contact export crams name, city and country into one column. TEXTSPLIT takes the text and the delimiter and returns one piece per column. Split A2 at "," and you get Lena Fischer, " Berlin" and " Germany", each with the space that followed the comma. Wrap the whole thing in TRIM, which trims every piece of the result, and the spaces are gone.

Why not split on ", " (comma and space) instead? Because exports are rarely consistent. Row 4 has no spaces after its commas, so ", " finds nothing there and returns the whole text in one cell. Splitting on the comma alone and trimming handles both, and also the double space in row 6. For a dash, use =TEXTSPLIT(B2,"-"), which turns TSH-BLK-M into TSH, BLK and M.

Split a whole column, and the #N/A padding

Fill =TRIM(TEXTSPLIT(A2,",")) down the column and each row spills its own pieces to the right. That is the simplest setup. A single TEXTSPLIT pointed at A2:A6 does not give you one row per contact, because a spilled formula cannot return a separate list for each cell.

If you want one formula for the whole table, join the rows first with a row delimiter and split on both: TEXTJOIN puts a ";" between the contacts, then TEXTSPLIT splits columns at "," and rows at ";". Raj Patel has no country, so his row is one piece short. TEXTSPLIT fills the gap with #N/A unless you give it the last argument, pad_with. Here it pads with an empty text "". TEXTJOIN returns #VALUE! if the joined text passes 32,767 characters, so keep this for lists of moderate size.

Method 2: TEXTBEFORE and TEXTAFTER for one piece

Often you need only one part, such as the city, in its own column. TEXTAFTER(A2,",") returns everything after the first comma, and TEXTBEFORE then cuts that at the next comma, leaving the middle part. TEXTAFTER(A2,",",-1) gives the last part: a negative instance counts from the end.

Watch the rows with fewer parts. For Raj Patel, "Pune" has no comma after it, so a plain TEXTBEFORE returns #N/A. Its fifth argument, match_end, set to 1 treats the end of the text as a delimiter, so the formula below returns Pune for Raj and Berlin for Lena. These functions also need Excel 365 or 2024.

Method 3: MID, SUBSTITUTE and REPT (any Excel version)

Older Excel has no TEXTSPLIT. The first part is easy: =LEFT(A2,FIND(",",A2)-1) returns Lena Fischer. The middle part with FIND needs the position of the second comma and gets long fast. A shorter trick works for any position: replace every comma with 100 spaces, cut out the n-th 100-character block with MID, and TRIM the padding away.

For the 2nd piece, MID starts at character 101; for the 3rd, at 201, and so on. If a piece is missing, as Raj's country is, the result is an empty text instead of an error. Use a width larger than your longest piece; 100 suits names, cities and codes.

Method 4: Text to Columns (no formulas)

When you just need the data split once, Text to Columns is fastest and works in every version. It overwrites the original column and the columns to its right, and the result does not update if the source changes. Make sure the columns to the right are empty, or work on a copy.

Tick "Treat consecutive delimiters as one" when values are separated by several spaces. TEXTSPLIT has the same option as its fourth argument, ignore_empty: =TEXTSPLIT(A2," ",,TRUE) skips the empty piece between two spaces.

1. Select A2:A6 (one column only).
2. Go to Data > Text to Columns, choose Delimited and click Next.
3. Tick Comma (or Space, or tick Other and type a dash). Check the preview, then click Next.
4. Pick a column in the preview and set it to Text if it holds IDs or codes with leading zeros, so Excel does not turn 00742 into 742.
5. Set Destination to an empty cell such as C2 to keep the original column, then click Finish.