Split Names column into First Last name columns #2

=IFERROR(REPLACE(A1,1,FIND(",",A1)+1,""),LEFT(A1,FIND(" ",A1)-1))
=IFERROR(LEFT(A1,FIND(",",A1)-1),REPLACE(A1,1,FIND(" ",A1),""))

Hi,

There is one problem with it, if a first name contains two words, it will push the second to the last name column.
This was addressed by the original thread.

Thanks,
Ben

=CHOOSE(2*(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)+ISERR(FIND(",",A1))+1,REPLACE(A1,1,FIND(",",A1)+1,""),LEFT(A1,FIND(" ",A1)-1),REPLACE(A1,1,FIND(",",A1)+1,""),LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1))
=CHOOSE(2*(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)+ISERR(FIND(",",A1))+1,LEFT(A1,FIND(",",A1)-1),REPLACE(A1,1,FIND(" ",A1),""),LEFT(A1,FIND(",",A1)-1),REPLACE(A1,1,FIND(" ",A1,FIND(" ",A1)+1),""))

Hi,

That's much better, just two issues found.

1- If column only has one word it gives an error, should go under first name
2- If column has more than 3 words it gives an error, should all but the last go under first name and last word under last name.


Thanks,
Ben

If its Excel then remove the tag about Microsoft Access

If you work in Access the Split Command should serve you just fine...probably you could make a table with First Names and if the result of the split (Reversed Name/FirstName) is not "contained" then it will take the results in Reverse.

Put your list in the Original Data tab table, then refresh the table in Transformed tab.  See if this works for you.
EE.xlsx

Oops... Forgot the last step.
EE.xlsx

@Tom,
Tried your latest, didn't seem to do anything, perhaps I need instructions...

@John,

If its Excel then remove the tag about Microsoft Access

File could have been linked to Access and create those in logic in either Query itself like Saqib did,  or via UDF's...

Thanks Sakib for great job!

bfuchs, the tool I showed you if rather simple to update.  I just copied the names I had in the previous file (Original Tab), added them to the bottom and went to the Transformed Tab and right-clicked in the table and selected "Refresh".  See attached.  The query runs and updates for the new data.  What version of Excel are you using?  That might be the issue.  I would think you could update the query if you had Excel 2016 Pro or later.  Maybe I don't have a grasp of share-ability for the tool.  Anyway, you might want to look into it, it is good for many things.  - Tom
EE-5-2.xlsx

Hi,

and went to the Transformed Tab and right-clicked in the table and selected "Refresh"

Dont see that option, see attached.

you had Excel 2016 Pro or later

I do have Office365.

Thanks,
Ben
Untitled.png

See if you can go to Data > Queries, and that should bring up a query screen on the right. Does that work?

Data>Show Queries. Does it show a query on the right?

@ Tom,

Now I saw it under Connections and queries.

Can you explain more on how this works, where do I see the SQL?

How can i modify that if needed, and how you copy rows from there to another source?

Thanks,
Ben

The Power Query (Get and Transform is the name now) records the steps of transformation in a language called M.  The code can be seen within the formula bar or by going to (don't exactly have the path) View>Advanced Editor.  There is quite a few videos on the internet that can introduce you to Power Query.

Thank you Tom!