textsplit

 The TEXTSPLIT function in Excel is a powerful tool for splitting text strings into multiple cells based on specified delimiters. Below are 10 examples of how to use the TEXTSPLIT function, including scenarios with data and formulas.

---

1. Basic Text Splitting by Comma

Split a text string by a comma delimiter.

Formula:

excel

Copy

=TEXTSPLIT("Apple,Banana,Cherry", ",")

Result:

Copy

Apple   Banana   Cherry

---

2. Splitting by Multiple Delimiters

Split a text string using both a comma and a space as delimiters.

Formula:

excel

Copy

=TEXTSPLIT("Apple, Banana; Cherry", ",; ")

Result:

Copy

Apple   Banana   Cherry

---

3. Splitting into Rows

Split a text string into rows instead of columns.

Formula:

excel

Copy

=TEXTSPLIT("Apple,Banana,Cherry", ",", , TRUE)

Result:

Copy

Apple
Banana
Cherry

---

4. Splitting with Ignored Empty Values

Split a text string and ignore empty values.

Formula:

excel

Copy

=TEXTSPLIT("Apple,,Banana,,Cherry", ",", , , TRUE)

Result:

Copy

Apple   Banana   Cherry

---

5. Splitting with Row and Column Delimiters

Split a text string using both row and column delimiters.

Formula:

excel

Copy

=TEXTSPLIT("Apple:Banana:Cherry;Orange:Mango:Peach", ":", ";")

Result:

Copy

Apple   Banana   Cherry
Orange  Mango    Peach

---

6. Splitting Text from a Cell

Split text from a specific cell (e.g., A1).

Formula:

excel

Copy

=TEXTSPLIT(A1, ",")

Data in A1:

Copy

Apple,Banana,Cherry

Result:

Copy

Apple   Banana   Cherry

---

7. Combining with Other Functions (e.g., TRIM)

Split text and remove extra spaces using TRIM.

Formula:

excel

Copy

=TRIM(TEXTSPLIT("Apple,  Banana,  Cherry", ","))

Result:

Copy

Apple   Banana   Cherry

---

8. Splitting with a Fixed Number of Columns

Split text into a fixed number of columns.

Formula:

excel

Copy

=TEXTSPLIT("Apple,Banana,Cherry,Date", ",", , , , 2)

Result:

Copy

Apple   Banana   Cherry,Date

---

9. Splitting with Dynamic Delimiters

Use a formula to dynamically determine the delimiter.

Formula:

excel

Copy

=TEXTSPLIT("Apple-Banana-Cherry", LEFT("-;", 1))

Result:

Copy

Apple   Banana   Cherry

---

10. Splitting and Transposing Results

Split text and transpose the results into a vertical list.

Formula:

excel

Copy

=TRANSPOSE(TEXTSPLIT("Apple,Banana,Cherry", ","))

Result:

Copy

Apple
Banana
Cherry

---

Bonus: Handling Errors with IFERROR

Wrap TEXTSPLIT with IFERROR to handle cases where splitting fails.

Formula:

excel

Copy

=IFERROR(TEXTSPLIT("Apple,Banana,Cherry", "|"), "No delimiter found")

Result:

Copy

No delimiter found

---

These examples demonstrate the versatility of the TEXTSPLIT function in Excel. You can adapt these formulas to your specific data and requirements!