Split Column in Excel by Word (5 Effective Ways)

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.

Split Column in Excel by Word


Download Practice Workbook

You can download the practice workbook from the download button below.


5 Ways to Split a Column in Excel by Word

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.

Split Column by Word with Text to Columns Wizard

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

Split Column by Word with Text to Columns Wizard


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.

Split Column by Word with Flash Fill in Excel

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

Split Column by Word with Formula


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.

Split Column by Word with Power Query

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

Split Column by Word with Excel VBA

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.

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. Do visit our ExcelDemy blog to explore more on excel. Stay with us and keep learning.

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo