Excel: Split string by delimiter or pattern, separate text and numbers

This tutorial demonstrates how to efficiently split cells in Excel using both formulas and the advanced "Split Text" feature. Learn to separate text using commas, spaces, or any delimiter, and to split alphanumeric strings into their text and number components.

Splitting cell contents across multiple cells is a common Excel task. Previous articles covered the "Text to Columns" and "Flash Fill" methods. This tutorial focuses on formula-based solutions and the powerful "Split Text" tool.

• Formula-Based Cell Splitting:
  • Separating text by comma, colon, slash, dash, or other delimiters.
  • Splitting text by line breaks.
  • Formulas for separating text and numbers.
• Using the "Split Text" Tool:
  • Splitting cells by character.
  • Splitting cells by string.
  • Splitting cells by mask (pattern).

Splitting Text with Excel Formulas

Excel formula-based string splitting typically involves LEFT, RIGHT, MID, FIND, and SEARCH functions. While formulas might seem complex initially, the underlying logic is straightforward.

Splitting by Delimiter (Comma, Semicolon, etc.)

The core is identifying the delimiter's position using SEARCH (case-insensitive) or FIND (case-sensitive). Then, LEFT, RIGHT, or MID extract the relevant text segments.

Consider splitting "Item-Color-Size" SKUs into three columns:

1. Item Name (before the first hyphen): =LEFT(A2, SEARCH("-",A2,1)-1)
2. Color (between hyphens): =MID(A2, SEARCH("-",A2) 1, SEARCH("-",A2,SEARCH("-",A2) 1) - SEARCH("-",A2) - 1)
3. Size (after the last hyphen): =RIGHT(A2,LEN(A2) - SEARCH("-", A2, SEARCH("-", A2) 1))

Replace the hyphen ("-") with your desired delimiter (space, comma, etc.). Adjust " 1" and "-1" if your delimiter has more than one character.

Splitting by Line Break

Use CHAR(10) (line feed) to represent line breaks in formulas. Adapt the above formulas, replacing hyphens with CHAR(10).

Splitting Text and Numbers

There's no universal solution for all alphanumeric strings. The optimal formula depends on the specific pattern.

'Text Number' Pattern:

Method 1: Count digits and extract. Use RIGHT to extract numbers and LEFT to extract text based on digit count.

Method 2: Find the first digit's position using MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")). Then, use LEFT and RIGHT for extraction.

'Number Text' Pattern:

Use LEFT to extract numbers (counting digits as above) and RIGHT to extract text.

Splitting Cells with the "Split Text" Tool

The "Split Text" tool (part of a suite like Ultimate Suite for Excel) offers a visual approach:

Splitting by Character: Splits at each occurrence of a specified character.

Splitting by String: Splits using a combination of characters as delimiters.

Splitting by Mask (Pattern): Splits based on a pattern, useful for complex scenarios.

The "Split Text" tool significantly simplifies complex splitting tasks compared to formula-based methods.

Downloads:

Excel Split Cells formulas (.xlsx) Ultimate Suite trial (.exe)

The above is the detailed content of Excel: Split string by delimiter or pattern, separate text and numbers. For more information, please follow other related articles on the PHP Chinese website!