Reverse First Name & Last Name, where each Name is Separated with a Space
Reverse First Name & Last Name, where each Name is Separated with a Space
Reverse First Name & Last Name(s), with or without comma, where each Name is Separated with a Space.
Cell B11 formula: Cell B11 Reverses 1st Name & Multiple Last Names (ie. All Names after 1st Blank Space), for cell A11 Name.
Cell D11 formula: Cell D11 Reverses 1st Name & Multiple Last Names (ie. All Names after 1st Space), for cell A11 Name – adds Comma after Multiple Last Names.
———————————————————————————————–
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), then return the 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 A11 ie. TRIM(A11).
Enter formula in cell B11 and copy down.
Cell B11 formula:
=IF(ISNUMBER( FIND(” “, A11)), MID(A11& ” ” &A11, FIND(” “, A11)+1, LEN(A11)), A11&””)
Explanation – Cell B11 formula (Non-Array Formula):
1) MID(A11& ” ” &A11, FIND(” “, A11)+1, LEN(A11)): The FIND function – FIND(” “,A11) – returns the position of the 1st blank in cell A4 string ie. 5. Adding 1 to this (which becomes 6), is the start_num argument of the MID function. Using – A11&” “&A11 – repeats the cell A11 string with an inbetween space. This is the text argument of the MID function. Using “LEN(A11)” as the num_chars argument of the MID function, returns the characters from cell A4 string, starting after the 1st blank space so that the Last Names (multiple Names after the 1st space) appear first, and then all characters equivalent to the cell A11 string length are returned which includes the First Name also. In effect the returned string starts from the Last Names and then includes the First Name, thus effectively reversing the First & Last Names.
2) ISNUMBER( FIND(” “, A11)): Using the FIND function returns the position of 1st occurrence of blank space in cell A11 string and using ISNUMBER returns TRUE if a number is returned and then the formula in 1) above is applied. If there is no blank space it means that either cell A11 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 cell A11 value in this case. A11&”” is used to enable returning a blank if cell A11 is blank ie. if cell A11 is blank then just entering A11 returns zero because a blank cell is evaluated as zero, while using A11&”” returns blank.
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 (ie. no blank space), then return the 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 A11 ie. TRIM(A11).
Enter formula in cell D11 and copy down.
Cell D11 formula:
=IF(ISNUMBER( FIND(” “, A11)), MID(A11& “, ” &A11, FIND(” “, A11)+1, LEN(A11)+1), A11&””)
Explanation – Cell D11 formula (Non-Array Formula):
1) This formula is similar to the cell B11 formula except that: (i) A11&”, “&A11 is used in cell D11 formula instead of A11&” “&A11 in cell B11 formula, thus adding a comma immediately after the Last Names; and (ii) the num_chars argument of the MID function in cell D11 formula is “LEN(A11)+1” which adds 1 to the num_chars argument in cell B11 formula of “LEN(A11)” – this is to account for the comma addition.
—————————————————————————–
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.
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 A18 Name ie. First, Middle & Last Name, are extracted in consecutive cells (cells B18 to D18);
If cell A18 has only One Name (ie. no blank space), then return the Name in the 1st cell (cell B18);
If cell A18 Full Name has only 2 Names, then the First Name is extracted in the 1st cell (cell B18), the 2nd cell (cell C18) remains blank, and the Last Name is extracted in the 3rd cell (cell D18);
If cell A18 Full Name has 3 or more Names, then the 1st Name is extracted in the 1st cell (cell B18), the Single Last Name in the 3rd cell (cell D18), and the 2nd and all other Names before Last Name are extracted in the 2nd cell (cell C18);
Cell A18 Full Name may have any number of Names (0, 1, 2 ,3 or more) & each of these is separated with Single Space only;
Cell B18 formula: Cell B18 extracts 1st Name from cell A18 Name. If cell A18 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 C18 formula: Cell C18 extracts extracts the Single Middle Name only from cell A18 Name, where cell A18 Full Name has exactly 3 names and extracts multiple Single Names where cell A18 Full Name has more than 3 Names (if Cell A18 has less than 3 Names then cell C18 is blank). Normal Non-Array formula.
Cell D18 formula: Cell D18 extracts extracts the Single Last Name only (ie. Surname after Last Blank Space) from cell A18 Name, where cell A18 Full Name has 2 or more names. 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.