This article illustrates 5 different ways you can split a column in excel by word. Suppose each cell of a column in a dataset contains a particular word. You may need to split the entire column based on that word. The following picture highlights such a case. Have a quick look through the article to learn how to do that.
How to Split a Column in Excel by Word: 5 Ways
Imagine you have a list of addresses in a column as shown below. Each cell of the column contains the word SUITE. Now you want to split the column by that word. Then follow the methods below.
1. Use Text to Columns Feature to Split Column by Word in Excel
You can split the column using the Text to Columns wizard in excel. Follow the steps below to be able to do that.
📌 Steps
- First, select the range B5:B9.
- Then, press CTRL+H to open the Find and Replace window.
- Next, 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.
- Then, press the Replace All button. Now hit OK and close the Find and Replace window.
- After that, select Data >> Text to Columns as shown in the picture below.
- Then, in step 1, mark the Delimited data type and then select Next.
- Next, in step 2, check Other for Delimiters and use # as the delimiter and then select Next
- Now, in the final step, use the upward arrow in the Destination field to select the destination for the split data. Finally, select the Finish button.
- After that, you will see the split data as follows.
- Now, you can remove the # character from the original column.
2. Split Column by Word with Flash Fill Feature
After replacing the word SUITE with #SUITE in the earlier method, you can use the Flash Fill feature in excel to get the same result.
- Firstly, copy the text before the # character from cell B5 and paste it onto cell C5. Then, press CTRL+E while you are in cell C6. After that, you will get the following result.
- Secondly, copy the text after the # character from cell B5 and paste it onto cell D5. Then, press CTRL+E while you are in cell D6. After that, you will see the following result.
3. Combine RIGHT, LEFT, and LEN Functions with SEARCH to Segregate a Column
Alternatively, you can combine the LEFT, RIGHT, LEN, and SEARCH functions in excel to get the desired result as earlier.
- First, enter the following formula in cell C5 and then use the Fill Handle icon to apply it to the cells below.
=LEFT(B5,SEARCH("SUITE",B5)-2)
- Next, apply the following formula in cell D5. Then drag the Fill Handle icon to the cells below.
=RIGHT(B5,LEN(B5)-SEARCH("SUITE",B5)+1)
4. Split Column by Word with Power Query Tool
You can also use the Power Query Editor in excel to split data based on a particular word. Follow the steps below to do that.
📌 Steps
- First, select the entire range of data. Then, select Data >> From Table/Range as shown below. Next, click OK. After that, you will be taken to the Power Query Editor.
- Now, select Replace Values from the Home tab in the editor.
- Then, enter SUITE in the Value to Find field and #SUITE in the Replace With field. Then select OK.
- Next, select Split Column >> By Delimiter as shown in the picture below.
- After that, use the drop-down arrow for the custom delimiter and enter # in the delimiter field. Then, select OK.
- Now, select Close & Load >> Close & Load To as shown in the following picture.
- Then, use the upward arrow to select the location for the split data in the same worksheet. Now, select OK.
- Finally, you will get the desired result as shown below.
5. Use VBA Code to Separate a Column by Word
It is also possible to split the column by using Excel VBA. Follow the steps below to be able to do that.
📌 Steps
- First, press ALT+F11 to open the VBA window.
- Then, select Insert >> Module to create a new blank module.
- Copy the following code after that.
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- Now, paste the copied code on the blank module as shown below. Then, keep the cursor on the code and press F5 to run it. After that, you will get the same result as earlier.
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
- Remember to use a different character if the # sign is already present in the data.
- You need to modify the code based on your dataset if you are using a different one.
Download Practice Workbook
You can download the practice workbook from the download button below.
Conclusion
Now you know the 5 different methods to split a column in excel based on a particular word. Please let us know if this article has helped you with your problem. You can also use the comment section below for further queries or suggestions.
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
