I'm using this formula to extract the Surname and Firstname from a string where the Surname comes first followed by the Firstname and then some other information:
=TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",100)),200))
i.e. Bloggs Jim HCD Centre Nine Team 4
However, is there a way to modify this so that it returns the Firstname and then the Surname please? Instead of returning Bloggs Jim I'd like it to return Jim Bloggs if at all possible.
I've tried playing around with the 100 and 200 but to no avail, (shows I know what I'm doing doesn't it??)
Kind regards and many thanks as usual....