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. Use the Flash Fill.

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.

Read More: How to Split Text in Excel by Character

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 a position, as shown below, to create a break in the line. (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.

Read More: How to Split Text in Excel Using Formula

Method 3 – Using Helper Data

Split the Course No by character length 3 to get the Subject and Course Id. Use Helper Data and the MID function.

Step 1:

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

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

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.

Read More: How to Split Text by Number of Characters in Excel

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. Use the LEFT function and the Right function.

Step 1:

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

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.

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 “.” . Use the FIND function, the LEFT function, the RIGHT function, the MID function.

Step 1:

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

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.

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.

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 “.” Use the SEARCH, LEFT, RIGHT, and MID functions.

Step 1:

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

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

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

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. Use the FIND function.

Step 1:

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

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.

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

The Marks and Grade of a student are together. Split them by using the SUBSTITUTE, LEFT, RIGHT, SUM, and LEN functions.

Step 1:

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

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.

• Press ENTER.
• Drag down the Fill Handle Tool.

You will get the Marks in the Marks column.

Step 2:

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

C5 is the text
LEN(C5)-LEN(D5)-1 will give the character length.

• Press ENTER.
• Drag down the Fill Handle  and you will see the Grade in the Grade column.

Practice Section

Practice here.

Download Practice Workbook

Further Readings

• How to Split Text in Excel into Multiple Rows
• How to Split Text after a Certain Word in Excel
• Split Text by Space with Formula in Excel
• How to Split First And Last Name in Excel

<< Go Back to Splitting Text | Split in Excel | Learn Excel