Numbers '09: Extract first word of a cell

If it is first name followed by a space then last name, you can use this formula (the names are in column B for this formula)

=RIGHT(B,LEN(B)-FIND(" ",B))

You may want to use TRIM on the name first to remove any extra spaces that might be there. so each B in the formula would be TRIM(B).

If someone has a two-part first name such as "Mary Ann", the formula fails. There really isn't any way around that problem since Numbers doesn't know what a name is. A formula that would split Mary Ann Jones into "Mary Ann" and "Jones" would fail on "John De Beers" and one that does the second name correct (the formula provided above) will fail on the other.

Message was edited by: Badunit

If B2 is the location of your source string then
=IFERROR(LEFT(TRIM(B2),SEARCH(" ",TRIM(B2))-1),IF(ISBLANK(B2),"",TRIM(B2)))

This will give one of the following:
1. The first word if there are multiple words
2. The only word if there is just one word
3. A blank if there are no words
4. The first/only word after any leading spaces if the source string has leading spaces.

The simple answer, which assumes no leading spaces and always more than one word in the source string is:
=LEFT(B2,SEARCH(" ",B2)-1)

Hello,

I have a contact list that I am trying to sort out and I need to separate first and last name.
The formula given here was great for first names, but I am having trouble with the last name:

Using this formula
{quote}=RIGHT(O3;FIND(" ";O3)-2){quote}

where O2 is the cell where I have my full name, gives any kind of truncated names, depending on the length of the first name. I have tried any variation for the last term (-2), but nothing satisfactory.

Now I have found this excel formula that I am trying to adapt to numbers:


{quote}=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
which should become something like that in my case:
=RIGHT(O2;LEN(O2)-FIND("*",SUBSTITUTE(O2;" ";"*";LEN(O2)-LEN(SUBSTITUTE(O2;" ";"")))))

if I am not mistaken about the change from commas to semi-colon
{quote}

Any idea to what I should do ? Ideally, what I would like to have is a formula that extracts everything that is after the first word, so as to manage the name with particle and/or the middle names....

Thanks for your attention,

Colin