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.
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 the position shown below to create a line break. (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.
Step 1:
- Select the output Cell, D6.
- Enter the following formula.
=MID($C6,COLUMN()+D$4-COLUMN($D6)+1,3)
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.
Result:
You will get the Subjects in the Subject column.
Step 2:
- Select the output Cell, D6.
- Enter 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.
- 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.
Step 1:
- Select the output Cell, C5.
- Enter the following formula.
=LEFT(C5,3)
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.
=RIGHT(C5,3)
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 “.” .
Step 1:
- Select the output Cell, C5.
- Enter 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 “@” . 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.
=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.
- 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.
=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
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 “.”
Step 1:
- Select the output Cell, C5.
- Enter 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.
It will be subtracted from 1.
- 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.
=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
- 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.
=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. 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.
Step 1:
- Select the output Cell, C5.
- Enter 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.
- 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.
=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. 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
Marks and Grade are displayed in the same cell.
Step 1:
- Select the output Cell, D5.
- Enter 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 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.
=RIGHT(C5,LEN(C5)-LEN(D5)-1)
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
Get FREE Advanced Excel Exercises with Solutions!