The formula counts the number of the space characters if there is only one, it uses the exact formula we introduced for extracting the last name before if there are two spaces, it locates the second one and splits the next characters. In this formula, the IF function checks the existence of a middle name. If our list includes middle names, we have to combine the above functions with IF and SUBSTITUTE functions. This formula locates the space character and splits the characters after it. To extract the last names, we use a combination of the RIGHT, FIND, and LEN functions. Note: This formula works for the previous example too. In this formula, the FIND function finds the space character’s location as a number and gives it to the LEFT function to split the first name from cell A2. To extract the first names, we can use the combination of the LEFT function and the FIND function.
#How to split cells in excel how to
This example shows how to extract the first names from a list containing first names and last names. Now that we know the text functions we need, we can directly go to the examples. The only difference is that the FIND function is case-sensitive, but the SEARCH function is not. Note: Excel SEARCH function does the same thing as the FIND function. SUBSTITUTE: It replaces one or more text strings with another one.It returns the number of characters starting from the position you specify. MID: It extracts a substring from a text string.LEN: It returns the total number of characters in a text string.RIGHT: It extracts a specific number of characters from the right side of a string.It returns the starting position of the sub-string as a number. FIND: We use it to find a string inside another one.LEFT: It extracts a specific number of characters from the left side of a string.Here are the text functions that we can use to split cells in Excel: You must know how to use these functions and how to combine them. Excel text functions work great when you want to split cells. The last method to split cells is using Excel text functions. Note: If you don’t want to use shortcuts, you can go to the Data tab and click on Flash Fill from the Data Tools group. Do the same thing for the last names and ID numbers.Press “ Ctrl+E,” which is the shortcut for the flash fill option.In Cell B2, type the corresponding first name (i.e., Matilda).All you need to do is to follow these steps: Suppose that you want to extract the first names, last names, and ID numbers from the first column and put them in the next columns. To split cells in the previous example, we just need to write the part of the text that we want splitted in the desired cell, then use the flash fill feature. Splitting Cells Using the Flash Fill FeatureĪnother easy way to split cells in Excel is using the flash fill option. Note: If you leave the destination as default, Excel will keep the first column where it is and move the rest to the next columns. Having set the information, click Finish. Change the destination to $B$2 by typing it or clicking on the icon and selecting a range on the spreadsheet. Step 3of 3: At this stage, you can specify the data format and the data destination.You can see the result in the Data preview section. Also, you’d better check the “ Treat consecutive delimiters as one” box. Step 2 of 3: Since our data are separated by space and comma, you must choose both as delimiters and then click Next.Choose the “Delimited” option, which is the character by which you specify to split cells and then click Next. Step 1 of 3: At this step, you have 2 options: Delimited and Fixed Width.The “Convert text to column wizard” window will open. Go to the Data tab and choose “ Text to Column” from the Data Tools group.ģ.Select the cells you want to split the data.You can split these data following these steps: Suppose that you have a spreadsheet containing some information about a group of people, including first name, last name, and ID number and you want to put this information in separate cells. Splitting Cells Using Text to Column Feature In this tutorial, we are going to explain three methods to split cells in Excel. At times like this, you need to know how to split cells in Excel! There sure have been times when you entered a lot of data in single cells under a column and decided later that all the information in those single cells better be divided into different cells under multiple columns. Comfortable and satisfying as it is, working with Excel can sometimes be confusing.