How to reverse First Name and Last Name in Excel - Part 2

Today we are going to discuss about various methods to reverse First and last name in Excel.

We have already discussed 2 methods in Part 1. This is Part 2 in reversing names with space as separator. Lot to learn, let's dive in.

Method 1:

Formula in D4 cell is,

=REPLACE(C4,1,SEARCH(" ",C4),"")&" "&LEFT(C4,SEARCH(" ",C4)-1)

Let me start from scratch,

In the name 'Leila Gharani', we are going to replace 'Leila ' with zero length text string. So, only 'Gharani' will be left out.

=REPLACE(C4,1,SEARCH(" ",C4),"")

Arguments of REPLACE function,

=REPLACE(old_text,start_num,num_chars,

new_text)

This function helps us to pick the substring from main text,

Start_num and num_chars are attributes to pick substring,

New_text is the replacement string,

Old_text:

Here, old text is 'Leila Gharani' in C4 cell.

Start_num:

Since, we are going to wipe out left most part ('Leila '), start number of substrings is always one and so we hardcoded that.

Num_chars:

Relative position of Space character will help us to get the length of substring. So, we used SEARCH function to pick the position of Space character in 'Leila Gharani'.

If we evaluate that part, result is shown below,

So, characters from 1 to 6 is picked from 'Leila Gharani' and replaced with two double quotes (""), that means zero length text string, that means blank.

Now, we can simply pick left part of main text excluding space and concatenate with our existing text. We can do this with the help of LEFT function.

Arguments of LEFT function,

=LEFT(text,[num_chars])

This function will pick substring from main string based on number of characters from

left side. Num_chars argument is optional, if we ignore that, Default value of one is used.

In num_chars argument, we subtract one to exclude space, if we evaluate this,

Then concatenate both of our substrings with Space and drag the formula till D14.

Bonus Tip:

In above method, we used SEARCH function to do the same operation in 2 places.

We can use LET function to reduce the calculation time and to improve the readability.

=LET(nc,SEARCH(" ",C4),REPLACE(C4,1,nc,"")&" "&LEFT(C4,nc-1))

Output of SEARCH function is named as 'nc', and we used this name in 2 places.

This name 'nc' is evaluated once and kept in memory and will be used whenever it is called.

Excel helps us; so, we help excel to reduce workload.

Method 2:

Formula in E4 cell is,

=SUBSTITUTE(C4,LEFT(C4,SEARCH(" ",C4)),"")&" "&LEFT(C4,SEARCH(" ",C4)-1)

Let me start from scratch,

Arguments of SUBSTITUTE function,

=SUBSTITUTE(text,old_text,new_text,[instance_num])

In REPLACE function, we used the attributes to get substring. But here in SUBSTITUTE function, we will feed the substring by itself in old_text argument and the replacement string in new_text argument.

Here, old_text is core part, so will start from there,

Now we got the substring, then we are going to substitute this part with blank.

Remaining part is same as Method 1, so you know what to do.

Method 3:

Simple and straightforward dynamic array function.

Only available in Office 365 version.

Formula in F4 cell is,

=TEXTAFTER(C5," ")&" "&TEXTBEFORE(C5," ")

Bonus Method:

Formula in G4 cell is,

=DROP(TEXTSPLIT(C4," "),,1)&" "&TAKE(TEXTSPLIT(C4," "),,1)

TEXTSPLIT function will split text across columns or rows. Here, we fed Space as the column delimiter and so text will get split across columns.

Then DROP function helps us to skip first column and the result is 'Gharani'.

Take function helps us to pick first column and the result is 'Leila'.

That's it, concatenate both substrings with space and enjoy the text function festival.