How to Separate Two Words in Excel (6 Easy Ways)

Separating two words in Excel is quite handy in order to extract data from a dataset. In this article, we use LEFT, RIGHT, LEN, SEARCH, FIND functions, various Excel features as well as VBA Macro Code to separate two words in Excel.

Among those methods, the Text to Columns feature is one of them. The following image shows the use of it for separating two words in Excel.

Download Practice Workbook

Download this practice workbook to exercise.

6 Easy Ways to Separate Two Words in Excel

Let’s say, we have a dataset containing a column of Full Name (consisting of First Name and Last Name), we want Full Name into two separate columns containing the First and Last Name. We will separate the name into two separate parts. For avoiding any compatibility issues, opt to use Excel 365 edition.

1. Use Text to Columns Feature to Separate Words

We can separate the Full Name column into two columns via the Text to Columns feature resulting in First Name and Last Name as two individual columns. Therefore, follow the below steps to complete the task.

STEPS:

• Select the range of cells (B3:B12).
• Then go to Data>Click on Text to Columns.

• A window will pop up. In the popup window, Click Choose the file that best describes your data as Delimited. Afterward, click on Next.

• Another window will come up. In that window, under Delimiters options Tick the Space option. Also, Tick the box saying Treat consecutive delimiters as one. Again, Select Next.
• It is necessary to tick the box saying Treat consecutive delimiters as one because while inputting data users might place too many spaces between words. If the box remained Unticked the outcome will be merely two split cells with nothing but spaces in them.

• Choose the outcome Cell Data Format (i.e.Text). Also, select any Destination range you want the data to display (i.e.C5:D15).
• Press the Finish button.

• The outcome will be the same as the image below.

From the image, you can see the Full Name column gets split into two different columns depicting the First and Last Name separately.

Read More: How to Split Text in Excel Using Formula (5 Easy Ways)

2. Combine LEFT, RIGHT, LEN, and SEARCH Functions for Splitting Words

The LEFT function extracts text strings from cell references. The RIGHT function extract text from the right side of the text. Combining these two functions with the SEARCH function can fetch data as First and Last Names separately.

STEPS:

• Insert the below formula in any blank cell (C5) you want to display the First Name.

• Press ENTER after this.

• Press ENTER and Drag the Fill Handle, the outcomes are much alike as depicted in the following picture.

• Paste the following formula in any blank cell (D5), you want to display the Last Name.

• Hit ENTER then drag the Fill Handle, Full Names will sort as Last Name in the new column entirely.

Read More: How to Split Text by Number of Characters in Excel (7 Ways)

3. Apply LEFT, RIGHT, LEN, and FIND Functions to Divide Words

We can use a combination of multiple functions to manipulate data from a dataset. A combination of LEFT and Find can extract a word from in any cell. On the other hand, combined RIGHT, LEN, and FIND functions fetch a word from the right side of any text string.

STEPS:

• Type the bold text as a formula in any blank cell (i.e.C5).

• Press Enter after this.

• Tab the ENTER key afterward Drag the Fill Handle, the consequences will be the same as in the image below.

• Copy the following formula and paste it into any blank cell (D5)

• Press ENTER then drag the Fill Handle; the rest of the cells will sort as Last Name throughout the range.

Read More: How to Split Text by Space with Formula in Excel (5 Ways)

4. Separate Two Words Through VBA Code

We can use a simple VBA Macro Code to generate separate columns attributed to First and Last Names. Before running the VBA Macro Code, the dataset looks like the image below.

STEPS:

• First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’for open the Visual Basic Editor.

• From the Toolbar Click Insert>Module.
• In the Module, paste the following code then press F5 to run the code.

Sub Word_Separate()
Dim Xarray() As String, C As Long, x As Variant
For n = 5 To 14
Xarray = Split(Cells(n, 2), ",")
C = 3
For Each x In Xarray
Cells(n, C) = x
C = C + 1
Next x
Next n
End Sub

• Go to the VBA worksheet, we will see the Full Name column data get separated into two columns as we want them throughout the whole method.

Read More: How to Split a String into an Array in VBA (3 Ways)

5. Utilize Excel Power Query Feature to Split Words

Power Query split column using a delimiter. Thus, we can use Power Query to split the Full Name column into two individual columns.

Steps:

• Select the range of cells (B4:B14) you want to split. Then go to Data>From Table (in Get & Transform section). As our range of cells is not in a particular table. It is necessary to form a Table.

• It will bring out a popup window Create Table. Click on OK.
• After Clicking OK, Power Query Editor for Table 1 will appear. In the Home Tab, Click on the drop-down menu Split Column>Select By Delimiter.

• Another window named Split Column by Delimiter pops up.
• Choose Space from the drop-down option Select or enter delimiter and None from the drop-down option Quote Character.
• Click OK.

• These steps generate a new worksheet beside PowerQuery as a result of showing the split columns we desire.

• Then you load the table back to the worksheet.
• To do this, go to the Home tab > Close & Load > Close and Load To.

• After loading the table in a new worksheet, we can see that the names are now divided into two separate parts.

Read More: How to Separate Words Using Formula in Excel

6. Separate Words Using Flash Fill Feature

Excel has a tendency to predict entries that are input before. By manipulating Excel’s behavior, we can insert data much easier into two different columns. In this case, we use Flash Fill to do so to separate two words in Excel.

Steps:

• Type the First Name in any cell (C5).
• Press ENTER.
• Select the range of cells C5:C14.
• Then go to Data> Data Tools>Flash Fill.

• Then we will see that the range of cell C5:C14 is filled with the first part of the name listed in the range of cells B5:B14.

• After that, repeat the same process for the next part of the name.
• Then you can see that two parts of the words now separate using the Flas Fill feature in Excel.

Read More: How to Separate Text in Excel (10 Easy Methods)

Conclusion

In this article we use functions such as LEFT, RIGHT, LEN, FIND and SEARCH as well as features and VBA Macro Code. The usage of combined functions has a prerequisite of understanding character and their spacing. Features like Text to Columns and Flash Fill do not require such things. Power Query and VBA Macro Code are exceptional in their aspects. Hope you find the steps lucid enough to follow. Comment if you have further queries regarding the methods or want to add something.Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.

Further Readings

• How to Split Text in Excel into Multiple Rows (6 Quick Tricks)
• Split String by Character in Excel (6 suitable Ways)
• How to Split Text after a Certain Word in Excel (7 Ways)
• Split String by Length in Excel (8 Ways)
• How to Split Text in Excel by Character (5 Quick Methods)
• Splitting Text in Excel (8 Useful Applications)