200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
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?
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)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
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.
Alternatively:
=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))