TEXTSPLIT function in Excel: split text strings by delimiter

This tutorial demonstrates the powerful new TEXTSPLIT function in Excel 365, enabling efficient string splitting using custom delimiters. Excel offers various cell-splitting methods, including Text to Columns and Fill Flash, but TEXTSPLIT provides a dedicated, dynamic solution.

TEXTSPLIT divides strings into multiple cells (columns and/or rows) based on specified parameters. It accepts up to six arguments, with only the first two mandatory.

TEXTSPLIT Function Syntax:

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

• text: (Required) The string to split (can be a string or cell reference).
• col_delimiter: (Required) Character(s) defining column splits. If omitted, row_delimiter is required.
• row_delimiter: (Optional) Character(s) defining row splits.
• ignore_empty: (Optional) FALSE (default) includes empty cells for consecutive delimiters; TRUE ignores them.
• match_mode: (Optional) 0 (default) is case-sensitive; 1 is case-insensitive.
• pad_with: (Optional) Replaces missing values in 2D arrays (default is #N/A).

Basic Usage:

• Horizontal Split (Columns): =TEXTSPLIT(A2, ",") splits the string in A2 by commas.
• Vertical Split (Rows): =TEXTSPLIT(A2, ,",") splits A2 by commas across rows.

Advanced Applications:

• Substring Delimiters: Use a substring like ", " to split by comma and space: =TEXTSPLIT(A2, ", ")

• 2D Array Splitting: Define both col_delimiter and row_delimiter for splitting across both dimensions. For example: =TEXTSPLIT(A2, "=", ", ")

• Multiple Delimiters: Use an array constant like {"x","y","z"} for the delimiter argument to handle various separators.

• Ignoring Empty Values: Set ignore_empty to TRUE to skip empty entries between delimiters.

• Case Sensitivity: match_mode controls case sensitivity (0: case-sensitive, 1: case-insensitive).

• Padding Missing Values: Use pad_with to replace #N/A errors in 2D arrays with a custom value (e.g., =TEXTSPLIT(A2, "=", ", ", , ,"-")).

• Date Splitting: Convert dates to text using TEXT before applying TEXTSPLIT.

• Removing Characters: Nest SUBSTITUTE functions to remove unwanted characters after splitting.

• Skipping Values: Include specific strings in the delimiter array to skip them during splitting.

Alternatives (for non-Excel 365 versions): Use combinations of SEARCH/FIND, LEFT, RIGHT, and MID functions.

This comprehensive guide illustrates the versatility of TEXTSPLIT and provides alternatives for older Excel versions. A practice workbook is available for download.

The above is the detailed content of TEXTSPLIT function in Excel: split text strings by delimiter. For more information, please follow other related articles on the PHP Chinese website!