How to Split Text in Excel by Character (5 Quick Methods)

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


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.

Split Text in Excel by Character with Convert Text to Columns Wizard

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.

Split Text in Excel by Character with Convert Text to Columns Wizard

❺ Insert a cell address as Destination and hit Finish.

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

Split Text in Excel by Character

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, MIDFIND, 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.

Combining LEFT and FIND Functions to Split Text in Excel by Character

❸ 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 @.

Combining MID and FIND Functions to Split Text in Excel by Character

❺ 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.

Combining RIGHT and FIND Functions to Split Text in Excel by Character

❼ 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


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.

Apply Dynamic Array Formula to Split Text in Excel by Character

❸ 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.

Divide Text in Excel by Character Using Flash Fill

❸ 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 DataFrom FileFrom Excel Workbook.

Split Text in Excel by Character Using Power Query

❷ 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.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo