Method 1 – Split Text in Excel by Character with the Convert Text to Columns Wizard
- Select your data.
- 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.
Method 2 – Combining LEFT, RIGHT, MID, FIND, and LEN Functions to Split Text in Excel by Character
- Insert the following formula in cell C5.
=LEFT(B5,FIND(";",B5)-1)- 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.
- Insert the following formula in cell D5.
=MID(B5,FIND(";",B5)+1,FIND("@",B5)-FIND(";",B5)-1)- 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 @.
- Insert the following formula in cell E5.
=RIGHT(B5,LEN(B5)-FIND("@",B5))- 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 to cells C12, D12, and E12.
These three formulas will split a text in place of a semicolon and @ into three columns.
Method 3 – Apply a Dynamic Array Formula to Split Text in Excel by Character
- Insert the following formula in cell C5.
=TRANSPOSE(FILTERXML("" &SUBSTITUTE(SUBSTITUTE(B5,";", ""),"@","") & "","//s"))- 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.
You will see the split texts into three different columns.
Method 4 – Divide Text in Excel by Character Using Flash Fill
- Insert data before the semicolon in the first cell of the Name column.
- When you start entering the next row, Excel will show suggestions. Press ENTER to accept.
- Start inserting data between the semicolon and @ in the column Country.
- After the first few entries, Excel will show suggestions. Press ENTER to accept again.
- Start inserting data after @ in the column City.
- After inserting data in two consequent cells, Excel will show suggestions. Press ENTER to accept again.
You will see the split texts into three different columns.
Method 5 – Split Text in Excel by Character Using Power Query
- Go to Data ➤ Get Data ➤ From File ➤ From Excel Workbook.
- From the Navigator window, select your worksheet name.
- Click on Transform Data.
- 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.
- Insert @ in the Custom box.
- Hit OK.
You will see the split texts into three different columns.
- Load the columns into a sheet with Close & Load.
Download the Practice Workbook
Related Articles
- How to Split Text by Number of Characters in Excel
- Split First And Last Name in Excel
- How to Split String by Length in Excel
- Split Text after a Certain Word in Excel
- How to Split Text by Space with Formula in Excel
<< Go Back to Splitting Text |Â Split in Excel |Â Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
