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

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.

Split String by length.

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.

Split String by length

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

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.

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.

flash fill feature

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

Split String by length

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

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.

Split String by length

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

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

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

helper data

➤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

helper data

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

Split String by length

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.

Split String by length

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

=LEFT(C5,3)

C5 is the text
3 is the number of characters

using LEFT and RIGHT function

➤Press ENTER
➤Drag down the Fill Handle Tool

using LEFT and RIGHT function

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

Split String by length

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

=RIGHT(C5,3)

C5 is the text
3 is the number of characters

using LEFT and RIGHT function

➤Press ENTER
➤Drag down the Fill Handle Tool

using LEFT and RIGHT function

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

Split String by length


Further Readings:


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.

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

using 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

Using 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

Using FIND function

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

Split String by length

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.

Split String by length

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

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

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

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

Using SEARCH 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)-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
➤Drag down the Fill Handle Tool

Using SEARCH function

Result:
Afterward, 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

Split by 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

Split by line break

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

Split String by length

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.

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

Split Number+Text String

Result:
Then, 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

Split Number+Text String

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

Split String by length

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.

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

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