How to Separate Words in Excel Using Formula (Ultimate Guide)

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 separating words by the delimiter, separating text from numbers, separate different format names from a text string, etc.

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 into 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, <code>=LEFT(B2,SEARCH(“-“,B2,1)-1) in cell C2.

How to Separate Words in Excel Using Formula

  • 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 the Item column.

Extracting Colors name from the Text Strings

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

  • First, write down the formula,<code> =MID(B2, SEARCH(“-“,B2) + 1, SEARCH(“-“,B2,SEARCH(“-“,B2)+1) – SEARCH(“-“,B2) – 1) in cell D2.

split text in excel formula

  • 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 <code>=RIGHT(B2,LEN(B2)-SEARCH(“-“,B2, SEARCH(“-“,B2) + 1)) in cell E2.

split text in excel formula

  • 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, =RIGHT(B2,SUM(LEN(B2) - LEN(SUBSTITUTE(B2, {"0","1","2","3","4","5","6","7","8","9"},"")))) and press Enter.

how to separate text and numbers in excel

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

  • Now to extract Cities name write down the formula, <code>=LEFT(B2,LEN(B2)-LEN(D2)) in cell C2 and press Enter.

how to separate text and numbers in excel

  • By dragging this cell downwards you will get all the cities names in the City column.

Finding the position of the First digit of a String

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

  • In cell C2, write down the formula <code>=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&”0123456789″)) and press 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, <code>=LEFT(B2, C2-1) in cell D2 and copy this formula for the rest of the cells of Column City. You will get the below result after that.

how to separate text and numbers in excel

  • For extracting the numbers use the formula <code>=RIGHT(B2, LEN(B2)-C2+1) in cell E2 and drag it down for the rest of the cells of column Postcode.

how to separate text and numbers in excel

Separate Words in Excel Using Formula (Working With 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, suffixes of names. Let`s look into the below picture so that you will get an idea of what we are going to do.

How to Separate Words in Excel Using Formula

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

Here we are going to extract the 1st 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

How to Separate Words in Excel Using Formula

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 1st 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

How to Separate Words in Excel Using Formula

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

How to Separate Words in Excel Using Formula

Separating Names from Text string with “3 Last Names”

Use the formula given in the below table to extract the 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 the first and last name which has 2 names as the 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 that 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

How to Separate Words in Excel Using Formula

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’s 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

How to Separate Words in Excel Using Formula

Download The Working File

Conclusions

As we can see there are a lot of methods to separate words in Excel using formulas. For extracting words in different columns 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

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

Related Articles

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

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.

3 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?

Leave a reply

ExcelDemy
Logo