Separating words are important in Excel to extract information from your worksheet. There are many methods of doing that. Here, in this article, we will see “How to Separate Words in Excel Using Formula”. We will use many different examples to demonstrate the processes like separate words by the delimiter, separating text from numbers, separate different format names from a text string etc.

Table of Contents

## Separate Words by Delimiter using Formula

In this example, we will work with words with the delimiters. Suppose, we have a list of Items along with other information like color and size. These are written in a cell with combinations of the colon. Our goal is to separate these words in different columns to make the information more precise. Let`s see the below table.

In the above table, you can see the description of different items. Here, we create extra 3 columns to extract the exact information in each specific column from the **Item Description **column.

### Extracting Items from Text Strings

First of all, we are going to extract the item names from the Item Description. The process of doing this is given below,

- Write down the formula,

in cell C2.**=LEFT(B2,SEARCH("-",B2,1)-1)**

- Now press
**Enter**. You will see the item name from cell B2 appears in cell C2**.**

- Drag the formulated cell C2 downwards to copy this formula for the rest of the cells of
**Item**column.

### Extracting Colors name from the Text Strings

Now, to extract the colors name follow the below steps.

- First, write down the formula,

in cell D2.**=MID(B2, SEARCH("-",B2) + 1, SEARCH("-",B2,SEARCH("-",B2)+1) - SEARCH("-",B2) - 1)**

- After pressing
**Enter**you will get to see the below result.

Drag the formulated cell D2 downwards to copy this formula for the rest of the **Color** column.

### Extracting Size from the Text String

To extract Size we will use a different formula that uses RIGHT, LEN, and SEARCH functions together. The procedure for this is given below.

- Write the formula

in cell E2.**=RIGHT(B2,LEN(B2)-SEARCH("-",B2, SEARCH("-",B2) + 1))**

- After pressing
**Enter**you will get the below result.

- Like earlier, drag the formulated cell downwards to copy this formula for the rest of the cells and you will get the below result.

## Separating Text from Numbers

In many cases, you will find text with numbers and from that, you may need the separate the texts. There are few formulas for doing that. Let’s look into the below table. From this table, we will extract the cities name and postcodes separately.

### Extracting Words and Numbers by counting Characters

One of the processes of doing that is counting the total digits in a cell and from them extracting the number and word that you want.

- In cell
**D2,**write down the formula**,**and press Enter.`=RIGHT(B2,SUM(LEN(B2) - LEN(SUBSTITUTE(B2, {"0","1","2","3","4","5","6","7","8","9"},""))))`

- Drag this formulated cell downwards for the rest of the cells of column
**Post Code.**

- Now to extract Cities name write down the formula,

in cell C2 and press**=LEFT(B2,LEN(B2)-LEN(D2))****Enter**.

- By dragging this cell downwards you will get all the cities name in the
**City**column.

### Finding the position of the First digit of a String

This process involves finding the position of the 1^{st} digit of a number in a cell. There will be a separate column to find the position of the 1^{st} digit.

- In cell C2, write down the formula

and press**=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789"))****Enter**. After this double-click, the**Fill Handle**option or drag down the formulated from C2 to C8 to copy this formula for the rest of the cell. You will get to see the below result.

- Now to extract cities name write the formula,

in cell D2 and copy this formula for the rest of the cells of Column**=LEFT(B2, C2-1)****City**. You will get the below result after that.

- For extracting the numbers use the formula

in cell E2 and drag it down for the rest of the cells of column**=RIGHT(B2, LEN(B2)-C2+1)****Postcode.**

## Separate Words in Excel Using Formula (Working With Different Names)

In this example, we will be working different format names. From this names, we will be extracting first names, middle names, last names, suffixes of names. Let`s look into the below picture so that you will get an idea of what we are going to do.

### Separating Names from a Cell Which Has “No Middle Names”

Here we are going to extract the 1^{st} and last name from a cell which has no middle names. Look into the below table to get a clear idea about the formulas and the results.

Full Name | Rupom Chowdhury |

Description | No Middle Names |

Formula For the First Name | `=LEFT(B2, SEARCH(" ",B2,1))` |

First Name Result | Rupom |

Formula For the Last Name | `=RIGHT(B2,LEN(B2)-SEARCH(" ",B2,1))` |

Last Name Result | Chowdhury |

### Extracting Names with “1 Middle Name”

In this example, you are going to see the extraction of 3 names from a cell. Follow the procedure given in the below table for getting the desired result.

Full Name | Niloy N. Costa |

Description | 1 Middle Name |

Formula For the First Name | `=LEFT(B2, SEARCH(" ",B2,1))` |

First Name Result | Niloy |

Formula For the Middle name | `=MID(B2,SEARCH(" ",B2,1)+1,SEARCH(" ",B2,SEARCH(" ",B2,1)+1)-SEARCH(" ",B2,1))` |

Middle Name Result | N. |

Formula for the Last Name | `=RIGHT(B2,LEN(B2)-SEARCH(" ",B2,SEARCH(" ",B2,1)+1))` |

Last Name Result | Costa |

### Extracting Names with “2 Middle Names”

This example shows the extraction of names having 2 middle names. The first and last names will also be extracted along with the two middle names.

Full Name | Ahmed J.H. Likhon |

Description | 2 Middle Names |

Formula For the First Name | `=LEFT(B2, SEARCH(" ",B2,1))` |

First Name Result | Ahmed |

Formula For the Middle name | `=MID(B2,SEARCH(" ",B2,1)+1,SEARCH(" ",B2,SEARCH(" ",B2,SEARCH("",B2,1)+1)+1)-SEARCH(" ",B2,1))` |

Middle Name Result | J.H. |

Formula for the Last Name | `=RIGHT(B2,LEN(B2)-SEARCH(" ",B2,SEARCH("",B2,SEARCH(" ",B2,1)+1)+1))` |

Last Name Result | Likhon |

### Separating Names from USA Name Format (Comma with the Last Name in 1st place)

If any name is written in the USA name format you can extract the first, last and middle names by following the formulas stated in the below table.

Full Name | Khan, Siam Hasan |

Description | Comma with the Last Name in 1^{st} Place |

Formula For the First Name | `=MID(B2,SEARCH(" ",B2,1)+1,SEARCH(" ",B2,SEARCH(" ",B2,1)+1)-SEARCH(" ",B2,1))` |

First Name Result | Siam |

Formula For the Middle name | `=RIGHT(B2,LEN(B2)-SEARCH(" ",B2,SEARCH(" ",B2,1)+1))` |

Middle Name Result | Hasan |

Formula for the Last Name | `=LEFT(B2, SEARCH(" ",B2,1)-2)` |

Last Name Result | Khan |

## Separating Names from Text string with “2 First Names”

To extract names with the first name having 2 names follow the formula stated in the below table.

Full Name | Syed Mufrad Nabi Swapnil |

Description | 2 First Names |

Formula For First Name | `=LEFT(B2, SEARCH(" ",B2,SEARCH(" ",B2,1)+1))` |

First Name Result | Syed Mufrad |

Formula For the Middle name | `=MID(B2,SEARCH(" ",B2,SEARCH(" ",B2,1)+1)+1,SEARCH(" ",B2,SEARCH(" ",B2,SEARCH(" ",B2,1)+1)+1)-(SEARCH(" ",B2,SEARCH(" ",B2,1)+1)+1))` |

Middle Name Result | Nabi |

Formula for the Last Name | `=RIGHT(B2,LEN(B2)-SEARCH(" ",B2,SEARCH(" ",B2,SEARCH(" ",B2,1)+1)+1))` |

Last Name Result | Swapnil |

### Separating Names from Text string with “3 Last Names”

Use the formula given in the below table to extract first and last name which has 3 names as the last name.

Full Name | Syed Waliun Nabi Ony |

