How to How to Split Text with Multiple Delimiters in Excel in Excel - Practical Guide

Learn to split text using multiple delimiters in Excel through formulas like TEXTSPLIT, SUBSTITUTE, and FILTERXML. This skill handles complex data parsing scenarios where commas, spaces, or special characters separate values, eliminating manual sorting and enabling automated data organization for cleaner analysis.

Why This Matters

Splitting text with multiple delimiters automates data cleaning and organization, saving hours on manual entry and reducing errors in data preparation workflows.

Prerequisites

•Basic understanding of Excel formulas and cell references
•Familiarity with common delimiters (comma, space, semicolon, pipe)
•Knowledge of nested functions and array formulas

Step-by-Step Instructions

Prepare Your Data

Place text containing multiple delimiters in a single column (e.g., Column A). Identify all delimiter types present in your data (e.g., commas and spaces in 'John, Smith | Engineer').

Use TEXTSPLIT Function (Excel 365+)

Enter formula =TEXTSPLIT(A1,{" ",",","|"},TRUE) in cell B1 to split by multiple delimiters simultaneously. The TRUE parameter trims spaces; adjust delimiters in curly braces as needed.

Apply SUBSTITUTE for Normalization

For older Excel versions, use nested SUBSTITUTE formulas: =SUBSTITUTE(SUBSTITUTE(A1,",","|")," ","|") to convert all delimiters to a single type before splitting with Text-to-Columns.

Use Text-to-Columns Feature

Select normalized data, navigate to Data > Text to Columns > Choose Delimited option, check all delimiter types (comma, space, semicolon), and click Finish to populate adjacent columns.

Verify and Clean Results

Review split columns for empty cells or incomplete data; use TRIM function to remove extra spaces (=TRIM(B1)) and adjust formulas as needed for consistency across your dataset.

Alternative Methods

FILTERXML with SUBSTITUTE

Combine SUBSTITUTE to convert delimiters into XML tags, then use FILTERXML to extract values: =FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s"). This works in Excel 2013+.

Regular Expression (REGEX) via VBA

Use a custom VBA function with regular expressions for complex multi-delimiter scenarios. Access VBA via Developer > Visual Basic > Insert Module and define a custom REGEX function.

Power Query Import

Load data into Power Query (Data > Get Data > From Text/CSV), set delimiters in preview, and let Power Query automatically handle multiple delimiters before loading back to Excel.

Tips & Tricks

✓Always use TRIM() after splitting to remove unwanted leading/trailing spaces from extracted values.
✓Test your formula on a few rows before applying to entire dataset to catch delimiter inconsistencies.
✓Use consistent delimiter ordering in your formula (e.g., longest delimiters first with SUBSTITUTE) to avoid partial replacements.
✓Preview results with a small sample dataset to ensure delimiters are recognized correctly.

Pro Tips

★For maximum compatibility, use TEXTSPLIT (Excel 365) which handles all delimiters simultaneously without nested functions.
★Combine TEXTSPLIT with TOCOL/TOROW to pivot split results into single column/row format for downstream processing.
★Use Find & Replace (Ctrl+H) to visually identify all delimiter types before writing formulas, ensuring comprehensive coverage.
★Cache normalized data in a helper column before splitting to preserve original values and enable auditing.

Troubleshooting

TEXTSPLIT returns #NAME? error

This function is only available in Excel 365 and Excel 2021+. Use FILTERXML with SUBSTITUTE for older versions, or upgrade your Excel subscription.

Split data appears in only one cell instead of multiple columns

Ensure your Excel version supports spill ranges (Excel 365+). For older versions, manually adjust formula to extract individual columns using MID, FIND, or TEXT functions.

Extra blank cells appear between split values

Use TEXTSPLIT with TRUE parameter to remove empty items, or apply FILTER(array,array<>"") to eliminate blanks after splitting with alternative methods.

Delimiters in the data content aren't distinguished from delimiters meant for splitting

Escape embedded delimiters before splitting by surrounding them with unique markers, then restore original characters after splitting.

Related Excel Formulas

TEXTSPLIT SUBSTITUTE FILTERXML TRIM MID FIND FILTER TOCOL TOROW

Frequently Asked Questions

What is the best function for splitting text with multiple delimiters in Excel 365?

TEXTSPLIT is the best choice for Excel 365, as it handles multiple delimiters in a single formula without nesting. Simply specify all delimiters in curly braces: =TEXTSPLIT(A1,{","," ","|"},TRUE).

Can I split text by multiple delimiters in Excel 2019 or older versions?

Yes, use FILTERXML combined with nested SUBSTITUTE functions to normalize delimiters before extraction. Alternatively, use Text-to-Columns feature after converting all delimiters to one type.

How do I remove spaces after splitting with multiple delimiters?

Apply TRIM function to each split result: =TRIM(B1). For dynamic arrays in Excel 365, use =TRIM(TEXTSPLIT(A1,{" ",",","|"},TRUE)) to trim all results at once.

What if my delimiters are inconsistent across rows?

Normalize delimiters first using nested SUBSTITUTE formulas to convert all variations to a single delimiter, then split. For example: =SUBSTITUTE(SUBSTITUTE(A1,",","|")," ","|").

Can Power Query handle multiple delimiters automatically?

Yes, Power Query detects multiple delimiters during import and shows preview options. You can select all relevant delimiters in the delimiter selection screen before loading data back to Excel.