Changing Name Position


hey i need your help

so, i got a task to transform data and theres this one task where i should change the name position as u see in the file attached i made up similar to my one of my tasks. In column table 1, the first name positions were all in the second.

How do i change the name position, making the second name which is the real initial name to be the first name in table 2?



Selected Answer

Hello Fida and welcome to the Forum.

You can use Excel's text manipulation functions to convert the names from last name, first name to a first name last name format.

Use "Select All" below to copy this formula then paste it into cell B4:

=RIGHT(A4,LEN(A4)-FIND(" ",A4)) & " " & LEFT(A4,FIND(",",A4)-1)
Then copy that down to other rows.

It works by finding where the first space is in the name then taking the right bit of the string (with number of characters being the length of the string less that found position). That is combined with a space plus the left bit of the string (up to where the comma is found (less 1 so the , is removed).

You'll get some explanantion of the LEFT, RIGHT, LEN and FIND functions in Don's tuturial here: Formula to Delete the First or Last Word from a Cell in Excel

Hope this works for you



Hi John, thank you so much for your help. The function works perferctly. You're  awesome! :) Thanks again.
Fida_mutia (rep: 14) Apr 28, '21 at 12:45 am
Add to Discussion


Depending on your version of Excel, you can use flash fill, by typing the first and second names (first name & surname) you should get the option to flash fill.


=TRIM(RIGHT(SUBSTITUTE(A4," ",REPT(" ",100),1),100))&" "&TRIM(LEFT(SUBSTITUTE(A4,",",REPT(" ",100)),100))

Or 365 version:

=TRIM(RIGHT(SUBSTITUTE(A4:A30," ",REPT(" ",100),1),100))&" "&TRIM(LEFT(SUBSTITUTE(A4:A30,",",REPT(" ",100)),100))



Kevin. The Excel 365 (array) formula is a neat and quick way of doing it- nice!
John_Ru (rep: 1267) Apr 27, '21 at 9:52 am
Hey Kevin, thanks alot for your help.
Frankly speaking, I'm a bit confused with your formula as it is longer than John's. I'd very appreciate if you could explain in detail how the function work because I wanna learn more about the logic in your function.
Thanks again.
Fida_mutia (rep: 14) Apr 28, '21 at 12:53 am

The formula propsed by Kevin has two bits but it's easier to explian the LEFT bit...

Inside that, the SUBSTITUTE function takes the string and replaces each space with 100 spaces (via the REPT function). The function LEFT(<string>,100) returns the frist 100 chratcters of the string, Provided the first bit of the string is 100 character of less, you'll then always get that portion plus several spaces. The surrounding TRIM fuction just strips off the leading and trailing spaces.

Hope this makes sense (and you can work out  a similar logic for the RIGHT element)
John_Ru (rep: 1267) Apr 28, '21 at 4:42 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login