Extract first second "word" from a Cell..

[Guest]

I need to extract the second "word" from a Cell.

The data comes from a database, which I do have some control over. I'm
importing into Excel so that I can create new "elements" in order to do
charts.

One column, which seems so simple, is giving me problems.

The first two words in a column will always look like this:

PM Smith asdfasdfasfasdf (where asdf... is free text.)

I want extract the "Name" and create a new column with this value. I'm
hoping to avoid creating a bunch of nested "IF" statements searching for all
possible names.

Thanks,

KSL

 

[Guest]

Extracts the Nth word

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

Number of word to extract in A1
Senetnce in A2.

Mike

 

[Guest]

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)

 

[Guest]

Perfect. I finally figured out how/why this works...and my brain hurts!

Thanks!

KSL.

 

[Rick Rothstein \(MVP - VB\)]

I need to extract the second "word" from a Cell.

Perfect. I finally figured out how/why this works...and my brain hurts!

If you like having your brain hurt, you might find this formula
entertaining<g>...

=SUBSTITUTE(SUBSTITUTE(A1,MID(A1,FIND(" ",A1,FIND("
",A1)+1),1024),""),LEFT(A1,FIND(" ",A1)),"")

Rick

 

[Guest]

I tried it, and yes to both:

1. IT works!
2. IT Hurts! (I've no clue how this one works!

KSL.

 

[Rick Rothstein \(MVP - VB\)]

The inner SUBSTITUTE replaces everything after the second blank space (the
two embedded FIND functions locate where that is) with an empty string and
the second SUBSTITUTE works on the value returned from that operation (which
is nothing more than your first two words) by replacing everything to the
left of and including the first blank space with and empty string.

Rick