How to extract all words in a cell except the last one?

[bianling]

I'm sure that has been solved before, but I cannot find or figure ou
the right formula so far.

I have a column of cells that have the first name, middle name(s) an
last name in that order in one cell.

I've seen formulas to extract the first or last word of a cell.
I want to extract the first name and middle names, that is all word
except the last word (or last name).

Thank you for replying with the solution

 

[JE McGimpsey]

One way:


=LEFT(A1, FIND("^", SUBSTITUTE(A1,"_", "^", LEN(A1) - LEN(SUBSTITUTE(A1,
"_", "")))) - 1)

where "_" represents a space (to prevent an unfortunate linewrap).

 

[bianling]

Thank you for your quick reply.

unfortunately I copied and pasted your formula, but I only got #VALUE
I wouldn't have the knowledge to play with your formula.

What are some other ways/formulas you suggested this could be don
with?

Thank you

 

[Bob Phillips]

I tried it and it works. Are you sure that you put the whole formula in, the
posting wrapped it around despite John's efforts. Also, did you replace the
"_" by " "?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Also be sure you don't have anyone entered with a space WITHIN their last name. Something like John Charles Van Engel. Most of the time this would be VanEngel but if someone entered it with a space tha tmight screw up what you are trying to do..

 

[bianling]

Thank you again for your quick reply.

Your formula does work. I had moronically misunderstood how to edit th
space.

I am myself very impressed by the formula. Is there any chance you ca
explain a little bit what it does? For example what is the "^" use
for?

Thank you again

 

[JE McGimpsey]

the caret (^) is just a character that is unlikely to be found in a name
by itself. Substitute finds the last space and replaces the space with a
caret. The Left function then uses Find() to find the caret, and return
all the characters to the left .