How to Split Text in Excel by Character: 5 Quick Methods

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.

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

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.

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

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

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

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.

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

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.

Apply Dynamic Array Formula to Split Text in Excel by Character

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.

Divide Text in Excel by Character Using Flash Fill

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.