How to Split a String by Length in Excel - 8 Methods

This is an overview.

The dataset showcases students’ records.

Method 1 – Using the Flash Fill Feature to Split a String by Length

Student Id contains University name, Year, Section, and Roll.

Extract the data by splitting the Student Id : by character length 3, by length 4, and by character length 3.

Step 1:

Select the output Cell, C5.
Enter the first three characters.

Step 2:

Press ENTER.
In the next Cell, C6, go to Data Tab >> Data Tools >>Flash Fill.

Result:
You will get the University name in the University column.

Step 3:

Select the output Cell, D5.
Enter the middle four characters.

Follow Step 2.

Result:
You will get the Year in the Year column.

Step 4:

Select the output Cell E5.
Enter the last three characters.

Follow Step 2.

Result:
You will get the Section and Roll of each student in the Section and Roll column.

Method 2 – Using the Text to Columns Option

Split Student Id to get the University name, Year, Section, and Roll of each student.

Step 1:

Select the Student Id column.
Go to Data Tab >> Data Tools >>Text to Columns

In the Convert Text to Columns Wizard:

Select Fixed width.
Click Next.

Click the position shown below to create a line break. (Here, after University name and after Year)
Select Next.

Select General as Column data format.
Enter the output cell $C$5 in Destination.
Click Finish.

Result:
You will get the split data in the three columns: University, Year, Section, and Roll.

Method 3 – Using Helper Data

Split the Course No by character length 3 to get the Subject and Course Id.

Step 1:

Select the output Cell, D6.
Enter the following formula.

=MID($C6,COLUMN()+D$4-COLUMN($D6)+1,3)

Formula Breakdown

COLUMN()+D$4-COLUMN($D6)+1 will return the start number
COLUMN($D6) produces the column number of this cell.
3 is the number of characters

Press ENTER.
Drag down the Fill Handle Tool.

Result:
You will get the Subjects in the Subject column.

Step 2:

Select the output Cell, D6.
Enter the following formula.

=MID($C6,COLUMN()+E$4-COLUMN($E6)+3,3)

COLUMN()+E$4-COLUMN($E6)+3 will return the start number
COLUMN($E6) produces the column number of this cell.
3 is the number of characters.

Press ENTER.
Drag down the Fill Handle Tool.

Result:
You will see the Course Id in the Course Id column.

Method 4 – Using LEFT and RIGHT Functions

Split the Course No by character length 3 each time, the Subject and Course Id will be separated.

Step 1:

Select the output Cell, C5.
Enter the following formula.

=LEFT(C5,3)

C5 is the text
3 is the number of characters

Press ENTER.
Drag down the Fill Handle Tool.

You will see the Subjects in the Subject column.

Step 2:

Select the output Cell, E5.
Enter the following formula.

=RIGHT(C5,3)

C5 is the text
3 is the number of characters

Press ENTER.
Drag down the Fill Handle Tool.

You will see the Course Id in the Course Id column.

Read More: Split String by Character in Excel

Method 5 – Use the FIND Function to Split a String with a Special Character

Split the Email Ids by special characters “@” and “.” .

Step 1:

Select the output Cell, C5.
Enter the following formula.

=LEFT(B5,FIND("@",B5)-1)

B5 is the text
FIND(“@”,B5)-1 is the number of characters
FIND will give the position of “@” . It will be subtracted from 1 and return the number of characters.

Press ENTER.
Drag down the Fill Handle Tool.

Result:
You will get the first portion of the Email Id

Step 2:

Select the output Cell, C5.
Enter the following formula.

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

B5 is the text
FIND(“@”, B5) is the start number, which is the position of the “@” character
FIND(“.”,B5,FIND(“@”,B5)+1)-FIND(“@”,B5) is the number of characters
It is the difference between the “.” character and the “@” character.

Press ENTER.
Drag down the Fill Handle Tool.

Result:
You will see the middle portion of this Email Id.

Step 3:

Select the output Cell, C5.
Enter the following formula.

=RIGHT(B5,LEN(B5)-FIND(".",B5,FIND("@",B5))+1)

B5 is the text
LEN(B5)-FIND(“.”,B5,FIND(“@”,B5))+1 is the number of characters
the LEN function will give the character length, and it will be subtracted from the position of the “.” character. The difference will be the number of characters after the “.” character.

Press ENTER.
Drag down the Fill Handle Tool.

Result:
You will get the last portion of the Email Id.

Method 6 – Using the SEARCH Function to Split a String with a Special Character

Split the Email Ids by special characters “@” and “.”

Step 1:

Select the output Cell, C5.
Enter the following formula.

=LEFT(B5,SEARCH("@",B5)-1)

Formula Breakdown

B5 is the text
SEARCH(“@”,B5)-1 is the number of characters
SEARCH will give the position of “@” character.
It will be subtracted from 1.

Press ENTER.
Drag down the Fill Handle Tool.

Result:
You will get the first portion of the Email Id.

Step 2:

Select the output Cell, D5.
Enter the following formula.

=MID(B5,SEARCH("@",B5),SEARCH(".",B5,SEARCH("@",B5)+1)-SEARCH("@",B5))

Formula Breakdown

Here, B5 is the text
SEARCH(“@”, B5) is the start number, which is the position of the “@” character
SEARCH(“.”,B5,SEARCH(“@”,B5)+1)-SEARCH(“@”,B5) is the number of characters
It is the difference between the “.” character and the “@” character

Press ENTER.
Drag down the Fill Handle Tool.

You will get the middle portion of the Email Id.

Step 3:

Select the output Cell, E5.
Enter the following formula.

=RIGHT(B5,LEN(B5)-SEARCH(".",B5,SEARCH("@",B5))+1)

B5 is the text
LEN(B5)-SEARCH(“.”,B5,SEARCH(“@”,B5))+1 is the number of characters
Here, the LEN function will give the character length. It will be subtracted from the position of the “.” character, and the difference will be the number of characters after the “.” character.

Press ENTER.
Drag down the Fill Handle Tool.

You will get the last portion of the Email Id.

Method 7 – Split a String by a Line Break

The Student Id and Student Name are separated by a line break. To extract the Student Id and Student Name, split the string by the line break.

Step 1:

Select the output Cell, C5.
Enter the following formula.

=LEFT(B5,FIND(CHAR(10),B5))

B5 is the text
FIND(CHAR(10), B5) is the number of characters
FIND will give the position of line break and CHAR(10) is used for a line break.

Press ENTER.
Drag down the Fill Handle Tool.

Result:
You will get the Student Id in the Student Id column.

Step 2:

Select the output Cell, E5.
Enter the following formula.

=RIGHT(B5,LEN(B5)-FIND(CHAR(10),B5)+1)

B5 is the text
LEN(B5)-FIND(CHAR(10), B5)+1 is the number of characters
Here, the LEN function will give the character length. It will be subtracted from the position of line break and the difference will be the number of characters after line break.

Press ENTER.
Drag down the Fill Handle Tool.

You will get the Student Name in the Student Name column.

Read More: How to Separate Two Words in Excel

Method 8 – Split a String with a Combination of Number and Text

Marks and Grade are displayed in the same cell.

Step 1:

Select the output Cell, D5.
Enter the following formula.

=LEFT(C5, SUM(LEN(C5) - LEN(SUBSTITUTE(C5, {"0","1","2","3","4","5","6","7","8","9"}, ""))))

C5 is the text
SUBSTITUTE(C5, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”) will substitute any number with a Blank and the LEN function will return the number of numerical values.
Then it will be subtracted from the total character length and the sum of this value will be the number of characters.