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


    DezB

    Try:


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

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

    NBVC, your code shows surname,firstname,


    for firstname,surname, try


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

    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!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login