Split and Combine Text in Excel With TEXTSPLIT and TEXTJOIN (Examples)

To split text in Excel, use TEXTSPLIT: give it a column delimiter such as a comma to spread a cell across columns, or a row delimiter to send the pieces down rows, and list several delimiters in curly braces when the data mixes them. To combine text, use TEXTJOIN, which joins a range with the separator you choose and can skip blank cells. TEXTBEFORE and TEXTAFTER handle the common in-between jobs, such as pulling a first name or an email domain out of a cell.

Who this is for and what you will have

This guide is for anyone who gets several values crammed into one cell, such as a CRM export or a contact list, or who needs to turn a column into one comma-separated line. By the end you will have copy-paste formulas to split cells into columns or rows, extract names, email domains and ZIP codes, build conditional lists, and do the same in older Excel versions. Every formula is worked out against the sample data below, so you can paste it into a blank sheet and compare.

Check your Excel version first

Microsoft's Applies To lists show where each function is available:

To see which Microsoft 365 plans include the desktop Excel app, read Office 365 vs Microsoft 365. Microsoft's TEXTJOIN page also asks subscribers to run the latest version of Office, so update first if a function seems to be missing.

Sample data

Paste this into cells A1:E6 of a blank sheet. The names and domains are fictional, using Microsoft's contoso.com and fabrikam.com placeholders. Leave D6 empty on purpose.

The Skills column is deliberately messy: some cells use commas, some use semicolons, D5 mixes both, and D4 has an empty value between two commas.

Split text with TEXTSPLIT

The syntax, with Microsoft's argument names, is:

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) 

• text: the text or cell to split.
• col_delimiter: where the text breaks into a new column.
• row_delimiter: where the text breaks into a new row. Optional.
• ignore_empty: TRUE skips consecutive delimiters. The default, FALSE, creates an empty cell.
• match_mode: 1 makes the delimiter match case-insensitive. The default, 0, is case-sensitive.
• pad_with: the value used to fill gaps in a two-dimensional result. The default is #N/A.

TEXTSPLIT is a dynamic array function: you type it in one cell and the results spill into the neighboring cells.

Split a cell into columns by comma

Click F2 and enter:

=TEXTSPLIT(D2,", ") 

Result: F2:H2 shows Excel, Power BI and SharePoint. The delimiter is a comma followed by a space, so the pieces come out without a leading space. If your data has inconsistent spacing, split on the comma alone and wrap the result in TRIM, which removes all spaces except single spaces between words:

=TRIM(TEXTSPLIT(D2,",")) 

The same pattern splits the address. In F2:

=TEXTSPLIT(C2,", ") 

Result: 742 Elm St, Seattle and WA 98101. Because each formula spills across its own row, you can fill it down from F2 to F6 and every row gets its own three columns.

Split a cell into rows

Leave col_delimiter empty and use row_delimiter instead, as Microsoft's own examples do:

=TEXTSPLIT(D2,,", ") 

Result: three cells stacked vertically, Excel, Power BI, SharePoint. Do not fill this version down: each copy needs several rows, so the copies collide and show #SPILL!.

Split by multiple delimiters

Put the delimiters inside an array constant in curly braces. D5 uses both a semicolon and a comma:

=TEXTSPLIT(D5,{", ","; "}) 

Result: Excel, Teams, Forms in three columns. For data without spaces, the constant is simply {",",";"}. Type the braces yourself; for array constants Excel expects you to.

Skip empty values with ignore_empty

D4 contains Power Automate, , Excel, which has nothing between the two delimiters:

=TEXTSPLIT(D4,", ") 

Result: Power Automate, an empty cell, then Excel. Set ignore_empty to TRUE to drop the gap:

=TEXTSPLIT(D4,", ",,TRUE) 

Result: Power Automate and Excel in two columns.

Case-insensitive delimiters with match_mode

Word delimiters are case-sensitive by default:

=TEXTSPLIT("Red AND Green and Blue"," and ") 

Result: Red AND Green and Blue, because only the lowercase " and " matches. Set match_mode to 1:

=TEXTSPLIT("Red AND Green and Blue"," and ",,,1) 

Result: Red, Green, Blue.

Fill gaps with pad_with

When you use both delimiters, rows can end up with different lengths, and Excel fills the gap with #N/A:

=TEXTSPLIT("Excel, Teams; Planner",", ","; ") 

