This works really well, thank you, however I need to also grab the first word
of the cell. I have tried 0 and -1 but that doesn't seem to work.
Can you help with this one?

"JBeaucaire" wrote:

Just to be clear about changing it if you want to for other purposes, here is
the version that would always return the FOURTH word:

=MID(MID(MID(SUBSTITUTE(A1," ","^",3),1,256),
FIND("^",SUBSTITUTE(A1," ","^",3)),256),2,
FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",3),1,256),
FIND("^",SUBSTITUTE(A1," ","^",3)),256))-2)
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JBeaucaire" wrote:

Lengthy, but it always returns the second word from the string searched:

=MID(MID(MID(SUBSTITUTE(A1," ","^",1),1,256),
FIND("^",SUBSTITUTE(A1," ","^",1)),256),2,
FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",1),1,256),
FIND("^",SUBSTITUTE(A1," ","^",1)),256))-2)

Whichever word you want in the string, change the single digit in the
SUBSTITUTE to that number -1. So to get the 3rd word, change all the 1s to 2s.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Christie" wrote:

I have quite a few cells that have alot of information in them eg surname,
christian name and address. I only need the surname and christian name in one
cell. How can I do this?
eg Smith Jones 123 Harper St Manly (A1). I need the cell to only show Smith
Jones (B1)