Removing Last Word in Columns in Numbers '09

EDIT: I took "remove" as meaning you wanted the last name, not that you wanted the last name gone. Rereading your post I think I got that backwards.

I can think of a brute force method which requires a few columns. You can reduce it to fewer columns by combining formulas together. Or maybe someone else has a more elegant approach

Column B = the names
Column C = 0 (zero)
Column D =IFERROR(FIND(" ",$B,C+1),"")
Drag-fill or copy/paste D into columns E-J
K2 =COUNT(D2:J2) drag-fill to the rest of the column
Column L =OFFSET(C,0,K)
Column M =RIGHT(B,LEN(B)-L)

Columns D through J locate all the spaces. K and L together find the position of the last space in the string. M returns everything to the right of that last space. You can combine K through M into one formula but I don't know of a more elegant way to find the last space than by finding each one, one at a time.

There are, I'm sure, other ways than this to do the same basic thing but the ones I can think of also require multiple columns.

Message was edited by: Badunit

Message was edited by: Badunit

EDIT: I took "remove" as meaning you wanted the last name, not that you wanted the last name gone.Rereading your post I think I got that backwards.

That was a first cut. Here's another way.

B = names
C = IFERROR(RIGHT(B,LEN(B)-FIND(" ",B)),"")
Drag fill C so it is in C-G
H2=OFFSET(C2,0,4-COUNTBLANK(C2:G2))
Drag fill H2 to fill the rest of the column.

Message was edited by: Badunit

Cool. You may want to subtract 1 from the length in that formula or enclose the entire formula with the TRIM function. I believe you are including the space after the first name(s).