How to Use TEXTSPLIT Function in Excel - Practical Guide

Learn to use TEXTSPLIT to break text strings into separate cells based on delimiters. This powerful function automatically splits data like names, addresses, or CSV content into columns or rows, eliminating manual parsing and saving hours on data cleaning tasks.

Why This Matters

TEXTSPLIT automates data splitting tasks that typically require complex formulas or manual effort, dramatically improving productivity with messy or imported datasets.

Prerequisites

•Basic understanding of Excel formulas and cell references
•Excel 365 or Excel 2024 (TEXTSPLIT available in these versions)

Step-by-Step Instructions

Enter your source text in a cell

Place the text string you want to split in a cell, such as 'John Smith' in cell A1, or reference a range containing multiple entries to split.

Click the destination cell for split results

Select the cell where you want the first split value to appear, typically to the right or below your source data to avoid overwriting.

Type the TEXTSPLIT formula with delimiter

Enter =TEXTSPLIT(A1," ") to split by space, or =TEXTSPLIT(A1,",") for comma-separated values; replace the delimiter as needed for your data.

Specify optional parameters for advanced splitting

Add a third parameter like TRUE to ignore consecutive delimiters: =TEXTSPLIT(A1," ",TRUE) or use row_col parameter (0=columns, 1=rows) for output direction.

Press Enter to execute and auto-populate results

The formula automatically fills adjacent cells with split values; the spilled range shows all results without copying the formula down.

Alternative Methods

Use Data > Text to Columns

Select data range, go to Data menu > Text to Columns, choose delimiter type, and finish. This permanently modifies cells but works for older Excel versions.

Combine with FILTERXML or REGEX

For advanced splitting with patterns, use FILTERXML or newer REGEX functions to extract specific text segments based on complex rules.

Tips & Tricks

✓Use TEXTSPLIT with multiple delimiters by nesting the function: =TEXTSPLIT(TEXTSPLIT(A1,",")," ") to split by both comma and space.
✓Combine TEXTSPLIT with TRIM to automatically remove extra spaces around split values for cleaner results.
✓Test your formula on a small dataset first to ensure the delimiter correctly identifies split points in your data.

Pro Tips

★Use the row_col parameter to output split results vertically (1) instead of horizontally (0) for narrow columns or stacked data layouts.
★Combine TEXTSPLIT with BYROW to apply splitting across multiple rows efficiently without manually adjusting cell references.
★Leverage spilled ranges in dependent formulas: =UPPER(TEXTSPLIT(A1," ")) automatically applies UPPER to each split segment.

Troubleshooting

Formula returns #NAME? error

This indicates TEXTSPLIT is not recognized in your Excel version. Upgrade to Excel 365 or 2024, or use Text to Columns as an alternative.

Split values not appearing in adjacent cells

Ensure the destination cell and surrounding cells are not protected or merged. Clear any blocking objects and re-enter the formula.

Delimiter not splitting text correctly

Verify the exact delimiter character in your data (spaces, tabs, commas) match the one in your formula; use Find & Replace to check hidden characters.

Extra spaces or empty cells in results

Add TRUE as the third parameter to ignore consecutive delimiters: =TEXTSPLIT(A1," ",TRUE) or nest TRIM inside the formula.

Related Excel Formulas

FILTERXML REGEX MID FIND TRIM SUBSTITUTE BYROW

Frequently Asked Questions

Does TEXTSPLIT work in Excel 2019 or older?

No, TEXTSPLIT is only available in Excel 365 and Excel 2024. For older versions, use Data > Text to Columns or create a formula with MID, FIND, and other text functions.

Can I split text into rows instead of columns?

Yes, use the row_col parameter set to 1: =TEXTSPLIT(A1," ",,1) outputs results vertically, or 0 for horizontal columns (default).

What if my data has multiple different delimiters?

Nest TEXTSPLIT functions to handle multiple delimiters sequentially, or use SUBSTITUTE to replace all delimiters with a single character before splitting.

How do I handle empty cells in the split results?

Set the third parameter to TRUE to skip consecutive delimiters: =TEXTSPLIT(A1," ",TRUE) prevents empty cells from appearing in your output.

Can TEXTSPLIT work with dynamic ranges?

Yes, reference entire columns or ranges: =TEXTSPLIT(A:A," ") splits all text in column A simultaneously using spilled ranges.