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

Get FREE Advanced Excel Exercises with Solutions!

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.

Before going to the next part, let’s have a quick look o the methods we are going to use and relevant outputs.

Overview of Excel Split String by Length


Split String by Length in Excel: 8 Quick Ways

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.

Sample DatasetHere, I am using Microsoft Excel 365 version for this purpose, you can use any other versions according to your choice.


Method-1: Use 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.

Data for Using Flash Fill

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

Type to Split String by length

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

flash fill feature

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

flash fill feature

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

Type to Use flash fill feature

➤ Follow Step-02 of this method.

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

flash fill feature

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

Type to Use Flash Fill

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

Split String by length

Read More: How to Split Text in Excel by Character


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.

Data for Using Text to Column feature

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

text to columns option

Then Convert Text to Columns Wizard will appear.

➤ Select Fixed width Option.
➤ Click Next

text to columns option

➤ 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

text to columns option

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

text to columns option

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

Split String by length in Excel

Read More: How to Split Text in Excel Using Formula


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.

Sample Data

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

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

helper data

 💡  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

helper data

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

Split String by length

Step-02:
➤ Select the output Cell E6
➤ Type 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.

helper data

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Use Helper Data

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

Split String by length

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


Method-4: Using LEFT and RIGHT Functions

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

=LEFT(C5,3)

C5 is the text
3 is the number of characters

Use of LEFT and RIGHT Functions

➤ Press ENTER
➤ Drag down the Fill Handle Tool and you will get the Subjects in the Subject column.

LEFT Function

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

=RIGHT(C5,3)

C5 is the text
3 is the number of characters

RIGHT Function to Split String by Length

➤ Press ENTER
➤ Drag down the Fill Handle Tool and you will get the Course Id in the Course Id column.

RIGHT Function in Excel

Read More: Split String by Character in Excel


Method-5: Use 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.

Split String by length

Step-01:
➤ Select the output Cell C5
➤ Type 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 “@” character. After that, it will be subtracted from 1 and denote the number of characters.

using FIND function

➤ Press ENTER
➤ Drag down the Fill Handle Tool

FIND Function

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

using FIND function

Step-02:
➤ Select the output Cell C5
➤ Type 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.

Using FIND function

➤ Press ENTER
➤ Drag down the Fill Handle Tool

FIND Function

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

Split String by length

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

Using FIND function

➤ Press ENTER
➤ Drag down the Fill Handle Tool.

FIND Function

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

Split String by length


Method-6: Use 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 SEARCH, LEFT, RIGHT, and MID functions.

Split String by length

Step-01:
➤ Select the output Cell C5
➤ Type 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.
After that, it will be subtracted from 1.

Using SEARCH function

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Using SEARCH function

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

Using SEARCH function

Step-02:
➤ Select the output Cell C5
➤ Type 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

Using SEARCH function

➤ Press ENTER
➤ Drag down the Fill Handle Tool and you will get the middle portion of this Email Id.

Split String by length

Step-03:
➤ Select the output Cell E5
➤ Type 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, and then it will be subtracted from the position of the “.” character, and the difference will be the number of characters after the “.” character.

Using SEARCH function

➤ Press ENTER
➤ Afterward drag down the Fill Handle Tool and you will get the last portion of this Email Id.

Split String by length


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.

Split String by length

Step-01:
➤ Select the output Cell C5
➤ Type 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.

Split by line break

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Line Break

Result:
Then, you will get the Student Id in the Student Id column.

Split by line break

Step-02:
➤ Select the output Cell E5
➤ Type 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, and then it will be subtracted from the position of line break and the difference will be the number of characters after line break.

Split by line break

➤ Press ENTER
➤ Drag down the Fill Handle Tool and you will get the Student Name in the Student Name column.

Split String by length

So, that’s the way to split string by length in Excel.

Read More: How to Separate Two Words in Excel


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 SUBSTITUTE, LEFT, RIGHT, SUM, and LEN functions.

Split String by length

Step-01:
➤ Select the output Cell D5
➤ Type 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 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.

Split Number+Text String

➤ Press ENTER
➤ Drag down the Fill Handle Tool and you will get the Marks in the Marks column.

Split String by length

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

=RIGHT(C5,LEN(C5)-LEN(D5)-1)

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

Split Number+Text String

➤ Press ENTER
➤ Drag down the Fill Handle Tool and you will get the Grade in the Grade column.

Split String by length

So, you are succeed to split string by length in Excel.


Practice Section

For doing practice by yourself we have provided a Practice section like below on each sheet on the right side. Please try on Excel to split string by length yourself.

Practice


Download Practice Workbook


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


<< Go Back to Splitting TextSplit in ExcelLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo