Extract first second "word" from a Cell..

[Leonhardtk]

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

 

[Mike H]

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

 

[Teethless mama]

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

 

[Leonhardtk]

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

 

[Leonhardtk]

I tried it, and yes to both:

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

KSL.