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

Get FREE Advanced Excel Exercises with Solutions!

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


Split Text in Excel by Character: 5 Quick Methods

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


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 B5.
  • FIND(“@”,B5) looks for @ within cell B5.
  • 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


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 Text in Excel into Multiple Rows

 


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.


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


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.


Download Practice Workbook

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


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.


Related Articles


<< Go Back to Splitting TextSplit in ExcelLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo