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

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.

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

Dataset for Download

6 Easy Ways to Separate Two Words in Excel

Method 1: Using Text to Columns Feature

Method 2: Using LEFT RIGHT and SEARCH Function

Method 3: Using LEFT RIGHT LEN and FIND Function

Method 4: Using VBA Macro Code

Method 5: Using Power Query Feature

(Video) Excel Conditional Formatting - Manage Rules

Method 6: Using Flash Fill Feature

Conclusion

Further Readings

Dataset for Download

Separate Two Words in Excel.xlsm

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.

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

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.

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

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

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

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

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

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

(Video) Excel Conditional Formatting Dates - Find Out the Weekdays from Some Dates

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

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.

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

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

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

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.

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

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

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

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)

(Video) Excel Conditional Formatting - Data Bars

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.

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

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

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

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.

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

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

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

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.

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

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

Step 2: From the Toolbar Click Insert>Module.

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

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

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

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.

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

(Video) How to Make a Gantt Chart in Excel 2013 [video]

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.

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

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

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

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.

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

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.

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

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

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

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.

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

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

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

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

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

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

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

(Video) How to Make a Graph or Chart in Excel from Scratch

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)

FAQs

How do you split words in word? ›

Word automatically breaks the text at a space or a hyphen at the end of a line. To keep two words or a hyphenated word together on one line, you can use a nonbreaking space or nonbreaking hyphen instead of a regular space or hyphen. Click where you want to insert the nonbreaking space.

How do I separate first and last names in Excel? ›

Read the steps
  1. Add an empty column by right-clicking on the top of the column next to the existing column of names, then select Insert. ...
  2. Click the Data tab.
  3. Click on the top of the column with your contacts' names to highlight the whole column.
  4. Click Text to Columns.
  5. Select "Delimited" and click Next.
5 Aug 2022

How do I separate columns in Excel? ›

How to Split Data into Multiple Columns in Microsoft Excel (Text-to ...

How do I separate Data in Excel with a comma? ›

How to Create a Comma Separated List from Excel
  1. Insert an empty column next to your IDs.
  2. Type a comma in the cell next to the first ID.
  3. Hold your mouse on the lower-right of the cell containing the column and double-click.
  4. You should now have commas next to your IDs.

How do I separate a column of text and numbers in Excel? ›

Split text and numbers
  1. Generic formula. =MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))
  2. To separate text and numbers, you can use a formula based on the FIND function, the MIN function, and the LEN function with the LEFT or RIGHT function, depending on whether you want to extract the text or the number. ...
  3. Overview.

How do I extract text from a cell? ›

For example, the formula =LEN() gives back the number of characters in a cell. So =LEFT(A1,LEN(A1)-2) extracts the entire text in a cell except the last two characters.

How do I cut text left in Excel? ›

Remove characters from left side of a cell
  1. =REPLACE(old_text, start_num, num_chars, new_text)
  2. =RIGHT(text,[num_chars])
  3. =LEN(text)

How do you split names in Excel formulas? ›

Enter the formula of =RIGHT(A2,LEN(A2)-FIND(” “,A2,1)) in another blank cell, Cell C2 in this case. 3. Copy those two formulas down and you will see the full name column is split to the first name and last name columns as shown below.

How do I split a row in word? ›

Select the cell that you want to split. Select Layout > Split Cells. Enter the number of columns or rows that you want to split the selected cell into, and then select OK.

What is it called when a word is split between two lines? ›

Hyphenation (properly called word division) is breaking long words between lines. The purpose of hyphenation is to reduce the white space between words.

How do I separate names in sheets? ›

Here's how:
  1. Select the range you want to split into first and last names.
  2. Open the Data menu, and select the Split Text To Columns option. ...
  3. A separator selection menu will appear floating over the cells.
  4. Choose Space from the Separator menu.
  5. The names in the selected range will be split into first and last names.

Can you split a cell within a cell in Excel? ›

Click in a cell, or select multiple cells that you want to split. Under Table Tools, on the Layout tab, in the Merge group, click Split Cells. Enter the number of columns or rows that you want to split the selected cells into.

How do I split one column into multiple columns in Word? ›

Splitting Cells: Quick Menu Option
  1. Select the cell you want to split.
  2. Right click within the selected cell » select Split Cells... The Split Cells dialog box appears.
  3. In the Number of columns and/or Number of rows boxes, type or use the nudge buttons to select the desired number of rows and/or columns.
  4. Click OK.
31 Aug 2020

How do I split a column in sheets? ›

Split data into columns
  1. On your computer, open a spreadsheet in Google Sheets. ...
  2. At the top, click Data. ...
  3. To change which character Sheets uses to split the data, next to "Separator" click the dropdown menu.
  4. To fix how your columns spread out after you split your text, click the menu next to "Separator"

How do I separate Text from numbers in Excel? ›

How to separate numbers from text in Excel with "Flash Fill"
  1. Type the number from your first string. Manually enter the numbers from your first string in the adjacent blank cell. ...
  2. Select the range for your numbers. ...
  3. Click "Flash Fill" under the "Data" tab. ...
  4. Repeat to separate text.
8 Apr 2022

How do I get comma separated values in different rows in Excel? ›

1.2. Split into Rows Using Text to Columns
  1. First select Cell B5, go to Data > Text to Columns.
  2. Then from the Text to Columns Wizard select Original Data Type: Delimited and click Next.
  3. Now choose the Delimiters type: Comma and click Next.
  4. After that, choose the Destination cell (here Cell C5) and press Finish.
29 May 2022

What is the use of Excel Slicer function? ›

Slicers provide buttons that you can click to filter tables, or PivotTables. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is currently displayed. You can use a slicer to filter data in a table or PivotTable with ease.

How do I separate text and numbers in Excel 2016? ›

To separate the text string which number is before the text, you can use the Flash Fill feature of Excel 2013 and later version. Tip: You also can drag the filled handle to the range you want to use, and then click Auto Fill Option and check Flash Fill.

Which of these will you use to separate Data into multiple columns instantly? ›

  1. b) Split cell. We can split a cell or multiple cells in a table, by clicking the split cell dialog in merge group under table tools in the Layout tab.
  2. d) Table. A table contains a grid of columns and rows in excel. The table is used to represent organized data. ...
  3. b) Merging cell.
5 Jul 2019

How do you write text formula? ›

Combine Cells With Text and a Number
  1. Select the cell in which you want the combined data.
  2. Type the formula, with text inside double quotes. For example: ="Due in " & A3 & " days" NOTE: To separate the text strings from the numbers, end or begin the text string with a space.
  3. Press Enter to complete the formula.
25 Jul 2022

How do you select specific text in Excel? ›

Follow these steps:
  1. Begin by doing either of the following: To search the entire worksheet for specific cells, click any cell. ...
  2. On the Home tab, click Find & Select > Go To (in the Editing group). Keyboard shortcut: Press CTRL+G.
  3. Click Special.
  4. In the Go To Special dialog box, click one of the following options.

How do I select only text in a cell in Excel? ›

Save the code and close the window, then type this formula =TextOnly(A1) (A1 is the first row cell in your list range you want to extract text only from) into a blank cell, press Enter button, then fill the handle to the range you want.

How do I remove 2 characters from left in Excel? ›

How to remove characters from left in Excel
  1. =RIGHT(A2, LEN(A2) - 1)
  2. The screenshot below shows the REPLACE formula in action. ...
  3. =RemoveFirstChars(A4, 3)
  4. =LEFT(A2, LEN(A2) - 1)
  5. =LEFT(A2, LEN(A2) - 5)
  6. As you can see in the below screenshot, our custom function works brilliantly!
  7. …and the result won't keep you waiting:
14 Sept 2022

How do I remove two characters from right in Excel? ›

Select the cell which contains the text. Click on the Text Tools icon. A new window will appear. Select the second tab, Delete.

How do you separate first and last name in numbers? ›

Format or separate text into columns in (Apple) Numbers - YouTube

How do I split a middle name in Excel? ›

How to Separate Names in Excel?
  1. Select the “full name” column.
  2. In the Data tab, click on the option “text to columns”.
  3. The box “convert text to columns wizard” opens.
  4. Select the file type “delimited” and click on “next”.
  5. We need to select the type of delimiter that the data contains.

