- #Excel split cells long text into multiple columns how to
- #Excel split cells long text into multiple columns update
Note: These formula works well when you have names that have either the fist name and last name only, or the first, middle, and last name. If there is only 1 space character, it simply returns all the text to the right of the space character.īut if there are 2, then it spots the second space character and returns the number of characters after the second space. This formula checks whether there is a middle name or not (by counting the number of space characters). To avoid the error, it is wrapped within the IFERROR function. In cases there is no middle name, the MID function returns an error. MID function starts from the first space character and extracts the middle name by using the difference of the position of the first and the second space character. It simply looks for the first space character and returns all the characters before the space. This is the same formula we used when there was no middle name. To get the first name: =LEFT(A2,SEARCH(" ",A2)-1) The formula in such cases is a bit complex. There may be cases when you get a combination of names where some names have a middle name as well. Splitting Names that have a First Name, Middle Name, and Last Name
#Excel split cells long text into multiple columns how to
Click here to learn how to remove leading/trailing/double spaces in Excel. Note: These functions may not work well if you have leading, trailing or double spaces in the names. This last name is then extracted by using the RIGHT function. This gives the number of characters in the last name. It then subtracts that number from the total length of the name (that is given by the LEN function). This formula uses the search function to find the position of the spacebar using the SEARCH function. Similarly, to get the last name, use the following formula: =RIGHT(A2,LEN(A2)-SEARCH(" ",A2)) It then extracts all the characters to the left of it by using the LEFT function. In the case of Bruce Wayne, the space character is in the 6th position. This formula uses the SEARCH function to get the position of the space character. This formula would spot the first space character and then return all the text before that space character: To get the first name from this list, use the following formula: =LEFT(A2,SEARCH(" ",A2)-1) Suppose you have the same data as shown below: Splitting Names that have a First Name and Last Name
#Excel split cells long text into multiple columns update
While the Text to Column feature gives a static result, the result that you get from using functions is dynamic and would automatically update when you change the original data. Split Cells in Excel Using Text FunctionsĮxcel Text functions are great when you want to slice and dice text strings. This means that if there are any changes in the original data, you’ll have to repeat the process to get updated results.