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.
Thanks
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
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
THANKS SOOOOOOOOOOOOOOO Much, my friend!!
Works beautifully
Great! Can you tick answer to show it has been answered? Cheers, Al
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
Yes, I am well aware of that function.; Thanks again