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.
Things to Remember
- Use a different character if the # sign is already present in the data.
- Modify the VBA code according to the dataset you are working with.
Download Practice Workbook
Related Articles
- How to Split Date and Time Column in ExcelÂ
- How to Split Column in Excel by CommaÂ
- How to Split Column by First Space in ExcelÂ
- Split One Column into Multiple Columns in Excel Formula
