You'll often see names that are out of order, and need to flip them. Here are a couple quick formulas to help you accomplish that task.
Contents:
How to Reverse Last and First Names Separated by a Space
Formula:
Replace the A1 with the cell containing the full names you'd like to reverse.
=TEXTAFTER(A1, " ") & " " & TEXTBEFORE(A1, " ")Example: Reversing Last and First Names
Suppose you have a column of names where each cell contains a first name followed by a last name, separated by a space:
You can use the following formula along side the names to reverse the first and last names:
=TEXTAFTER(A1, " ") & " " & TEXTBEFORE(A1, " ")This formula is particularly useful in situations where you need to reverse the order of names or reformat strings where elements are separated by a specific character (like a space in this case). The use of TEXTAFTER and TEXTBEFORE simplifies what would otherwise require more complex functions or multiple steps.
We can break the formula down into a few different parts.
TEXTAFTER(A1, " ") TEXTAFTER is a function in Excel that extracts the part of the text from a specified cell (in this case, A1) that comes after a given delimiter (in this case, a space " ").
If A1 contains "John Doe", TEXTAFTER(A1, " ") will return "Doe". It finds the first space and returns everything after it.
& " " & The & symbol is used in Excel to concatenate, or join, different text strings together.
In this formula, " " (a space enclosed in quotes) is a text string that's being added between the first and last names to ensure they are separated by a space in the final result.
So, after extracting "Doe" from "John Doe", this part of the formula adds a space after "Doe".
TEXTBEFORE(A1, " ") TEXTBEFORE is another Excel function used to extract the part of the text from a specified cell that comes before a given delimiter.
In the case of "John Doe" in A1, TEXTBEFORE(A1, " ") will return "John". It finds the first space and returns everything before it.
Putting it All Together
When you combine these functions using &, the formula first extracts the last name ("Doe"), adds a space, and then extracts the first name ("John"). So, for "John Doe" in cell A1, the entire formula =TEXTAFTER(A1, " ") & " " & TEXTBEFORE(A1, " ") will return "Doe John".
How to Reverse Last and First Names Separated by a Comma
Formula:
Replace the A1 with the cell containing the full names you'd like to reverse.
=TEXTAFTER(A1, ",") & " " & TEXTBEFORE(A1, ",")Example: Reversing Last and First Names Separated by a Comma
Suppose you have a column of names where each cell contains a first name followed by a last name, separated by a comma:
You can use the following formula along side the names to reverse the first and last names:
=TEXTAFTER(A1, ",") & " " & TEXTBEFORE(A1, ",")Like the previous example, this formula is particularly useful in situations where you need to reverse the order of names or reformat strings where elements are separated by a specific character (a comma in this case). The use of TEXTAFTER and TEXTBEFORE simplifies what would otherwise require more complex functions or multiple steps.
This formula extracts the first name (text after the comma) and the last name (text before the comma) using TEXTAFTER and TEXTBEFORE, respectively.
For a more detailed breakdown of the formula, check out the previous example. All things are the same except the delimiter used (" " vs ", ").