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

The following image shows one of the ways to separate two words in Excel. Read on to learn more.

Let’s say we have a dataset containing a column of Full Name (consisting of a First Name and a Last Name), and we want to separate it into parts containing the First and Last Name.

Method 1 – Use Text to Columns Feature to Separate Words

STEPS:

Select the range of cells (B3:B12).
Go to the Data tab and select Data Tools.
Click on Text to Columns.

In the popup window, for Choose the file type that best describes your data, choose Delimited.
Click on Next.

Under Delimiters options, tick the Space option. Tick the box saying Treat consecutive delimiters as one.
Select Next.

Choose the resulting cell data format (i.e.Text). Select any Destination range you want the data to display (i.e. C5:D14).
Press Finish.

The outcome should be the same as the image below.

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

STEPS:

Insert the below formula in the first blank cell (C5) for which you want to display the First Name:

=LEFT(B5, SEARCH(" ",B5,1))

Press Enter.

Formula Breakdown

SEARCH(” “,B5,1)): Inside the formula, the SEARCH function returns the string location (” “) of the text string in cell B5,
LEFT(B5, SEARCH(” “,B5,1)): LEFT function displays the text strings from the beginning of cell B5 to the space.

Drag the Fill Handle to fill in the entire column with the formula.

Paste the following formula in the first blank cell (D5) where you want to display the Last Name:

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

Formula Breakdown

SEARCH(” “,B5,1)): Inside the formula, the SEARCH function returns the string location (” “) of the text string in cell B5,
LEN(B5): Returns the length of the character in the cell B5.
RIGHT(B5,LEN(B5)-SEARCH(” “,B5,SEARCH(“”,B5)+1)): RIGHT function displays the text strings from the beginning of cell B5 to the space returned by the SEARCH function.

Hit Enter then drag the Fill Handle to copy the formula down.

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

STEPS:

Select C5 and copy the following formula:

=LEFT(B5,FIND(" ",B5)-1)

Press Enter.

Formula Breakdown

➢ FIND(” “,B5): Inside the formula, the FIND function returns the string location (” “) of the text string in cell B5,

➢ LEFT(B5,FIND(” “,B5)-1): LEFT function displays the text strings from the beginning of cell B5 to the space.

Drag the Fill Handle to copy the formula across the column.

Copy the following formula and paste it into D5:

=RIGHT(B5,LEN(B5)-FIND(" ",B5))

Formula Breakdown

FIND(” “,B5): Inside the formula, the FIND function returns the string location (” “) of the text string in cell B5,
LEN(B5): Return the length of the string in cell B5.
RIGHT(B5,LEN(B5)-FIND(” “,B5)): RIGHT function displays the text strings from the end of cell B5 to the space.

Press Enter then drag the Fill Handle to fill the other cells.

Method 4 – Separate Two Words Through VBA Code

Before running the VBA Macro Code, the dataset looks like the image below.

STEPS:

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

From the toolbar, click Insert and then Module.
In the Module, paste the following code, then press F5 to run it.

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 back to the worksheet.

Method 5 – Utilize Excel Power Query Feature to Split Words

Steps:

Select the range of cells (B4:B14) you want to split.
Go to Data and select From Table (in the Get & Transform section).

This will bring out a popup window Create Table. Click on OK.
The 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.