Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

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


Download Practice Workbook


8 Ways to Split String by Length in Excel

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 (5 Quick Methods)


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 (5 Easy Ways)


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 (7 Ways)


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: How to Separate Text in Excel (10 Easy Methods)


Similar Readings


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

Read More: Splitting Text in Excel (8 Useful Applications)


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 (6 Easy Ways)


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


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo