Equals Left, Formula

    I have a list of names, first and last that I want to read the first names only, I have tried the =LEFT but the names vary in lenght so that doesnt really work.


    John Gottas-Healy
    Richard Montgomery
    Brian Reeves
    Stephen Hewitt


    I only want the first name from each, is this possible some other way?

    Re: Equals Left, Formula


    Hi Barry,


    Assuming there is always a space after the first name you can use a combination of the left and find formula, using the find to see where in the whole name the forst space occurs and then just returning the bit in the cell to the left of the space.


    For example if your name is in cell A1 and it is Richard Montgomery
    type;


    =LEFT(A1,FIND(" ",A1)-1)


    which would return "Richard"


    You need the minus 1 on the end, otherwise it would return "Richard " with a space on the end.


    Hope that's what you needed.


    Cheers,
    Ian

    Re: Equals Left, Formula


    The Easiest way i can think of is to convert text to columns...


    Select the column with the names in then click on Data.. there you should see Text to Columns... select the Delimited Radio button and on the next screen tick Space... this will put the first name in one column and the second in another?

    Re: Equals Left, Formula


    Just had a thought,


    You could add an IF so even if there is only one name in the cell you would still return an answer;


    IF(ISERROR(FIND(" ",A1),A1,LEFT(A1,FIND(" ",A1)-1))


    Any good?
    Ian

Participate now!

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

Register Yourself Login