How do I isolate a last name in Excel? ›

How to Extract Last Name in Excel (3 Easy Ways) | No Formula Used

How do I separate text in sheets? ›

Split text, remove duplicates, or trim whitespace
  1. On your computer, open a spreadsheet in Google Sheets. ...
  2. At the top, click Data. ...
  3. To change which character Sheets uses to split the data, next to "Separator" click the dropdown menu.

How do I cut text left in Excel? ›

Remove characters from left side of a cell
  1. =REPLACE(old_text, start_num, num_chars, new_text)
  2. =RIGHT(text,[num_chars])
  3. =LEN(text)

What is the opposite of concatenate in Excel? ›

Opposite of CONCATENATE in Excel (splitting cells)

The opposite of concatenate in Excel is splitting the contents of one cell into multiple cells.

What is a split function? ›

Divides text around a specified character or string, and puts each fragment into a separate cell in the row.

How do I separate text and numbers in Excel? ›

How to separate numbers from text in Excel with "Flash Fill"
  1. Type the number from your first string. Manually enter the numbers from your first string in the adjacent blank cell. ...
  2. Select the range for your numbers. ...
  3. Click "Flash Fill" under the "Data" tab. ...
  4. Repeat to separate text.
8 Apr 2022

How do I remove 2 characters from left in Excel? ›

How to remove characters from left in Excel
  1. =RIGHT(A2, LEN(A2) - 1)
  2. The screenshot below shows the REPLACE formula in action. ...
  3. =RemoveFirstChars(A4, 3)
  4. =LEFT(A2, LEN(A2) - 1)
  5. =LEFT(A2, LEN(A2) - 5)
  6. As you can see in the below screenshot, our custom function works brilliantly!
  7. …and the result won't keep you waiting:
14 Sept 2022

How do I remove two characters from right in Excel? ›

Select the cell which contains the text. Click on the Text Tools icon. A new window will appear. Select the second tab, Delete.

How do you split names in Excel formulas? ›

Enter the formula of =RIGHT(A2,LEN(A2)-FIND(” “,A2,1)) in another blank cell, Cell C2 in this case. 3. Copy those two formulas down and you will see the full name column is split to the first name and last name columns as shown below.

How do I add two text Columns in Excel? ›

Combine text from two or more cells into one cell
  1. Select the cell where you want to put the combined data.
  2. Type = and select the first cell you want to combine.
  3. Type & and use quotation marks with a space enclosed.
  4. Select the next cell you want to combine and press enter. An example formula might be =A2&" "&B2.

How do you concatenate a list in Excel? ›

CONCATENATE Excel Range (Without any Separator)

Select the entire formula and press F9 (this converts the formula into values). Remove the curly brackets from both ends. Add =CONCATENATE( to the beginning of the text and end it with a round bracket). Press Enter.

Videos

1. Excel Alternating Row Color - Shading Alternating 4 Rows
(ExcelDemy)
2. Resizing Moving Copying Deleting and Printing Charts in Excel
(ExcelDemy)
3. Excel Conditional Formatting - Highlight Cells Rules
(ExcelDemy)
4. Excel Conditional Formatting Based on Another Cell - Highlighting Rows
(ExcelDemy)
5. Use Text to Columns Convert Dates Stored as Text
(Doug H)
6. Creating and Customizing an Excel Chart
(ExcelDemy)

Top Articles

You might also like

Latest Posts

Article information

Author: Patricia Veum II

Last Updated: 10/01/2022

Views: 5950

Rating: 4.3 / 5 (64 voted)

Reviews: 95% of readers found this page helpful

Author information

Name: Patricia Veum II

Birthday: 1994-12-16

Address: 2064 Little Summit, Goldieton, MS 97651-0862

Phone: +6873952696715

Job: Principal Officer

Hobby: Rafting, Cabaret, Candle making, Jigsaw puzzles, Inline skating, Magic, Graffiti

Introduction: My name is Patricia Veum II, I am a vast, combative, smiling, famous, inexpensive, zealous, sparkling person who loves writing and wants to share my knowledge and understanding with you.