How to Separate Words Using Formula in Excel

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:

Separate Words by Delimiter Using Excel Formula

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

Here, we applied the 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)

In this formula, we applied the 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.

Separate Words by Delimiter Using Excel Formula

Lastly, we used the 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.

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.

Separate Words by Delimiter Using Excel Formula

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.

Separate Words by Delimiter Using Excel Formula

Here, we used the 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.


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

Apply Excel Formula to Separate Words in Different Names

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

Apply Excel Formula to Separate Words in Different Names

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


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

Separate Words by Delimiter Using Excel Formula

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

Separate Words by Delimiter Using Excel Formula

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

Separate Words by Delimiter Using Excel Formula

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

How to Separate Words in Excel Using Formula


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

Separate Words by Delimiter Using Excel Formula

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

Separate Words by Delimiter Using Excel Formula

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

Separate Words by Delimiter Using Excel Formula

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


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

Separate Words by Delimiter Using Excel Formula

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


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

Separate Words by Delimiter Using Excel Formula

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

Separate Words by Delimiter Using Excel Formula

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

Separate Words by Delimiter Using Excel Formula

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

Separate Words by Delimiter Using Excel Formula

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

Separate Words by Delimiter Using Excel Formula

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

Separate Words by Delimiter Using Excel Formula

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


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.

Split Numbers from Text with Formula in Excel

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.

Insert Excel Formula to Split Words from Line Break

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)

Here, first, we used the 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)

Here, the 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.

Here, first, we used the 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.


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.

Siam Hasan Khan

Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

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

  2. 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 Lara
      To 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.

Leave a reply

ExcelDemy
Logo