Result: a 2 by 2 grid. Row 1 is Excel, Teams; row 2 is Planner, #N/A. Add an empty string as pad_with to show a blank instead:

=TEXTSPLIT("Excel, Teams; Planner",", ","; ",,,"") 

Wrapping the formula in IFNA works too.

Turn a whole column into one list

TEXTSPLIT works on one piece of text, so to split every cell in D2:D6 into a single vertical list, join the column first and then split it:

=TEXTSPLIT(TEXTJOIN(", ",TRUE,D2:D6),,{", ","; "},TRUE) 

Result: 11 rows, in order: Excel, Power BI, SharePoint, Teams, Excel, Planner, Power Automate, Excel, Excel, Teams, Forms. The TRUE at the end removes the empty piece from D4. This approach is limited by the TEXTJOIN character limit covered later.

Extract parts with TEXTBEFORE and TEXTAFTER

To pull out one piece instead of all of them, use these two functions, which share the same syntax:

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) =TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) 

instance_num defaults to 1, and a negative number searches from the end. if_not_found sets what to return when the delimiter is missing; the default is #N/A.

First name and last name

=TEXTBEFORE(A2," ") 

Result: Megan.

=TEXTAFTER(A2," ",-1) 

Result: Bowen. The -1 means "after the last space", so a name with a middle name or initial still returns the final word. Fill both down to row 6 to get Alex and Wilber, Adele and Vance, and so on.

Username and email domain

=TEXTBEFORE(B2,"@") 

Result: megan.bowen.

=TEXTAFTER(B2,"@") 

Result: contoso.com. In B4 the same formula returns fabrikam.com. To label rows that have no @ instead of showing #N/A, use the sixth argument:

=TEXTAFTER(B2,"@",,,,"No domain") 

City, state and ZIP from an address

=TEXTBEFORE(TEXTAFTER(C2,", "),", ") 

Result: Seattle. TEXTAFTER keeps everything after the first comma, then TEXTBEFORE cuts at the next one.

=TEXTBEFORE(TEXTAFTER(C2,", ",-1)," ") 

Result: WA.

=TEXTAFTER(C2," ",-1) 

Result: 98101, the text after the last space.

Single-word values with match_end

If a name cell holds a single word with no space, the first-name formula above returns #N/A. Setting match_end to 1 treats the end of the text as a delimiter, so a one-word entry returns the whole word:

=TEXTBEFORE(A2," ",,,1) 

Combine text with TEXTJOIN

The syntax is:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) 

• delimiter: the text placed between items, such as ", ". A number is treated as text.
• ignore_empty: TRUE skips empty cells.
• text1, text2: the items to join, which can be ranges.

Join a column with a comma

=TEXTJOIN(", ",TRUE,A2:A6) 

Result: Megan Bowen, Alex Wilber, Adele Vance, Diego Siciliani, Lidia Holloway.

For a semicolon-separated recipient list from column B:

=TEXTJOIN("; ",TRUE,B2:B6) 

See what ignore_empty does

D6 is empty. With TRUE:

=TEXTJOIN(", ",TRUE,D2:D6) 

Result: Excel, Power BI, SharePoint, Teams; Excel; Planner, Power Automate, , Excel, Excel; Teams, Forms

With FALSE the empty D6 still gets a delimiter, so the result ends in a trailing comma and space. Note that ignore_empty only skips empty cells. The empty piece inside D4 is part of that cell's text, so it stays.

Conditional join with IF or FILTER

To join only the names in the Sales team, filter the range first. In Microsoft 365, Excel 2024 and Excel 2021:

=TEXTJOIN(", ",TRUE,FILTER(A2:A6,E2:E6="Sales","")) 

Result: Alex Wilber, Adele Vance, Lidia Holloway. The "" is FILTER's if_empty argument; without it, a team with no matches returns #CALC!.

In Excel 2019, which has TEXTJOIN but not FILTER, use IF instead:

=TEXTJOIN(", ",TRUE,IF(E2:E6="Sales",A2:A6,"")) 

Same result. In Excel versions that use legacy array formulas, confirm this one with Ctrl+Shift+Enter instead of Enter.

For two conditions, multiply them, which FILTER treats as AND. This returns Sales email addresses on contoso.com:

=TEXTJOIN("; ",TRUE,FILTER(B2:B6,(E2:E6="Sales")*ISNUMBER(SEARCH("contoso.com",B2:B6)),"")) 

Result: alex.wilber@contoso.com; lidia.holloway@contoso.com.

