Today we are going to discuss about various methods to reverse First and last name in Excel.
This is going to be Text functions fest. Lot to learn, lets' dive in.
Our data is in the range C3:C14, which contains Excel experts name separated by a space.
Method 1:
Formula in D4 cell is,
=MID(C4&" "&C4,SEARCH(" ",C4)+1,LEN(C4))
Arguments of MID function,
=MID(text,start_num,num_chars)
Let me give you simplest form of MID function with an example,
=MID("Leila Gharani",2,6)
Here, MID function extracts substring from the text 'Leila Gharani'. Starting position of the substring is 2 and number of characters to be extracted is 6. So, output is 'eila G'.
Let me start our task from scratch,
Concatenate name in C4 with same name and use Space (" ") as separator.
Substring what we are going to extract is shown within pipe symbol,
Leila |Gharani Leila| Gharani.
Two attributes to extract the substring are, starting position and number of characters.
Starting position:
Position of Space in C4 cell + 1,
SEARCH function helps us to get the position of Space character.
Bonus Tip:
To evaluate the part of formula, click on that part in IntelliSense and press F9.
Remember to press Esc or Ctrl + Z, otherwise evaluated result will be hardcoded in formula.
Number of Character:
We can use the length of C4 cell name as shown below.
After evaluating the num chars argument, the result shown below.
Then drag the formula all the way down till D14 cell.
Method 2:
Formula in E4 cell is,
=RIGHT(C4,LEN(C4)-SEARCH(" ",C4))&" "&LEFT(C4,SEARCH(" ",C4)-1)
Let me start from scratch,
In this method, we will try to pick the right-side portion after space and concatenate with left side portion.
Let me try with RIGHT function,
Arguments of this function,
=RIGHT(text,[num_chars])
For Example.,
=RIGHT("Leila Gharani",4)
Output of this function is "rani". So, it extracts the substring based on number of characters from right.
In our case, to find the number of characters, we are going to subtract the position number of Space character from overall length. This way, we can extract the right-side part of text.
Extracting the left-side part of the text is pretty straight forward,
Find the position of Space and subtract one, to get the number of characters from left side.
After Evaluation,
Then drag the formula all the way down.
Comentarios