![]() ![]() =RIGHT(A2, LEN(A2) - SEARCH(" ", A2, SEARCH(" ", A2, 1)+1))Ī similar approach can be used to split names with suffixes: To work out the length of the middle name, you subtract the position of the 1st space from the position of the 2nd space, subtract 1 from the result to get rid of a trailing space, and put this number in the num_chars argument of MID, telling it how many characters to extract.Īnd here are the formulas to separate names of the Last name, First name Middle name type: This number goes to the start_num argument of the MID function. To determine the position of the first space, use a simple SEARCH(" ",A2) function, to which you add 1 to start the extraction with the next character. To extract the middle name, you need to know the position of both spaces in the name. Then, you supply the above number to the RIGHT function instructing it to pull that number of characters from the end of the string. To get the last name, determine the position of the 2 nd space by using nested SEARCH functions, subtract the position from the total string length, and get the length of the last name as the result. To get the first name, you use the already familiar LEFT SEARCH formula. If your names are in the First name Middle name Last name format, the below formulas will work a treat: Splitting names that include a middle name or middle initial requires slightly different approaches, depending on the name format. The below screenshot shows the results: How to split full name to first, last, and middle name With the full name in cell A2, the formulas take the following shape: To get a surname, you use the LEFT SEARCH combination discussed in the previous example with the difference that you subtract 2 instead of 1 to account for two extra characters, a comma and a space. If you have a column of names in the Last name, First name format, you can have them split into separate columns by using the following formulas. So, here's how you can separate the first name and surname in Excel when some of the original names include a middle name: How to separate first and last name from name with comma After that, you subtract the above number from the total string length to get the length of the last name, and have the RIGHT function extract that many characters. Here is a high-level explanation of the formula's logic: you replace the last space in the name with a hash sign (#) or any other character that do not appear in any name and work out the position of that char. If some of the original names contain a middle name or middle initial, you'd need a bit more tricky formula to extract the last name: The result will look something similar to this: You enter the formulas in cells B2 and C2, respectively, and drag the fill handle to copy the formulas down the columns. With the full name in cell A2, the formulas go as follows: In this formula, you also use the SEARCH function to find the position of the space char, subtract that number from the total length of the string (returned by LEN), and get the RIGHT function to extract that many characters from the right side of the string. RIGHT( cell, LEN( cell) - SEARCH(" ", cell)) The first name can be easily extracted with this generic formula: These formulas cover the most typical scenario when you have the first name and last name in one column separated by a single space character. How to split first and last name from full name with space However, if you plan to make any changes to the original names and are looking for a dynamic solution that will update automatically, you'd better divide names with formulas. As the Destination, specify the topmost cell in the column where you want to output the results (please keep in mind that this will overwrite any existing data, so be sure to choose an empty column).ĭone! The first, middle, and last name are divided into separate columns: Separate first and last name in Excel with formulasĪs you have just seen, the Text to Columns feature is quick and easy. The default General format works nice in most cases. On the last step, you select the data format and destination, and click Finish.If you are dealing with names separated with a comma and space like Anderson, Ronnie, then check the Comma and Space boxes under Delimiters, and select the Treat consecutive delimiters as one checkbox (usually selected by default). ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |