Siam Hasan Khan
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.
- Following, insert this formula in cell D5 and hit Enter.
=MID(B5, SEARCH("-",B5) + 1, SEARCH("-",B5,SEARCH("-",B5)+1) - SEARCH("-",B5) - 1)
- 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.
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.
- 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)
- 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)
- 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.
- 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 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.