How to Separate Text in Excel (10 Easy Methods)

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.

how to separate text in excel


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.

how to separate text in excel

Read More: How to split text into multiple cells in Excel


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.

how to separate text in excel

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.

how to separate text in excel

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

how to separate text in excel

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.

how to separate text in excel

After that, the Power Query Editor window will appear.

  • Then, from Split Column >>> select By Delimiter.

how to separate text in excel

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…”.

how to separate text in excel

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

how to separate text in excel

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.

how to separate text in excel

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.

how to separate text in excel

  • Thirdly, select the cell range C5:C10.
  • Finally, use the Fill Handle again to the right side to AutoFill the formula.

how to separate text in excel

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.

Steps:

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

how to separate text in excel

  • 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 Sub

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

how to separate text in excel

A Warning dialog box may appear.

  • Click on OK.

This will execute our code, and the text will be separated.

how to separate text in excel

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:

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.

how to separate text in excel

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

how to separate text in excel

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.

how to separate text in excel

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:

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 Sub

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

how to separate text in excel

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

how to separate text in excel

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

how to separate text in excel

  • Then, press ENTER and AutoFill the formula.

Thus, we’ll separate the texts into 2 columns using the formula.

how to separate text in excel

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

how to separate text in excel

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

how to separate text in excel


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!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo