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 using the formula** in **Excel** with 4 essential examples. We will show the process of separating words by the delimiter, separating text from numbers, and separating different format names from a text string.

## Download Practice Workbook

Download this practice file and try the methods by yourself.

## 4 Essential Examples to Separate Words Using Formula in Excel

To describe the process of separating words with formulas, we will use 4 different types of datasets for better understanding. So, without any delay, let’s see the examples.

### 1. Separate Words by Delimiter Using Excel Formula

A **delimiter **is a symbol or sign that separates each text in the dataset. It can be a **Comma** (**,**), **Dash **(**–**) etc. In this section, we prepared a dataset of **5** types of item names with their** Color** and** Size**. Each is separated by delimiters. Now, let’s follow the example below to split the words.

#### 1.1. Combine LEFT, MID, RIGHT, LEN & SEARCH Functions

In this first example, we will use** the LEFT**,** MID**, **RIGHT**,** LEN **and **SEARCH** **functions** to separate words by delimiters.

- First, prepare a dataset like an image below:

- Now, insert this formula in
**cell C5**.

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

- Then, hit
**Enter**. - After this, use the
**Autofill**tool to get the separated Item names in**cell range C6:C8**.

**SEARCH**function to find the delimiter and the

**LEFT**function to get a single text from the left-hand side of

**cell B5**.

- Following, insert this formula in
**cell D5**and hit**Enter**.

`=MID(B5, SEARCH("-",B5) + 1, SEARCH("-",B5,SEARCH("-",B5)+1) - SEARCH("-",B5) - 1)`

**MID**and

**SEARCH**functions to find the delimiter and insert the middle value from

**cell B5**.

- Lastly, apply this formula in
**cell E5**and press**Enter**.

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

- Now, select
**cells D5**and**E5**and use the**AutoFill**tool up to**cell E8**to get individual**Colors**and**Size**.

**SEARCH**function to find the delimiter in

**cell B5**and the

**RIGHT**function to get the right side value. Also, we applied the

**LEN**function to get the text string length.

**Read More:** **How to Split String by Length in Excel (8 Ways)**

#### 1.2. Insert TRANSPOSE Function

Another method to separate words having delimiters is to use **the TRANSPOSE function** in excel. Here, the words are connected by **Commas**.

Now, follow the steps below.

- First, insert this formula in
**cell C5**.

`=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(B5,",","</s><s>") & "</s></t>","//s"))`

- Then, hit
**Enter**. - That’s it, you will see the word is automatically separated into
**3 pieces**like this.

**TRANSPOSE**function to create an array for

**splitting the text**. Then, we insert

**the FILTERXML function**for extracting individual items from

**cell B5**. Lately, we used

**the SUBSTITUTE function**to replace text based on the dataset.

- Lastly, use the
**Flash Fill**command to get output in**cell range C6:E8**.

**Read More:** **How to Split a String into an Array in VBA (3 Ways)**

### 2. Apply Excel Formula to Separate Words in Different Names

In this example, we will be working with different format names. From these names, we will be extracting first names, middle names, last names, and suffixes of names.

#### 2.1. Separate Words with No Middle Names

In this example, we will separate words that have **only first and last names**.

- In the beginning, insert this formula into
**cell C5**.

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

- Then, hit
**Enter**. - Following, use the
**Autofill**tool to get the first names in**cell range C6:C7**.

- Next, insert this formula in
**cell D5**.

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

- Now, just like before, get the last names with the
**Autofill**tool like this.

**Read More:** **How to Split Text in Excel Using Formula (5 Easy Ways)**

#### 2.2. Extract Words with 1 Middle Name

Let us assume, we have a list of names having **1 middle name**. Now, how will you separate them? Let’s check the steps below.

- First, insert this formula in
**cell C5**.

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

- Then, hit
**Enter**and apply this formula in**cells C6**and**C7**to get all the first names.

- Then, put this formula in
**cell D5**to get the middle name.

`=MID(B5,SEARCH(" ",B5,1)+1,SEARCH(" ",B5,SEARCH(" ",B5,1)+1)-SEARCH(" ",B5,1))`

- Next, hit
**Enter**and apply**Autofill**to get results in**cells D6**and**D7**.

- Lastly, insert this formula in
**cell E5**.

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

- Finally, apply the formula for
**cells E6**and**E7**. - That’s it, you will get the separated names like this.

#### 2.3. Split Texts with 2 Middle Names

At this stage, let us take another scenario where there are **2 middle names**. Let’s see the process to separate the words in this case.

- In the beginning, insert this formula in
**cell C5**to get the first name.

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

- Next, apply this formula in
**cell D5**for getting the middle names.

`=MID(B5,SEARCH(" ",B5,1)+1,SEARCH(" ",B5,SEARCH(" ",B5,SEARCH("",B5,1)+1)+1)-SEARCH(" ",B5,1))`

- Lastly, provide this formula in
**cell E5**for the last name.

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

- That’s it, you have got your first set of separated words.

- Finally, select
**cell range C5:E5**and use the**AutoFill**tool to get the final output.

**Read More: ****How to Split Text in Excel by Character (5 Quick Methods)**

#### 2.4. Separate Texts from 2 First Names

In this case, we will consider names that have **2 first names**. Simply go through the steps to **separate them**

- First, put this formula to split the first names in
**cell C5**.

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

- Then, press
**Enter**. - Afterward, apply the
**Flash Fill**common to get this output.

- Now, let us separate the middle name with this formula in
**cell D5**.

`=MID(B5,SEARCH(" ",B5,SEARCH(" ",B5,1)+1)+1,SEARCH(" ",B5,SEARCH(" ",B5,SEARCH(" ",B5,1)+1)+1)-(SEARCH(" ",B5,SEARCH(" ",B5,1)+1)+1))`

- Following, press
**Enter**>**Autofill**to get the output in**cells D6**and**D7**.

- Lastly, apply this formula to get the last name in
**cell E5**.

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

- Similarly, press
**Enter**>**Autofill**and get the final separated words.

**Read More: ****How to Separate Two Words in Excel (6 Easy Ways)**

#### 2.5. Extract Words Comprising 2 Last Names

Sometimes, you may find some names that have **2 last names**. Therefore, let us show you how to extract them in excel.

- Just like before, first, extract the first name using this formula.

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

- Then, hit
**Enter**. - Following, use the
**Flash Fill**tool to get all the first names in**cells C6**and**C7**.

- Next, apply this formula in
**cell D5**.

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

- Similarly, press
**Enter**>**Autofill**and get the separated words at once.

#### 2.6. Split Names Having 3 Last Names

At this stage, let us find the process to split names that have 3 last names.

- First, insert this formula in
**cell C5**.

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

- Then, press
**Enter**and drag the bottom corner up to**cell C7**to get the first names.

- Next, put this formula for separating 3 last names in
**cell D5**.

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

- Lastly, hit
**Enter**>**Autofill**to get the final output.

#### 2.7. Separate Words with Suffix in Name

Often you may find some names that have **Suffix** in their names. The use of a suffix has come from the **Western English** name tradition. It is applied to provide information about that person by adding the suffix at the end of the name. Let’s see the process of separating this.

- Like before, split the first name with this formula.

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

- Then, press
**Enter**>**Autofill**to get all the first names in**cell range C5:C7**.

- After that, put this formula in
**cell D5**to separate the last name.

`=MID(B5,SEARCH(" ",B5,1)+1,SEARCH(" ",B5,SEARCH(" ",B5,1)+1)-(SEARCH(" ",B5,1)+1))`

- Following, again press
**Enter**and then**Autofill**to get this output below:

- Lastly, insert this formula to extract the suffix from the name in
**cell E5**.

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

- Finally, apply this formula to
**cells C6**and**C7**. - That’s it, you will successfully separate words and suffixes using formulas.

#### 2.8. Separate Hyphenated Last Names

In this section, let us separate names that have** Hyphens **in their last names.

- First, extract the first name in
**cell C5**with this formula.

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

- Then, put this formula to separate the last name in
**cell D5**.

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

- Now, you will see the name in
**cell B5**is separated where the last name has a hyphen.

- Following, select
**cells C5**and**D5**. - Lastly, drag their bottom corner up to
**cell D7**to extract all the words at once.

#### 2.9. Split Words from Name in USA Format

The names in the **USA format** are organised in a way where the last name is placed in the first place. Then it is separated with a comma from the other parts of the names. In this example, we will separate the names according to regular order in spite of them being in the **USA format**.

- First, insert this formula in
**cell C5**to separate the first name.

`=MID(B5,SEARCH(" ",B5,1)+1,SEARCH(" ",B5,SEARCH(" ",B5,1)+1)-SEARCH(" ",B5,1))`

- Then, put this one in
**cell D5**to extract the middle name.

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