Description | 3 Last Names |

Formula For the First Name | `=LEFT(B2, SEARCH(" ",B2,1))` |

First Name Result | Syed |

Formula For the Last Name | `=RIGHT(B2,LEN(B2)-SEARCH(" ",B2,1))` |

Last Name Result | Waliun Nabi Ony |

### Separating Names from Text string with “2 Last Names”

Use the formula given in the below table to extract first and last name which has 2 names as last name.

Full Name | Tasfiq Chowdhury Pranto |

Description | 2 Last Names |

Formula For the First Name | `=LEFT(B2, SEARCH(" ",B2,1))` |

First Name Result | Tasfiq |

Formula For the Last Name | `=RIGHT(B2,LEN(B2)-SEARCH(" ",B2,1))` |

Last Name Result | Chowdhury Pranto |

### Separating names with “Suffix”

Names which contain suffix can be extracted by using the formulas stated in the below table.

Full Name | King Robert III |

Description | Suffix |

Formula For the First Name | `=LEFT(B2, SEARCH(" ",B2,1))` |

First Name Result | King |

Formula For the Last name | `=MID(B2,SEARCH(" ",B2,1)+1,SEARCH(" ",B2,SEARCH(" ",B2,1)+1)-(SEARCH(" ",B2,1)+1))` |

Last Name Result | Robert |

Formula for Suffix | `=RIGHT(B2,LEN(B2)-SEARCH(" ",B2,SEARCH(" ",B2,1)+1))` |

Suffix Result | III |

### Separating Names without “Prefix”

To ignore prefix in a name while extracting the First and last name you can use the below formulas.

Full Name | Mr. Ashiqur Rahman |

Description | Names with Prefix |

Formula For the First Name | `=MID(B2,SEARCH(" ",B2,1)+1,SEARCH(" ",B2,SEARCH(" ",B2,1)+1)-(SEARCH(" ",B2,1)+1))` |

First Name Result | Ashiqur |

Formula For the Last Name | `=RIGHT(B2,LEN(B2)-SEARCH(" ",B2,SEARCH(" ",B2,1)+1))` |

Last Name Result | Rahman |

### Separating names with “Hyphenated last Names”

Some people names contain hyphens. It is difficult sometimes to extract the hyphenated names. You can easily extract the hyphenated last name by using the formula in the table.

Full Name | Hasan Uz-Zaman |

Description | Name with Hyphenated last name |

Formula For the First Name | `=LEFT(B2, SEARCH(" ",B2,1))` |

First Name Result | Hasan |

Formula For the Last Name | `=RIGHT(B2,LEN(B2)-SEARCH(" ",B2,1))` |

Last Name Result | Uz-Zaman |

## Download The Working File

## Conclusions

As we can see there are a lot of methods to separate words in Excel using formula. For extracting words in different column Excel has a feature named **Text to Column**. When you want to extract names from a whole column to different columns you can easily use the Text to Column feature but while working on some specific cells you need to use the formula to extract words.

Hope you will find this article useful. Follow us for more useful articles.

## Our Recommended Excel Courses

- Microsoft Excel – Advanced Excel Formulas & Functions (Affiliate link)
- Excel Formulas and Functions: Make Basic & Advanced Formulas (Free for my readers)
- Microsoft Excel – Data Analysis with Excel Pivot Tables (Affiliate link)

## Related Articles

- How to Split Cells in Excel (The Ultimate Guide)
- Text to Columns in Excel (Split Column, Text, Date, …)
- Separate, Split & Remove Substring & Number from Text with Excel Functions & VBA
- Splitting Text in Excel Using Flash Fill
- How to split text into multiple cells in Excel
- Creating Email Addresses from a Single Column with Flash Fill, TEXT Formulas & Commentator’s Text Formula Suggestions

I like very much your way of teaching excel, which is very useful tips often.

.

Nice to hear that it helped you. Thanks and regards.

What if we have both a prefix and a middle name?