Split Names – Extract First, Middle & Last Names in Separate Cells
Extract First Name, Middle Name & Last Name(s) in separate cells, from Full Name, where each name is separated with a Space. 2nd cell to contain the excess Middle Names, where there are more than 3 Names separated with Space
Cell B21 formula: Cell B21 extracts the First Name from cell A21 Name.
Cell C21 formula: Cell C21 extracts the Middle Name(s), ie. the name(s) AFTER the 1st blank space and PRECEDING the Last blank space where there are 3 or more Names in cell A21.
Cell D21 formula: Cell D21 extracts the Single Last Name ie. the name AFTER the Last blank space where there are 2 or more Names in cell A21.
———————————————————————————————–
Cell B21, C21 & D21 formulas: Extract First Name, Middle Name & Last Name(s) in separate cells, from Full Name, where each name is separated with a Space. 2nd cell to contain the excess Middle Names, where there are more than 3 Names separated with Space.
Each of the 3 Names of Full cell A21 Name ie. First, Middle & Last Name, are extracted in separate cells (cells B21, C21 and D21);
If cell A21 has only One Name (ie. no blank space), then return the Name in the 1st cell (cell B21);
If cell A21 Full Name has only 2 Names, then the First Name is extracted in the 1st cell (cell B21), the 2nd cell (cell C21) remains blank, and the Last Name is extracted in the 3rd cell (cell D21);
If cell A21 Full Name has 3 or more Names, then the 1st Name is extracted in the 1st cell (cell B21), the Single Last Name in the 3rd cell (cell D21), and the 2nd and all other Names before Last Name are extracted in the 2nd cell (cell C21);
Cell A21 Full Name may have any number of Names (0, 1, 2 ,3 or more) & each of these is separated with Single Space only;
Cell B21 formula: Cell B21 extracts 1st Name from cell A21 Name.
If cell A21 has only One Name (ie. no blank space(s)), then return as First Name. Presume Names are separated with Single Space only, and if not, then use the TRIM function, which removes preceding, trailing and extra inbetween spaces, with A21 ie. TRIM(A21).
Cell B21 formula:
=IFERROR( LEFT(A21, FIND(” “, A21)-1), A21&””)
Explanation – Cell B21 formula (Non-Array Formula):
1) FIND(” “, A21): returns the position of the 1st blank in cell A21 string, ie. 5. “LEFT(A21, FIND(” “, A21)-1)”: This is the num_chars argument of the LEFT function and returns the characters to the left of the 1st blank space in cell A21 ie. “John”. Using IFERROR returns the cell A21 string (A21&””) so that if there is no blank space within the cell A21 string, the full string is returned. A21&”” is used to enable returning a blank if cell A21 is blank ie. if cell A21 is blank then just entering A21 returns zero because a blank cell is evaluated as zero, while using A21&”” returns blank.
Cell C21 formula: Cell C21 extracts extracts the Single Middle Name only from cell A21 Name, where cell A21 Full Name has exactly 3 names and extracts multiple Middle Names where cell A21 Full Name has more than 3 Names (if Cell A21 has less than 3 Names then cell C21 is blank).
Cell C21 formula:
=IFERROR( TRIM( MID( REPLACE(A21, FIND(“?”, SUBSTITUTE(A21, ” “, “?”, LEN($A21)- LEN( SUBSTITUTE( $A21,” “, “”)))), LEN(A21),””), FIND(” “, A21), LEN(A21))), “”)
Explanation – Cell C21 formula (Non-Array Formula):
1) LEN($A21)- LEN( SUBSTITUTE($A21, ” “, “”)): The Length of cell A21 string after removing all spaces (ie. 13) is deducted from the cell A21 Length (ie. 17), which returns the number of blank spaces within (ie. 4). This is the instance_num argument of the SUBSTITUTE function used in 2) below which indicates the last instance of blank space. Using the SUBSTITUTE function – SUBSTITUTE( $A21,” “, “”) – removes blank spaces and using LEN after this returns the length ie. number of characters in the string after removing blank spaces.
2) SUBSTITUTE(A21, ” “,”?”, LEN($A21)- LEN(SUBSTITUTE( $A21,” “, “”))): this uses the SUBSTITUTE function, with the instance_num argument returned in 1) above which indicates the last instance of blank space, to replace the last blank in cell A21 string with the symbol “?”, and returns the string – “John B R K?Miller”. Using the FIND function with this string – FIND(“?”, SUBSTITUTE(A21, ” “,”?”, LEN($A21)- LEN( SUBSTITUTE( $A21,” “, “”)))) – returns the position of the symbol “?”, which is the position of the last blank in cell A21 string ie. 11. This is also the start_num argument of the REPLACE function used in 3) below. Note: any less frequently used symbol or letter can be used instead of “?” which is used arbitrarily only.
3) REPLACE(A21, FIND(“?”, SUBSTITUTE(A21,” “, “?”, LEN($A21)- LEN( SUBSTITUTE($A21, ” “,””)))), LEN(A21), “”): using the start_num argument as explained in 2) above replaces the part of cell A21 string starting from the last blank, and using “LEN(A21)” as the num_chars argument ensures that the string starting from the last blank is covered right till the end. Using the new_text argument of “”, the REPLACE function essentially deletes all characters after the last blank to return the string – “John B R K” – which are all cell A21 characters from the 1st till the last blank. This is also the text argument of the MID function used in 4) below.
4) MID(REPLACE( A21, FIND(“?”, SUBSTITUTE(A21, ” “,”?”, LEN($A21)- LEN( SUBSTITUTE($A21, ” “,””)))), LEN(A21),””), FIND(” “, A21), LEN(A21)): using the MID function with the text argument of 3) above which is the cell A21 text from the 1st character till the last blank, and using the start_num argument of – FIND(” “, A21) – means that the text starting from the 1st blank space of cell A21 is considered, and using – LEN(A21) – as the num_chars argument ensures that all the characters of text argument are covered till the end. Using the MID function returns the characters starting from the 1st blank till the last blank, and using TRIM with this removes any preceding, trailing or extra inbetween spaces to return the MIDDLE Name(s) in cell C21 ie. “B R K”. If there is no Middle Name, using the IFERROR function returns a blank ie. “”.
Cell D21 formula: Cell D21 extracts extracts the Single Last Name only (ie. Surname after Last Blank Space) from cell A21 Name, where cell A21 Full Name has 2 or more names.
Cell D21 formula:
=IF(ISNUMBER( FIND(” “, A21)), TRIM( RIGHT( SUBSTITUTE( A21,” “, REPT(” “, LEN(A21))), LEN(A21))), “”)
Explanation – Cell D21 formula (Non-Array Formula):
1) SUBSTITUTE( A21,” “, REPT(” “, LEN(A21))): Using the REPT function – REPT(” “, LEN(A21)) – repeats the blank as many times as much as the number of characters of cell A21 string (ie. string length of 17). The SUBSTITUTE function is used to replace each blank of cell A21 string with the number of blanks equal to the cell A21 string length. The length of the new string returned is 81 (ie. 4+17+1+ 17+1+17+ 1+17+6). This is the text argument of the RIGHT function used in 2) below.
2) RIGHT(SUBSTITUTE( A21,” “, REPT(” “, LEN(A21))), LEN(A21)): using the RIGHT function with the num_chars argument of – LEN(A21) – and the text argument as in 1) above, returns the characters equivalent to the cell A21 string length, starting from the right of the string returned in 1) above ie. it covers the last Name of 6 characters plus some blank spaces preceding it. This method of using the RIGHT function is used to ensure that only the Text after the last space of cell A21 string is returned. Using the TRIM function removes any preceding or trailing blank spaces to return all characters after the last blank space i.e the Last name of “Miller”.
3) ISNUMBER( FIND(” “, A21)): Using the FIND function returns the position of 1st occurrence of blank space in cell A21 string and using ISNUMBER returns TRUE if a number is returned and then the formula in 2) above is applied. If there is no blank space it means that either cell A21 is blank or contains a single name only, and the FIND function does not return a number so using ISNUMBER will return FALSE, and using the IF statement returns a blank (ie. “”) in this case which indicates that there is no Last name.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Other formulas:
Extract First Name, Single or Multiple Last Name(s), separated with a space, from a Full Name.
Cell B4 formula: Cell B4 extracts 1st Name from cell A4 Name. If cell A4 has only One Name (ie. no blank space(s)), then return as First Name. Presume Names are separated with Single Space only. Normal Non-Array formula.
Cell C4 formula: Cell C4 extracts extracts the Single Last Name only (ie. Surname after Last Blank Space) from cell A4 Name. Normal Non-Array formula.
Cell D4 formula: Cell D4 extracts Multiple Last Names (ie. All Names after 1st Blank Space) from cell A4 Name. Normal Non-Array formula.
Reverse First Name & Last Name(s), with or without comma, where each Name is Separated with a Space.
Cell B11 formula: Reverses 1st Name & Multiple Last Names (ie. All Names after 1st Blank Space), for cell A11 Name. If cell A11 has only One Name (ie. no blank space(s)), then return the Name. Presume Names are separated with Single Space only. Normal Non-Array formula.
Cell D11 formula: Reverses 1st Name & Multiple Last Names (ie. All Names after 1st Blank Space), for cell A11 Name – adds Comma after Multiple Last Names. If cell A11 has only One Name then return the Name. Normal Non-Array formula.
Extract First Name, Middle Name & Last Name(s) in separate cells, from Full Name, where each name is separated with a Space.
3rd cell to contain the excess Last Names, where there are more than 3 Names separated with Space;
Each of the 3 Names of Full cell A29 Name ie. First, Middle & Last Name, are extracted in consecutive cells (cells B29 to D29);
If cell A29 has only One Name (ie. no blank space), then return the Name in the 1st cell (cell B29);
If cell A29 Full Name has only 2 Names, then the First Name is extracted in the 1st cell (cell B29), the 2nd cell (cell C29) remains blank, and the Last Name is extracted in the 3rd cell (cell D29);
If cell A29 Full Name has 3 or more Names, then the 1st Name is extracted in the 1st cell (cell B29), the Single Last Name in the 3rd cell (cell D29), and the 2nd and all other Names before Last Name are extracted in the 2nd cell (cell C29);
Cell A29 Full Name may have any number of Names (0, 1, 2 ,3 or more) & each of these is separated with Single Space only;
Cell B29 formula: Cell B29 extracts 1st Name from cell A29 Name. If cell A29 has only One Name (ie. no blank space(s)), then return as First Name. Presume Names are separated with Single Space only. Normal Non-Array formula.
Cell C29 formula: Cell C29 extracts extracts the Single Middle Name only from cell A29 Name, where cell A29 Full Name has 3 or more names (if Cell A29 has less than 3 Names then cell C29 is blank). Normal Non-Array formula.
Cell D29 formula: Cell D29 extracts all Names after the Last Blank Space, where cell A29 has more than one Name – this will mean it extracts 1 Name if cell A29 has exactly 2 or 3 Names, and Multiple Names if cell A29 has more than 3 names. Normal Non-Array formula.
Extract First Name, Middle Name & Last (3rd) Name in consecutive cells, from Full Name, where each name is separated with a Space. Single Formula to Split each of the 1st, 2nd & 3rd Names – Valid for UPTO 3 Names only separated with Space.
Each of the 3 Names of Full cell A40 Name ie. First, Middle & Last Name, are extracted in consecutive cells (cells B40 to D40);
If cell A40 Full Name has 3 Names, then each of the 3 Names are extracted in 3 consecutive cells (cells B40 to D40);
If cell A40 Full Name has only 2 Names, then the First Name is extracted in the 1st cell (cell B40), the 2nd cell (cell C40) remains blank, and the Last Name is extracted in the 3rd cell (cell D40);
If cell A40 has only One Name (ie. no blank space), then return the Name in the 1st cell (cell B40);
Presumes that cell A40 Full Name has UPTO 3 Names only (ie. 1st Name, Middle Name & Last Name) & each of these is separated with Single Space only;
Cell B40 formula: Cell B40 extracts 1st Name from cell A40 Name. If cell A40 has only One Name (ie. no blank space(s)), then return as First Name. Presume Names are separated with Single Space only. Normal Non-Array formula.
Cell C40 formula: Cell C40 extracts extracts the 2nd Name from cell A40 Name, where cell A40 Full Name has 3 names (if Cell A40 has less than 3 Names then cell C40 is blank). Normal Non-Array formula.
Cell D40 formula: Cell D40 extracts the 3rd Name where cell A40 has 3 Names, and extracts the 2nd Name if cell A40 has 2 Names only (remains blank if cell A40 has less than 2 Names). Normal Non-Array formula.