Turn Surname and Firstname around

  • Hi Folks

    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.... :-)


  • Ooops.. yes correct, thanks for catching that ... but don't forget to add the space between...

    =TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",100)),100,100)&" "&LEFT(SUBSTITUTE(B2," ",REPT(" ",100)),100))

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    MS Excel MVP 2010-2016