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

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

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.

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

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

Step 6: Tab on the option Finish. 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.

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

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.

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

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

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.

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

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)

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.

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

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

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.

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

Similar Readings:

• How to Separate Words in Excel Using Formula (Ultimate Guide)
• split text into multiple cells in Excel

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.

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

Step 2: From the Toolbar Click Insert>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 VariantFor n = 2 To 12MyArray = Split(Cells(n, 2), ",")Count = 3For Each i In MyArrayCells(n, Count) = iCount = Count + 1Next iNext nEnd Sub

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.

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.

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

Step 3: After Clicking OK, Power Query Editor for Table 1 will appear. In the HomeTab, Click on the drop-down menu Split Column>Select By Delimiter.

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.

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

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.

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

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

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

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

• Split String by Character in Excel (6 suitable Ways)
• How to Split String by Length in Excel (8 Ways)