To build a summary per team, type Marketing, Sales and Finance in G2:G4, then in H2:

=TEXTJOIN(", ",TRUE,FILTER($A$2:$A$6,$E$2:$E$6=G2,"")) 

Fill down. H2 shows Megan Bowen, H3 Alex Wilber, Adele Vance, Lidia Holloway, and H4 Diego Siciliani.

TEXTJOIN vs CONCAT vs CONCATENATE vs &

The same row combined three ways:

=CONCAT(A2," (",E2,")") =CONCATENATE(A2," (",E2,")") =A2&" ("&E2&")" 

Result for each: Megan Bowen (Marketing).

Microsoft says CONCAT supersedes CONCATENATE, which is kept for compatibility with older workbooks and may not be available in future versions. The & operator gives the same result as CONCATENATE and is often quicker for two or three cells. For anything with a separator or a range, use TEXTJOIN.

Older Excel: Text to Columns and Flash Fill

If you see #NAME? for TEXTSPLIT, these built-in tools give the same output as static values.

Text to Columns

1. Select the cells to split, for example C2:C6.
2. Go to Data > Text to Columns.
3. In the Convert Text to Columns Wizard, choose Delimited, then Next.
4. Select the delimiters, for example Comma, and check the Data preview.
5. Select Next, set the Destination to an empty cell such as J2, and select Finish.

Pick a destination with enough empty columns so the split does not overwrite other data. Splitting on the comma alone leaves a leading space on Seattle and WA 98101; clean those with TRIM afterward.

Flash Fill

1. In an empty column, for example J2, type the result you want for the first row, such as Megan, and press Enter.
2. Start typing the next first name in J3. Excel previews the rest of the column.
3. Press Enter to accept. You can also run it with Data > Flash Fill or Ctrl+E.

If no preview appears, Microsoft's page says to turn on Automatically Flash Fill under Tools > Options > Advanced > Editing Options; the location of Options varies by Excel version. Flash Fill and Text to Columns write values, not formulas, so run them again when the source data changes. For a wider look at Flash Fill, TRIM and AI-assisted cleanup, see cleaning messy spreadsheet data with AI.

For a formula approach in any version, Microsoft documents LEFT, RIGHT and SEARCH patterns. The last name in A2 is:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)) 

Result: Bowen. For the first name without the trailing space, subtract 1 from the position of the space:

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

Result: Megan.

Common errors and fixes

Three more points:

• #NAME? Microsoft says this error appears when Excel does not recognize text in a formula, such as a misspelled TEXTSPLT or a function your version lacks. It advises against hiding the error with IFERROR.
• Limits. A cell holds at most 32,767 characters and a formula at most 8,192, according to Excel's specifications and limits.
• Hidden spaces. TRIM does not remove the nonbreaking space (character 160) that often comes with text copied from web pages, so a delimiter that looks right may still not match.

More Excel and Microsoft 365 how-tos are on the More Microsoft 365 tutorials page.

Checklist

• Confirm your version: TEXTSPLIT, TEXTBEFORE and TEXTAFTER need Excel for Microsoft 365 or Excel 2024; TEXTJOIN needs Excel 2019 or later.
• Include the space in the delimiter (", ") or wrap TEXTSPLIT in TRIM.
• Use {",",";"} for several delimiters and TRUE for ignore_empty when delimiters can repeat.
• Leave room for the spill range, and keep TEXTSPLIT formulas outside Excel tables.
• Use a negative instance_num in TEXTAFTER to get the last word.
• Use TEXTJOIN, not CONCAT, when you need a separator or want to skip blanks.
• Wrap FILTER inside TEXTJOIN for a conditional list, and give FILTER an if_empty value.
• Keep joined results under 32,767 characters.
• In older Excel, use Text to Columns or Flash Fill, and rerun them when the data changes.

References

• TEXTSPLIT function
• TEXTJOIN function
• TEXTBEFORE function
• TEXTAFTER function
• CONCAT function
• CONCATENATE function
• FILTER function
• TRIM function
• Split text into different columns with the Convert Text to Columns Wizard
• Using Flash Fill in Excel
• Split text into different columns with functions
• How to correct a #SPILL! error
• How to correct a #NAME? error
• Dynamic array formulas and spilled array behavior
• Guidelines and examples of array formulas
• Excel specifications and limits

This guide was researched and drafted with AI assistance and checked against the vendor documentation listed in its References. Read the maintained version and related guides on EpiFive.