Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

how to separate 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.

sample dataset of names for spearation


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.

Use Text to Columns Feature to Separate Words in Excel

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

Choosing the type of file type for the conversion.

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

Choosing the delimiters

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

choosing column data format.

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

The final output of using text-to-column feature to separate names.

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.

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

  • Press ENTER after this.

Use of LEFT and SEARCH function to get the first part of the name.

πŸ” 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.

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

separating the first part of the name using the LEFT and SEARCH functions.

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

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

Use the Right, Len, and Search functions to get the second part of the name.

πŸ” 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, Full Names will sort as Last Name in the new column entirely.

The final output of using LEFT, RIGHT, LEN, and SEARCH functions to separate names.

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

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

  • Press Enter after this.

Apply LEFT, RIGHT, LEN, and FIND Functions in Excel to separate Words

πŸ” 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.

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

The first part of the name is separated using the LEFT and FIND functions.

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

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

Enter RIGHT, LEN, and FIND formulas to separate the second part of the name.

πŸ” 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; the rest of the cells will sort as Last Name throughout the range.

The final output of using RIGHT, LEFT, LEN, and FIND functions to separate names in Excel.

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.

Initialize VBA macro.

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

The FInal output shows separate two words in name using the VBA macro in Excel.

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.

Utilize Excel Power Query Feature to Separate two Words in Excel

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

split column using the split column feature.

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

fixing delimiter and quote character

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

names split into two parts in the power query editor.

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

table loaded from the power query editor to the sheet.

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

the final output of the power query feature showing in the sheet to separate two words in Excel.

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.

Separate two Words Using Flash Fill Feature in Excel

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

first part of the name separated using the flash fill.

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

The final output of the name separate using the flash fill feature.

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

Maruf Islam

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