Split Column in Excel by Word (5 Effective Ways)

Suppose we have a list of addresses in a column as shown below. Each cell of the column contains the word SUITE. Let’s split the column by that word.

Method 1 – Using Text to Columns Feature

Steps:

Select the range B5:B9.
Press CTRL+H to open the Find and Replace window.
Enter SUITE (the desired word) in the Find what field and #SUITE in the Replace with field. Use another character if the column already contains the # character.
Press the Replace All button.
Click OK to close the Find and Replace window.

Select Data >> Text to Columns.

In Step 1, mark the Delimited data type.
Select Next.

In Step 2, check Other for Delimiters and use # as the delimiter.
Click Next.

In Step 3, use the upward arrow in the Destination field to select the destination for the split data.
Click the Finish button.

The data is split as specified.

Remove the # character from the original column.

Method 2 – Using the Flash Fill Feature

We can use the Flash Fill feature to achieve the same result.

Steps:

Copy the text before the # character in cell B5 and paste it into cell C5.
Press CTRL+E while you are in cell C6.

The following result is returned.

Copy the text after the # character in cell B5 and paste it into cell D5.
Press CTRL+E while you are in cell D6.

The sequence is repeated in the rest of the series.

Method 3 – Combining RIGHT, LEFT, and LEN Functions with SEARCH

Alternatively, we can combine the LEFT, RIGHT, LEN, and SEARCH functions.

Steps:

Enter the following formula in cell C5 and use the Fill Handle icon to apply it to the cells below:

=LEFT(B5,SEARCH("SUITE",B5)-2)

Apply the following formula in cell D5 and drag the Fill Handle icon to the cells below:

=RIGHT(B5,LEN(B5)-SEARCH("SUITE",B5)+1)

Method 4 – Using the Power Query Tool

Steps:

Select the entire range of data.
Select Data >> From Table/Range.
Click OK.

The Power Query Editor will open.

Select Replace Values from the Home tab in the editor.

Enter SUITE in the Value to Find field and #SUITE in the Replace With field.
Click OK.

Select Split Column >> By Delimiter.

Use the drop-down arrow for the custom delimiter and enter # in the delimiter field.
Click OK.

Select Close & Load >> Close & Load To.

Use the upward arrow to select the location for the split data in the same worksheet.
Click OK.

The desired result is returned.

Method 5 – Using VBA Code

Steps:

Press ALT+F11 to open the VBA window.
Select Insert >> Module to create a new blank module.

Copy the following code:

Sub SplitColumnByWord()
    Range("B5:B9").Select
    Selection.Replace What:="SUITE", Replacement:="#SUITE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, FormulaVersion:=xlReplaceFormula2
    Selection.TextToColumns Destination:=Range("C5"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Other:=True, OtherChar:="#", _
        TrailingMinusNumbers:=True
    Selection.Replace What:="#SUITE", Replacement:="SUITE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, FormulaVersion:=xlReplaceFormula2
End Sub

Paste the copied code in the blank module.

Keep the cursor on the code and press F5 to run it.

The same result as earlier is returned.

Explanation of the VBA Code:

Sub SplitColumnByWord()
Write the code inside this sub procedure.

Range(“B5:B9”).Select
Select the dataset in the target column.

Selection.Replace What:=”SUITE”, Replacement:=”#SUITE”, LookAt:=xlPart, _
SearchOrder:=xlByRows, FormulaVersion:=xlReplaceFormula2
Add # before the desired word. Use a different one if it already exists in the dataset.

Selection.TextToColumns Destination:=Range(“C5”), DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Other:=True, OtherChar:=”#”, _
TrailingMinusNumbers:=True
Split the column based on the position of the # character.

Selection.Replace What:=”#SUITE”, Replacement:=”SUITE”, LookAt:=xlPart, _
SearchOrder:=xlByRows, FormulaVersion:=xlReplaceFormula2
Remove the # sign from the original dataset.