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.

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.

Dataset-How to Separate Two Words in Excel

Dataset for Download

6 Easy Ways to Separate Two Words in Excel

Method 1: Using Text to Columns Feature

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.

Step 1: Select the range of cells (B3:B12).

Step 2: Then go to Data>Click on Text to Columns.

Text to column-How to Separate Two Words in Excel

Step 3: A window will pop up. In the popup window, Click Choose the file that best describes your data as Delimited. Afterwards, click on Next.

Text to column delimited

.Step 4: Another window will come up. In that window, under Delimiters options Ticked the Space option. Also Ticked 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.

Text to column delimiters

Step 5: Choose the outcome Cell Data Format (i.e.Text). Also, Select any Destination range you want the data to display (i.e.C3:D12).

Text to Column data format

Step 6: Tab on the option Finish. The outcome will be the same as the image below.

Text to column final result-How to Separate Two Words in Excel

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

Method 2: Using LEFT RIGHT and SEARCH Function

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 Name separately.

Step 1: Insert the below formula in any blank cell(C3) you want to display the First Name

=LEFT(B3, SEARCH(” “,B3,1))

Here in the formula, the SEARCH function locates space (” “) text string from the beginning for cell B3 and then returns with the character number. Afterwards LEFT extracts the characters at the beginning to the number SEARCH returns.

Left and search function-How to Separate Two Words in Excel

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

Left search function result

Step 3: Paste the following formula in any blank cell (D3), you want to display the Last Name.

=RIGHT(B3,LEN(B3)-SEARCH(” “,B3,SEARCH(“”,B3)+1))

In the formula, the SEARCH function returns the character (Space) position adding an extra one then the RIGHT function subtracts it from the length (LEN function) of the text strings in any cell. 

Right and search function -How to Separate Two Words in Excel

Step 4: Hit ENTER then Drag the Fill Handle, Full Names will sort as Last Name in the new column entirely.

Right and search function result-How to Separate Two Words in Excel

Method 3: Using LEFT RIGHT LEN and FIND Function

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.

Step 1: Type the bold text as formula in any blank cell (i.e.C3)

=LEFT(B3,FIND(” “,B3)-1)

Inside the formula, the FIND function returns the string location (” “) of the text string in cell B3, in the case of double space we subtract one then the LEFT function displays the text strings from the beginning of the cell B3. 

Left and find function-How to Separate Two Words in Excel

Step 2: Tab the ENTER key afterwards Drag the Fill Handle, the consequences will be the same as in the image below.

Left and find function result

Step 3: Copy the following formula and Paste it into any blank cell (D3)

=RIGHT(B3,LEN(B3)-FIND(” “,B3))

The FIND function returns the string (Space) position from the beginning of the text in cell B3. Then the RIGHT function subtracts it from the length (LEN function) of the text strings and displays it from the right of the text in cell B3. 

Right len and find function-How to Separate Two Words in Excel

Step 4: Press ENTER then Drag the Fill Handle; the rest of the cells will sort as Last Name throughout the range.

Right len and find function result-How to Separate Two Words in Excel


Further Readings:


Method 4: Using VBA Macro Code

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

VBA method-How to Separate Two Words in Excel

Step 1: Press ALT+F11 altogether, the Microsoft Visual Basic Application will open.

Step 2: From the Toolbar Click Insert>Module.

Vba method module

Step 3: In the Module, paste the following code then press F5 to run the code.

Sub SplitName()
Dim MyArray() As String, Count As Long, i As Variant
For n = 2 To 12
MyArray = Split(Cells(n, 2), ",")
Count = 3
For Each i In MyArray
Cells(n, Count) = i
Count = Count + 1
Next i
Next n
End Sub

Vba macro code

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

Vba macro cod result-How to Separate Two Words in Excel

Method 5: Using Power Query Feature

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

Step 1: Select the range of cells (B1:B12) 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.

Power query-How to Separate Two Words in Excel

Step 2: It will bring out a popup window Create Table. Click on OK.

Power Query table formation

Step 3: 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. 

Power Query Editor

Step 4: 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.

Split column by delimiters

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

Power query final result-How to Separate Two Words in Excel

 Method 6: Using Flash Fill Feature

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

Step 1: Type the First Name in any cell (C3). Press ENTER.

Flash fill-How to Separate Two Words in Excel

Step 2: Go to Data> Data Tools>Flash Fill.

Flash fill feature

Afterwards, all the First Names from the Full Name column will automatically appear.

Flash fill result partial

Step 3: Repeat steps 1 and 2 with Last Name and you will get the result as shown in the image below.

Flash fill final result-How to Separate Two Words in Excel

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.


Further Readings:

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo