Rafiul Haq
Often our texts are in a hard-to-read format. In this article, we’re going to show how to separate text in Excel in 10 easy methods. To demonstrate our solutions, we’ve taken a dataset with the column: “Student Info”. We’ll separate this column into 2 columns.
Download Practice Workbook
10 Ways to Separate Text in Excel
1. Using Flash Fill to Separate Text in Excel
For the first method, we’re going to use the Excel Flash Fill feature to separate text. We’re going to type the first portion of our data and then we’ll use a keyboard shortcut to AutoFill the rest of the cells in a similar format.
Steps:
- Firstly, type the text before the comma in cell C5.
- Secondly, press CTRL + ENTER. This will keep our cursor in cell C5.
- Thirdly, press CTRL + E. This shortcut will activate the Flash Fill feature.
Text in a similar format will be inserted up to cell C10.
- Similarly, type text after the comma in cell D5.
- Finally, press CTRL + E.
Thus, we’ll separate text using the Flash Fill feature.
2. Separate Text in Excel by Applying Text to Columns Wizard
In this method, we’re going to use the Text to Columns Wizard to separate text with a delimiter.
Steps:
- Firstly, select the cell range B5:B10.
- Secondly, from the Data tab >>> select Text to Columns.
This will bring up the Text to Columns Wizard window. Here “Delimited” should be selected by default, if not, select it.
- Thirdly, press Next.
- Then, put a tick mark on “Comma” in the Delimiters option.
- After that, press Next.
- Then, select the Destination for our output as cell C5.
- Finally, press Finish.
This will separate our text in columns C and D. Moreover, the final step should look like this.
Read More: How to Separate Two Words in Excel (6 Easy Ways)
3. Use of Power Query to Separate Text
For the third method, we’re going to use the Power Query to separate text. Here, after loading our data in the Power Query, we’ll use the Split Column feature to segregate our text.
Steps:
- Firstly, select the cell range B5:B10.
- Secondly, from the Data tab >>> select “From Table/Range”.
The Create Table dialog box will appear.
- Thirdly, press OK.
After that, the Power Query Editor window will appear.
- Then, from Split Column >>> select By Delimiter.
A dialog box will appear.
- Select Comma as the delimiter.
- Then, press OK.
After that, we’ll see our original column is divided into 2 columns.
- From Close & Load >>> select “Close & Load To…”.
- Select cell C5 as the output location in the Existing worksheet section.
- Then, press OK.
After that, we’ll be almost near to our goal. However, there is an extra row 5. We’ll remove the row.
We’ll need to change our data table to a data range to remove the row.
- Firstly, select the range B5:B10.
- Secondly, from the Table Design tab >>> select Convert to Range.
- Thirdly, do this again for range C5:D10.
- Then, cut the cell range B6:D11 and paste it into row 5.
Thus, we’ll get rid of that unwanted row and finish our third method to segregate text.
Read More: How to Split Text in Excel by Character (5 Quick Methods)
4. Utilizing a Combined Formula to Separate Text
In this section, we’ll combine the TRIM, MID, SUBSTITUTE, REPT, LEN, and COLUMNS functions to create a formula to separate text.
Steps:
- Firstly, type the following formula in cell C5.
=TRIM(MID(SUBSTITUTE($B5," ",REPT(" ",LEN($B5))),(COLUMNS($B4:B4)-1)*LEN($B5)+1,LEN($B5)))Formula Breakdown
- SUBSTITUTE($B5,” “,REPT(” “,LEN($B5)))
- Output: “Joey ME”.
- The LEN function returns the length of a text. Here, LEN(B5) will return 7.
- The REPT function repeats character. We’re telling it to repeat blank space 7 times.
- Finally, the SUBSTITUTE function will replace the original single-spaced text with 7 spaces.
- COLUMNS($B4:B4)-1)*LEN($B5)+1
- Output: 1.
- Here, we’re defining the start number for the MID function.
- Our formula reduces to -> TRIM(MID(“Joey ME”,1,7))
- Output: “Joey”.
- The MID function returns a number of characters from a text. We’re telling it to return 7 characters starting from position 1.
- Finally, the TRIM function will remove all spaces from the text. Thus, we’re getting our final value.
- Secondly, press ENTER and use the Fill Handle to AutoFill the formula.
- Thirdly, select the cell range C5:C10.
- Finally, use the Fill Handle again to the right side to AutoFill the formula.
In conclusion, we’ve shown you yet another method of separating text in Excel.
Read More: How to Split Text by Space with Formula in Excel (5 Ways)
5. Using VBA Code for Pre Selected Range to Separate Text
In this method, we’ll use a VBA code to separate text. Here, we’ll need to preselect our cell range. After that, we’ll apply the TextToColumns method with space as the delimiter to separate text.
- Firstly, press ALT + F11 to bring up the VBA window.
Alternatively, we can do this from the Developer tab >>> select Visual Basic.
- Secondly, from Insert >>> select Module.
This will bring up the VBA Module window. We’ll write our code here.
- Thirdly, type the following code.
Sub SeparateText()
Dim cRow, cColumn As Integer
cRow = ActiveCell.Row
cColumn = ActiveCell.Column
Selection.TextToColumns Destination:=Cells(cRow, cColumn + 1), DataType:=xlDelimited, ConsecutiveDelimiter:=True, Space:=True
End SubVBA Code Breakdown
- Firstly, we’re calling our Function SeparateText.
- Secondly, declaring our variable types.
- Thirdly, we’re setting our selected range as our row and column.
- Finally, we’re using the VBA TextToColumns method with a space set as the delimiter to separate text.
- Then, Save the Module.
- After that, select cell range B5:B10.
- Finally, press the Run button.
Note: We need to keep our cursor inside our Sub Procedure, to do this.
A Warning dialog box may appear.
- Click on OK.
This will execute our code, and the text will be separated.
Read More: How to Split a String into an Array in VBA (3 Ways)
6. Creating a User-Defined Function to Separate Text
We’ll use the VBA Split function to formulate a custom function to separate text in this method.
Steps:
- Firstly, as shown in method 5, bring up the VBA Module window.
- Secondly, type the following code.
Function SeparateTextDelimiter(r As Range, d As String, x As Integer)
With Application
Function SeparateTextDelimiter(r As Range, d As String, x As Integer)
With Application
SeparateTextDelimiter = .IfError(.Index(Split(r, d), 1, x), "")
End With
End Function
VBA Code Breakdown
- Firstly, we’re calling our Function SeparateTextDelimiter.
- Secondly, declaring our parameter types.
- Finally, we’re setting the function structure. We can input any delimiter here.
- Additionally, we’ve used the VBA IfError method to ignore any errors.
- Thirdly, Save and close the window.
Now, we’ll use this function to separate text. To do that –
- Firstly, type the following formula in cell C5.
=SeparateTextDelimiter(B5,";",1)Our text is in cell B5, the delimiter is a Semicolon. Moreover, our function splits the text into two parts, we’ll return the first part. That’s why we’ve typed 1 in the function.
- Secondly, press ENTER and AutoFill the formula.
- Thirdly, select the cell range D5:D10 and type this formula.
=SeparateTextDelimiter(B5,";",2)Our text is in cell B5, the delimiter is a Semicolon. Moreover, our function splits the text into two parts, we’ll return the second part. That’s why we’ve typed 2 in the function.
- Finally, press CTRL + ENTER. This will AutoFill our formula.
This is what the final step should look like.
Read More: How to Separate Words in Excel Using Formula (Ultimate Guide)
7. Inserting FILTERXML Function to Split Text
We can use the FILTERXML function along with the TRANSPOSE and SUBSTITUTE functions to separate text.
Steps:
- Firstly, type the following formula in cell C5.
=TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(B5," ","</y><y>")&"</y></x>","//y"))Formula Breakdown
- SUBSTITUTE(B5,” “,”</y><y>”)
- Output: “Joey</y><y>ME”.
- The SUBSTITUTE function is replacing the blank space with the XML tag.
- Here, x means main-node and y means sub-node.
- FILTERXML(“<x><y>Joey</y><y>ME</y></x>”,”//y”) will return the texts separated in a column format.
- Our Formula reduces to TRANSPOSE({“Joey”;”ME”})
- Output: {“Joey”,”ME”}.
- The TRANSPOSE function changes the orientation of the range. Here it is changing our range from column to row.
- Secondly, press ENTER.
We can see the right side of the text is automatically separated in cell D6.
- Finally, AutoFill the formula to the cell C6:C10.
This will separate our texts into two columns.
8. VBA Code to Separate Range of Text (No Need to Pre Select)
In this method, we’ll use For Next Loop to create a VBA code for separating text in Excel. Moreover, we don’t need to define our cell range before executing the code.
Steps:
- Firstly, as shown in method 5, bring up the VBA Module window.
- Secondly, type the following code.
Sub SeparateTextWithLoop()
Dim sText() As String, cColumn As Long, x As Variant
For n = 5 To 10
sText = Split(Cells(n, 2), " ")
cColumn = 3
For Each x In sText
Cells(n, cColumn) = x
cColumn = cColumn + 1
Next x
Next n
End SubVBA Code Breakdown
- Firstly, we’re calling our Sub Procedure SeparateTextWithLoop.
- Secondly, declaring our variable types.
- Our data starts from row 5 and ends in 10. We’ve used that in our Loop.
- Then, we’re using the VBA Split function to separate our text with the Space delimiter.
- Finally, another For Loop is used to show our output.
- Then, click anywhere inside the code and press the Run button.
Thus, we’ll separate our texts using a VBA code without pre-selecting the cell range.
Read More: How to Split Text in Excel into Multiple Rows (6 Quick Tricks)
9. Merging LEFT, RIGHT, LEN & SEARCH Functions to Separate Text
In this method, we’ll combine the LEFT, SEARCH, RIGHT, and LEN functions to separate texts.
Steps:
- Firstly, type the following formula in cell C5.
=LEFT(B5,SEARCH(" ",B5)-1)Formula Breakdown
- SEARCH(” “,B5)-1
- Output: 4.
- Then the SEARCH function will look for the position of Space within cell B5.
- It will find it in position 5.
- We’re subtracting 1 to return the character before the Space.
- Our Formula reduces to -> LEFT(B5,4)
- Output: “Joey”.
- The LEFT function returns a number of characters from the left side of a cell. Here, we’re telling it to return 4 characters from cell B5. Thus, we’re getting our output.
- Secondly, press ENTER and AutoFill the formula.
- Thirdly, type this formula into cell D5.
=RIGHT(B5,LEN(B5)-SEARCH(" ",B5))Formula Breakdown
- LEN(B5)-SEARCH(” “,B5)
- Output: 2.
- The SEARCH function will look for the position of Space within cell B5.
- It will find it in position 5.
- The LEN function returns the character length of our text. It will return 7.
- Finally, we’re subtracting these two values. Thus we’re getting 2.
- Our formula reduces to -> RIGHT(B5,2)
- Output: “ME”.
- The RIGHT function returns characters from the right side of a text. We’re telling it to return 2 characters from the right side. Thus we’re getting “ME”.
- Then, press ENTER and AutoFill the formula.
Thus, we’ll separate the texts into 2 columns using the formula.
Read More: How to Split First And Last Name in Excel (6 Easy Ways)
10. Separate Text with Line Break Using a Combined Formula
For the last method, we’ll use the CHAR, FIND, LEFT, RIGHT, and LEN functions to separate text.
Steps:
- Firstly, type the following formula in cell C5.
=LEFT(B5,FIND(CHAR(10),B5)-1)Formula Breakdown
- FIND(CHAR(10),B5)-1
- Output: 4.
- The CHAR function returns a character. CHAR(10) will return the Line Break.
- Then the FIND function will look for the position of Line Break within cell B5.
- It will find it in position 5.
- We’re subtracting 1 to return the character before the Line Break.
- Our Formula reduces to -> LEFT(B5,4)
- Output: “Joey”.
- The LEFT function returns a number of characters from the left side of a cell. Here, we’re telling it to return 4 characters from cell B5. Thus, we’re getting our output.
- Secondly, press ENTER and AutoFill the formula.
- Thirdly, select the cell range D5:D10 and type this formula.
=RIGHT(B5,LEN(B5)-FIND(CHAR(10),B5))Formula Breakdown
- LEN(B5)-FIND(CHAR(10),B5)
- Output: 2.
- CHAR(10) will return the Line Break.
- Then the FIND function will look for the position of Line Break within cell B5.
- It will find it in position 5.
- The LEN function returns the character length of our text. It will return 7.
- Finally, we’re subtracting these two values. Thus we’re getting 2.
- Our formula reduces to -> RIGHT(B5,2)
- Output: “ME”.
- The RIGHT function returns characters from the right side of a text. We’re telling it to return 2 characters from the right side. Thus we’re getting “ME”.
- Finally, press CTRL + ENTER.
This will AutoFill our formula. In conclusion, we’ve shown you the last method to separate text.
Practice Section
We’ve provided practice datasets for each method in the Excel file.
Conclusion
We’ve shown you 10 easy methods of how to separate text in Excel. If you face any problems, feel free to comment below. Thanks for reading!
