We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Move last word in cell to new cell

asked 2020-07-08 22:41:01 +0200

Jim McKane gravatar image

I want to split off the last name in a field and move it to a new field. e.g. - A1 contains "james ainslie mckane" - I want to move "mckane" to A2 leaving A1 as "james ainslie"

this is only an example as there could be more than 2 first names in a field.


edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-07-09 02:04:56 +0200

Earnest Al gravatar image

updated 2020-07-09 02:08:40 +0200

Hi. If you want to start cutting up the data in place in the cell then you want a macro. If you can cope with having two new fields then this formula should be OK. I have put the new cells in column B and C for simplicity but you can put them wherever you want. Care is needed because some last names are in two parts, e.g. De Saville. Assuming A2 has the compounded name then in

image description

Surname - Cell C2 enter =RIGHT(A2;LEN(A2)-FIND("*";SUBSTITUTE(A2;" ";"*";LEN(A2)-LEN(SUBSTITUTE(A2;" ";"")))))

First Names - Cell B2 enter =LEFT(A2;LEN(A2)-LEN(C2))

Copy and Paste results as unformatted text to use as normal

Cheers, Al

edit flag offensive delete link more


THANKS SOOOOOOOOOOOOOOO Much, my friend!! Works beautifully

Jim McKane gravatar imageJim McKane ( 2020-07-09 02:24:31 +0200 )edit

Great! Can you tick answer to show it has been answered? Cheers, Al

Earnest Al gravatar imageEarnest Al ( 2020-07-09 02:30:44 +0200 )edit

BTW with a fixed number of fields then Data | Text to Columns and choosing the space as the separator will split the cell into a cell for each word

Earnest Al gravatar imageEarnest Al ( 2020-07-09 02:34:51 +0200 )edit

Yes, I am well aware of that function.; Thanks again

Jim McKane gravatar imageJim McKane ( 2020-07-09 10:24:23 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-07-08 22:41:01 +0200

Seen: 231 times

Last updated: Jul 09 '20