How to Split Cells Using Excel Formula – Step by Step

Hi friends,

Splitting cell content was a challenge but using Text to Columns and using Flash Fill we can now split the cell content in the next columns.

It was easy, isn’t it?

But sometimes it is the situation’s call that we must use formula to split the cell content (or better to say to extract specific data) and spread it over the next columns.

Honestly speaking, if you want to master Microsoft Excel, you must learn to solve the problems using formulas.

In this tutorial or article we will discuss how to split cell content in the next columns.

How to separate text in excel using formula?

or

How to separate first name, last name and date of birth in excel?

Suppose we got a table containing few names and their birthdays like ‘Jacob Elordi - June 26, 1997’ and in next row ‘Joey king - July 30, 1999’ and so on and we need to get the first name, last name and their date of births in the next columns.

Note : Separators can be commas, dots, hashes etc, in place of spaces but the excel formula will be the same, just replace space by that character. 

How to do it?

Let us solve this problem Step by Step.

1.     If we analyze the first row containing “Jacob Elordi - June 26, 1997”, we can understand that there are two separators, spaces and hyphen

2.     If we can identify the position (number) of the spaces and the hyphen, then we can get the desired texts easily using some TEXT functions.

What functions to use to split the cell text and spread over the next columns?

3.     We will use functions, SEARCH function and LEFT function, RIGHT function and MID function

Here SEARCH function will give us the position of the space and hyphen.

How to get First Name using formula in Excel?

Steps :

1.     What is the output of SEARCH function? It gives us the position of a specific character in a text or string, like =SEARCH(" ",C3,1); output is ‘6’ for the first row. (Here we are searching for space.)

2.     Now we will use LEFT function and we need only one number, the position of first “space”, but =SEARCH(" ",C3,1) formula gives us the position of the first occurrence of space, we need to less one to get the number of characters in first name, therefore, our excel formula becomes =LEFT(C3,SEARCH(" ",C3,1)-1) the output is ‘Jacob’, the first name in the First Name column.

We did the first task !

Split Cell Content using Excel Formula - Functions LEFT & SEARCH

How to get Last Name using formula in Excel?

This is a little complex, but think logically and enter the formula step by step, you will get the Last name.

Steps :

1.     We got the position of first space as 6 in =SEARCH(" ",C3,1) formula. The next task is to find out the second occurrence of space. How to get the next occurrence of space?

2.     Use nested SEARCH function as

=SEARCH(" ",C3, SEARCH(" ",C3,1)).

Explanation :

The first SEARCH function is searching for space but the starting number in this function should be 6 (the position of first space), this number is provided by the next SEARCH function colored in RED and we used it in our previous task. If we less 1 from this output, we will get the number of characters in the last name i.e., SEARCH(" ",C3,SEARCH(" ",C3,1)-1) ; output is 6.

3.     Now we know the starting position and the number of character in the last name part. We can use MID function to extract the last name because MID function requires text, start number and number of characters and our final formula using MID function is,

=MID(C3,SEARCH(" ",C3,1)+1,SEARCH(" ",C3,SEARCH(" ",C3,1)-1))

In this formula, ‘+1’ is used to get the position of the first character in the last name after first space and ‘-1’ is used to get the number of characters’ present in the last name before second space.

Split Cell Content using Excel Formula - Functions MID & SEARCH

How to get Date of Birth using formula in Excel?

This is easy, very similar to the method we use to get the first name, the difference is that we will use RIGHT function, as it is logical and easy to get the data of birth from the right side rather than traversing from left side.

Steps :

1.     We will search for hyphen in the given text using = SEARCH("-",C3,1), which gives us the position of hyphen from the left side, therefore, we will less one (as ‘-1’) to get the number of characters in the date of birth.

2.     Our excel formula to get the date of birth from the text is =RIGHT(C3,SEARCH("-",C3,1)-1)

Split Cell Content using Excel Formula - Functions RIGHT & SEARCH

I hope you have enjoyed this article.

Thank you for reading… Take care…