- Now, put this formula to extract the last name in
**cell E5**.

`=LEFT(B5, SEARCH(" ",B5,1)-2)`

- That’s it, we can see the name in
**cell B5**is separated in correct order despite being in**USA format**.

- Lastly, use the
**Flash Fill**command to get the result in**cell range C6:E7**.

#### 2.10. Extract Words Without Prefix

In this last example, we will extract words omitting the prefix in their names.

- First, apply this formula to split the first name in
**cell C5**.

`=MID(B5,SEARCH(" ",B5,1)+1,SEARCH(" ",B5,SEARCH(" ",B5,1)+1)-(SEARCH(" ",B5,1)+1))`

- Then, press
**Enter**>**Autofill**to get this output below:

- Next, apply this formula in
**cell D5**.

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

- Again, press
**Enter**>**Autofill**. - Finally, you will see that the names are separated without prefixes.

**Read More:** **How to Split First And Last Name in Excel (6 Easy Ways)**

### 3. Split Numbers from Text with Formula in Excel

At this stage, we will learn how you can split any number in a text string using the excel formula. For example, here is a dataset that has **5 countries’ Post Code**.

Let’s follow the steps below to split the number from this dataset.

- In the beginning, insert this formula in
**cell D5**to separate the number first.

`=RIGHT(B5,SUM(LEN(B5) - LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"},""))))`

- After that, put the formula shown in the
**Formula Bar**in**cell C5**.

`=LEFT(B5,LEN(B5)-LEN(D5))`

- Now, you can see the first
**Post Code**is separated from the country’s name.

- Lastly, use the
**Flash Fill**command to get a similar output in**cell range C6:D9**.

### 4. Insert Excel Formula to Split Words from Line Break

We are almost at the end of our article. In this last example, we will split words that are placed in several lines in a single cell. It is quite an interesting and helpful process. To demonstrate it, here we prepared a dataset that has 3 person’s **Names & Addresses**. The information is shown with line breaks.

Now, follow the steps below to split these words from the line break.

- First, insert this formula in
**cell C5**.

`=LEFT(B5, SEARCH(CHAR(10),B5,1)-1)`

**LEFT**function to extract the left side value. Then applied the

**SEARCH**function to return the position as a number in the text string. Lastly, we used

**the CHAR function**to return a character specified by a number in

**cell B5**.

- Then, apply this formula in
**cell D5**.

`=MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1)`

**MID**function is used to extract the value from the middle line in

**cell B5**. Then applied the

**SEARCH**function to return the position in the text string. Lastly, we used the

**CHAR**function to return a character specified by a number.

- Lastly, insert this formula in
**cell E5**.

`=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10),B5) + 1))`

- Therefore, here is the output against
**cell B5**.

**RIGHT**function to pull out the right side value. Then applied the

**SEARCH**function to find the position as a number in the text string and the

**LEN**function to determine the length of that string. Lastly, we used the

**CHAR**function to return a character specified by a number in

**cell B5**.

- Follow the same procedure and you will see the final set of separated words.

**Read More:** **How to Split Text by Number of Characters in Excel (7 Ways)**

## Conclusion

Henceforth, we have to come to the end of our long article. I hope you will find it really helpful how to separate words using the formula in Excel with 4 essential examples. Let us know your suggestions in the comment box. Follow **ExcelDemy** for more excel blogs.

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?

Hi Stevoisiak! Hope you are doing well.

Suppose you have the following name in

cell B5.Mr. Brian Charles LaraTo extract all the parts of this name, use the following formulas.

To separate

prefix:`=LEFT(B5,SEARCH(" ",B5)-1)`

To separate

first name:`=MID(B5,SEARCH(" ",B5)+1,SEARCH(" ",B5,SEARCH(" ",B5)+1)-(SEARCH(" ",B5)+1))`

To separate

middle name:`=MID(B5,SEARCH(" ",B5,SEARCH(" ",B5,1)+1)+1,SEARCH(" ",B5,SEARCH(" ",B5,SEARCH(" ",B5,1)+1)+1)-(SEARCH(" ",B5,SEARCH(" ",B5,1)+1)+1))`

To separate

last name:`=RIGHT(B5,LEN(B5)-FIND("^",SUBSTITUTE(B5," ","^",LEN(B5)-LEN(SUBSTITUTE(B5," ","")))))`

You can check the following article too, to know more.

https://www.exceldemy.com/separate-first-middle-last-name-with-excel-formula/

Best Regards.