In this article, you will learn** 5** methods to **split a text** in Excel by a **character **step-by-step.

**Table of Contents**hide

## Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

## 5 Methods to Split Text in Excel by Character

### 1. Split Text in Excel by Character with Convert Text to Columns Wizard

To **split a text by a character **using the** Convert Text to Columns Wizard,**

❶ Select your data and then

❷ Go to **Data **➤ **Data Tools **➤ **Text to Columns.**

The **Convert Text to Columns Wizard** will appear.

❸ Select **Delimited **and hit **Next**.

❹ Select **Comma **and **Other (**Insert** @** in the** Other **box**) **as **Delimiters **and hit **Next **again.

❺ Insert a cell address as **Destination **and hit **Finish**.

This will **split a text **in the place of **semicolon **and **@ **into three columns.

**Read More: ****Split String by Character in Excel (6 suitable Ways)**

### 2. Combining LEFT, RIGHT, MID, FIND, and LEN Functions to Split Text in Excel by Character

You can use three formulas using the **LEFT**, **RIGHT**, **MID**, **FIND**, and **LEN **function to **split a text.**

❶ At first insert the following formula in cell **C5**.

`=LEFT(B5,FIND(";",B5)-1)`

❷ Then press **ENTER**.

**Formula Breakdown**

**B5**has texts to**split**.**FIND(“;”,B5)**looks for a**semicolon**within cell**LEFT(B5,FIND(“;”,B5)-1)**returns texts before the first**semicolon**appears from the left side.

❸ After that insert the following formula in cell **D5**.

`=MID(B5,FIND(";",B5)+1,FIND("@",B5)-FIND(";",B5)-1)`

❹ Then hit **ENTER **again.

**Formula Breakdown**

**B5**has texts to**split**.**FIND(“;”,B5)**looks for a**semicolon**within cell**FIND(“@”,B5)**looks for**@**within cell**MID(B5,FIND(“;”,B5)+1,FIND(“@”,B5)-FIND(“;”,B5)-1)**returns texts between a**semicolon**and**@.**

❺ After that insert the following formula in cell **E5**.

`=RIGHT(B5,LEN(B5)-FIND("@",B5))`

❻ Then hit **ENTER **again.

**Formula Breakdown**

**B5**has texts with a comma.**FIND(“@”,B5)**looks for**@**within cell**=RIGHT(B5,LEN(B5)-FIND(“@”,B5))**returns texts after the**@**appears from the right side.

❼ Select cells **C5, D5, **and **E5 **and drag the** Fill Handle** icon up to cells **C12**,** D12, **and **E12**.

These three formulas will **split a text **in place of a **semicolon and @ **into three columns.

**Read More: ****How to Split Text in Excel Using Formula (5 Easy Ways)**

**Similar Readings**

**How to Split Text in Excel into Multiple Rows (6 Quick Tricks)****Split Text after a Certain Word in Excel (7 Ways)****How to Split Text by Space with Formula in Excel (5 Ways)**

### 3. Apply Dynamic Array Formula to Split Text in Excel by Character

The **dynamic array formula** used in this method can automatically **split a text** having **semicolon **and **@ **into three columns.

To use it,

❶ Insert the following formula in cell **C5**.

`=TRANSPOSE(FILTERXML("<s>" &SUBSTITUTE(SUBSTITUTE(B5,";", "</s><s>"),"@","</s><s>") & "</s>","//s"))`

❷ Then press** ENTER**.

The formula is an array formula, it will automatically keep **split **texts in cell **B5**, nevertheless, the formula was applied in cell **C5**.

**Formula Breakdown**

**SUBSTITUTE(B5,”;”,”</s><s>”)**

**The SUBSTITUTE function** substitutes the semicolon in cell **B5 **with a space.

**SUBSTITUTE(SUBSTITUTE(B5,”;”,”</s><s>”),”@”,”</s><s>”)**

**The outer SUBSTITUTE function** substitutes **@ **in cell **B5 **with a space.

**FILTERXML(“<t><s>” &SUBSTITUTE(B5,”,”,”</s><s>”)**

**The FILTERXML function** filters out data separated by spaces.

**TRANSPOSE(FILTERXML(“<t><s>” &SUBSTITUTE(SUBSTITUTE(B5,”;”,”</s><s>”),”@”,”</s><s>”) & “</s></t>”,”//s”))**

**The TRANSPOSE function** splits the data in cell **B5 **into three different columns.

❸ Drag the** Fill Handle** icon from cell **C5 **to **C12**.

Now you will see the **split **texts into three different columns.

**Read More: ****How to Split a String into an Array in VBA (3 Ways)**

### 4. Divide Text in Excel by Character Using Flash Fill

You can use the** Flash Fill feature to divide texts** by a character easily.

❶ Start inserting data before the semicolon encounters in the **Name **column.

❷ After inserting data in two consequent cells, Excel will show suggestions. Press **ENTER **to accept.

❸ Now start inserting data between the **semicolon** and **@ **in the column **Country**.

❹ After inserting data in two consequent cells, Excel will show suggestions. Press **ENTER **to accept again.

❺ Finally, start inserting data after **@ **in the column **City**.

❻ After inserting data in two consequent cells, Excel will show suggestions. Press **ENTER **to accept again.

Now you will see the **split **texts into three different columns.

**Read More: ****How to split text into multiple cells in Excel**

### 5. Split Text in Excel by Character Using Power Query

Follow the steps below to **split a text by a character **in Excel using the **Power Query.**

❶ Go to **Data **➤ **Get Data** ➤ **From File** ➤ **From Excel Workbook.**

❷ From the **Navigator **window, select your** worksheet name** having the texts to **split**.

❸ Then click on **Transform Data.**

❹ Now go to **Transform **➤ **Split Column** ➤** By Delimiter.**

The** Split Column by Delimiter** dialog box will appear.

❺ Select **Custom **from the **Select or enter delimiter** drop-down. Then **insert @ **in the **Custom **box.

❻ Then hit **OK**.

Now you will see the **split **texts into three different columns.

**Read More: ****How to Separate Two Words in Excel (6 Easy Ways)**

## Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the methods discussed in this article.

## Conclusion

To sum up, we have discussed **5 methods to split text** in Excel **by a character**. You are recommended to download the practice workbook attached with this article and practice all the methods. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website **Exceldemy** to explore more.