How To Split Data In A Cell In Excel

Bottom Line: Learn how to use formulas and functions in Excel to split full names into columns of offset and final names.

Skill Level: Intermediate

Watch the Tutorial

Scout on YouTube & Subscribe to our Aqueduct

Download the Excel Files

You tin download both the earlier and after files below. The earlier file is and then yous can follow along, and the after file includes all of the formulas already written.

Split-Names-with-Formulas-Begin.xlsx

Dissever-Names-with-Formulas-Final.xlsx

Splitting Text Into Separate Columns

Nosotros've been talking nigh various means to accept the text that is in one column and divide it into two. Specifically, we've been looking at the mutual example of taking a Total Name column and splitting it into Beginning Name and Last Name.

Split text in cells into separate columns

The showtime solution we looked at used Ability Query and y'all can view that tutorial hither: How to Split Cells and Text in Excel with Power Query. And so we explored how to use the Text to Columns feature that'due south built into Excel: Split Cells with Text to Columns in Excel. Today, I want to evidence you how to accomplish the same matter with formulas.

Using iv Functions to Build our Formulas

To split our Full Proper name column into Start and Terminal using formulas, nosotros need to utilise iv different functions. Nosotros'll be using SEARCH and LEFT to pull out the first proper noun. Then we'll apply LEN and Right to pull out the last name.

The SEARCH Part

They key to breaking up the first names from the last names is for Excel to identify what all of the total names take in common. That common factor is the space grapheme that separates the two names. To assistance our formula identify everything to the left of that infinite character equally the first name, we need to utilise the SEARCH function.

The SEARCH function returns the number of the character at which a specific graphic symbol or text string is found, reading left to correct. In other words, what number is the space character in the line of characters that brand up a full proper noun? In my name, Jon Acampora, the infinite character is the 4th character (after J, o, and northward), then the SEARCH office returns the number 4.

At that place are three arguments for SEARCH.

The first argument for the SEARCH function is find_text. The text nosotros desire to detect in our entries is the infinite character. So, for find_text, we enter " ", being sure to include the quotation marks.
The 2nd argument is within_text. This is the text we are searching in for the space character. That would be the cell that has the full name. In our example, the beginning cell that has a full name is A2. Since nosotros are working with Excel Tables, the formula will re-create down and change to B2, C2, etc., for each respective row.
The third and final argument is [start_num]. This argument is for cases where you desire to ignore a certain number of characters in the text earlier first your search. In our instance, we desire to search the entire text, kickoff with the very first character, so we practice not need to ascertain this argument.

All together, our formula reads: =SEARCH(" ",A2)

SEARCH function to identify space character

I started with the SEARCH function considering it will exist used equally one of the arguments for the next office we're going to look at. That is the LEFT office,

The LEFT Function

The LEFT office returns the specified number of characters from the start of a text string. To specify that number, we will use the value nosotros just identified with the SEARCH function. The LEFT function will pull out the letters from the left of the Full Name column.

The LEFT part has two arguments.

The first statement is text. That is just the cell that the function is pulling from—in our instance A2.
The second statement is [num_chars]. This is the number of characters that the office should pull. For this argument, we will use the formula we created to a higher place and subtract ane from information technology, considering we don't desire to actually include the space grapheme in our results. And then for our example, this argument would be SEARCH(" ",A2)-1

All together, our formula reads =LEFT(A2,SEARCH(" ",A2)-1)

LEFT function pulls the leftmost characters

Now that we've extracted the first name using the LEFT function, you can guess how we're going to use the RIGHT function. It volition pull out the final name. But before we go at that place, permit me explain i of the components that we will need for that formula. That is the LEN function.

The LEN Office

LEN stands for LENGTH. This function returns the number of characters in a text cord. In my name, there are 12 characters: 3 for Jon, 8 for Acampora, and one for the space in between.

There is merely one argument for LEN, and that is to place which text to count characters from. For our example, nosotros again are using A2 for the Full Proper name. Our formula is simply =LEN(A2)

LEN function counts the number of characters

The Right Part

The Correct formula returns the specified number of characters from the end of a text string. RIGHT has two arguments.

The first argument is text. This is the text that it is looking through in order to return the right characters. Just as with the LEFT office to a higher place, nosotros are looking at cell A2.
The second statement is [num_chars]. For this statement we want to subtract the number of characters that we identified using the SEARCH function from the total number of characters that we identified with the LEN office. That volition give us the number of characters in the last name.

Our formula, all together, is =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

RIGHT function pulls the rightmost characters

Note that nosotros did non subtract ane like nosotros did before, because we want the space character included in the number that is beingness deducted from the total length.

Pros and Cons for Using Formulas to Split Cells

The i outstanding advantage to this method for splitting text is the automated updates. When edits, additions, or deletions are made to the Full Proper name column, the First and Terminal names change besides. This is a big benefit compared to using Text to Columns, which requires yous to completely repeat the procedure when changes are made. And even the Power Query method, though much simpler to update than Text to Columns, all the same requires a refresh to be manually selected.

Changes automatically update using formula method

Of course, one obvious disadvantage to this technique is that fifty-fifty though each of the iv functions are relatively simple to understand, information technology takes some thought and time to combine them all and create formulas that work correctly. In other words, is non the easiest solution to implement of the three methods presented thus far.

Another disadvantage to consider is the fact that this merely works for scenarios that have two names in the Total Name column. If you take data that includes centre names or two kickoff names, the formula method isn't helpful without making some considerable modifications to the formulas. (Homework challenge! If yous'd similar to give that a try, please practice so and permit usa know your results in the comments.) Every bit we saw in the Ability Query tutorial, yous do in fact accept the capability to pull out more than 2 names with that technique.

Means to Split Text

Here are the links to the other posts on ways to dissever text:

Separate Cells with Text to Columns in Excel
How to Split Text in Cells with Flash Fill up in Excel
How to Separate Cells and Text in Excel with Power Query
Split past Delimiter into Rows (and Columns) with Power Query

Conclusion

I promise you've learned something new from this post and that is helpful equally you split information into carve up columns. If y'all accept questions or remarks, please leave a comment below!