![]() In the following wizard, check the Text option in the Column data format section, and specify the Destination cell where you want to put the results. In the Step 2 of 3 wizard, only check the Space option in the Delimiters section, and click Next button.ĥ. In the Convert Text to Columns Wizard – Step 1 of 3 dialog box, check the Delimited option, and click Next button.Ĥ. Click the Text to Columns button under Data Tab.ģ. Select the column that you will split, in this case it is A2:A13.Ģ. The second way will guild you to split the full name column easily.ġ. ![]() At last, enter this formula: =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2)+1))) into a blank cell E2, and then drag the fill handle down to the cells to apply this formula, and all first names have been split into column E, see screenshot: ![]() Then enter this formula: =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)) into a blank cell D2, and then drag the fill handle down to the cells to apply this formula, and all middle names have been split into column D, see screenshot:ģ. Enter this formula: =LEFT(A2,SEARCH(" ",A2)) into a blank cell C2, and then drag the fill handle down to the cells to apply this formula, and all first names have been split into column C, see screenshot:Ģ. If you want to split full names to first, middle and last names into separate columns, the following formulas may do you a favor.ġ. Split full names to first, middle and last names: Then enter the formula of =RIGHT(A2,LEN(A2)-FIND(" ",A2,1)) in another blank cell, Cell D2, and drag the fill handle down to the cells which you want to apply this formula, and all last names have been extracted in column D as following screenshot shown: Enter the formula of =LEFT(A2,FIND(" ",A2,1)-1) in a blank cell, says Cell C2 in this case, and then drag the fill handle down to the cells which you want to fill this formula, and all first names have been extracted at once, see screenshot:Ģ. You can use the Left function, Right function and Find function to spit a full name to the first name and last name with following steps:ġ. Split full names to first and last names: Split full names to first and last names by comma or other delimiters Split full name to first and last name with Kutools for Excel quickly Split full name to first and last name with Text to Column command Split full name to first and last name with formulas
0 Comments
Leave a Reply. |