How to Split String by Length in Excel (8 Ways)

If you are looking for some of the easiest ways to split string by length in Excel, then you are in the right place. So, let’s get started with this article.

Download Workbook

8 Ways to Split String by Length 

I have the following data table of students’ records of a university. So, with this data table, I will explain the ways of splitting a string by length or splitting a string by different characters or line breaks.
Here, I am using Microsoft Excel 365 version for this purpose, you can use any other versions according to your choice.

.

Method-1: Using Flash Fill Feature to Split String by Length

Here, the Student Id consists of University name, Year, Section, and Roll for each student. You can extract these data by splitting the Student Id at first by character length 3, then by length 4, and lastly by character length 3. To do this you can use the Flash Fill feature of Excel.

Step-01:
➤Select the output Cell C5
➤Write down the first three-length characters here.

Step-02:
➤Press ENTER
Then you will be in the next Cell C6
➤Go to Data Tab>>Data Tools Group>>Flash Fill Option.

Result:
Then, you will get the University name in the University column.

Step-03:
➤Select the output Cell D5
➤Write down the middle four-length characters here.

➤Follow Step-02 of this method.

Result:
In this way, you will get the Year in the Year column.

Step-04:
➤Select the output Cell E5
➤Type the last three-length characters here.

➤Follow Step-02 of this method.

Result:
After that, you will get the Section and Roll of each student in the Section and Roll column.

Read more: Splitting Text in Excel Using Flash Fill

Method-2: Using Text to Columns Option

You can split the Student Id to get the University name, Year, Section, and Roll of each student. To do this you can use the Text to Columns option.

Step-01:
➤Select the Student Id column.
➤Go to Data Tab>>Data Tools Group>>Text to Columns Option

Then Convert Text to Columns Wizard will appear.

➤Select Fixed width Option.
➤Click Next

➤Click at the desired position like below to create a break in the line.( I have clicked after University name and after Year)
➤Select Next

➤Select General as Column data format
➤Write down the output cell $C$5 in the Destination Box
➤Click Finish

Result:
Then you will get the split data in the following three columns; University, Year, Section, and Roll.

Method-3: Using Helper Data

If you split the following Course No by character length 3 each time, then you will get the Subject and Course Id. For doing this, here I am using Helper Data which is just the serial number from 0 according to the split data, and also using the MID function.

Step-01:
➤Select the output Cell D6
➤Type the following formula

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:
After that, you will get the Subjects in the Subject column.

Step-02:
➤Select the output Cell E6
➤Type 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:
In this way, you will get the Course Id in the Course Id column.

Method-4: Using LEFT and RIGHT function

Here, I will split the following Course No by character length 3 each time, the Subject and Course Id will be separated. To do this, I am using the LEFT function and the Right function.

Step-01:
➤Select the output Cell D5
➤Type the following formula

C5 is the text
3 is the number of characters

➤Press ENTER
➤Drag down the Fill Handle Tool

Result:
After that, you will get the Subjects in the Subject column.

Step-02:
➤Select the output Cell E5
➤Type the following formula

C5 is the text
3 is the number of characters

➤Press ENTER
➤Drag down the Fill Handle Tool

Result:
In this way, you will get the Course Id in the Course Id column.

Similar Readings:

• Split String by Character in Excel (6 suitable Ways)
• How to split text into multiple cells in Excel

Method-5: Using FIND function to Split String at Special Character

Let’s say, you want to split the following Email Ids by special characters “@” and “.” and so you will get three portions of this Email Id. You can do this by using the FIND function, the LEFT function, the RIGHT function, the MID function.

Step-01:
➤Select the output Cell C5
➤Type the following formula

B5 is the text
FIND("@",B5)-1 is the number of characters
FIND will give the position of “@” character. After that, it will be subtracted from 1 and denote the number of characters.

➤Press ENTER
➤Drag down the Fill Handle Tool

Result:
Then, you will get the first portion of this Email Id

Step-02:
➤Select the output Cell C5
➤Type 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:
In this way, you will get the middle portion of this Email Id.

Step-03:
➤Select the output Cell E5
➤Type the following formula

B5 is the text
LEN(B5)-FIND(".",B5,FIND("@",B5))+1 is the number of characters
Here, the LEN function will give the character length, and then 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

Result:
Afterward, you will get the last portion of this Email Id.

Method-6: Using SEARCH function to Split String at Special Character

You can split the following Email Ids by special characters “@” and “.” and so you will get three portions of this Email Id. It can be done by using the SEARCH function, the LEFT function, the RIGHT function, and the MID function.

Step-01:
➤Select the output Cell C5
➤Type the following formula

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

➤Press ENTER
➤Drag down the Fill Handle Tool

Result:
Then, you will get the first portion of this Email Id.

Step-02:
➤Select the output Cell C5
➤Type the following formula

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

Result:
In this way, you will get the middle portion of this Email Id.

Step-03:
➤Select the output Cell E5
➤Type 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, and then 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

Result:
Afterward, you will get the last portion of this Email Id.

Method-7: Split String by Line Break

In the Student Id & Name column, the Student Id and Student Name are separated by a line break. If you want to extract the Student Id and Student Name, then you have to split the string by line break. You can use the FIND function here.

Step-01:
➤Select the output Cell C5
➤Type 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:
Then, you will get the Student Id in the Student Id column.

Step-02:
➤Select the output Cell E5
➤Type 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, and then 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

Result:
In this way, you will get the Student Name in the Student Name column.

Method-8: Split a String of Combination of Number and Text

Here, in the Marks and Grade column, the Marks and Grade of a student are gathered together and so it is a combination of Number and Text. You can split them by using the SUBSTITUTE function, the LEFT function, the RIGHT function, the SUM function, and the LEN function.

Step-01:
➤Select the output Cell D5
➤Type 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 using the LEN function after that will give you 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

Result:
Then, you will get the Marks in the Marks column.

Step-02:
➤Select the output Cell E5
➤Type the following formula

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

➤Press ENTER
➤Drag down the Fill Handle Tool

Result:
Afterward, you will get the Grade in the Grade column.

Practice Section

For doing practice by yourself we have provided a Practice section like below on each sheet on the right side. Please do it by yourself.

Conclusion

In this article, I tried to cover the easiest ways to split string by length effectively. Hope you will find it useful.If you have any suggestions or questions feel free to share them with us.

Further Readings

• How to Separate Words in Excel Using Formula (Ultimate Guide)
• Separate Two Words in Excel (6 Easy Ways)