| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract first second "word" from a Cell..
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract first second "word" from a Cell..
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 "Leonhardtk" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract first second "word" from a Cell..
=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)
"Leonhardtk" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract first second "word" from a Cell..
Perfect. I finally figured out how/why this works...and my brain hurts!
Thanks! KSL. "Teethless mama" wrote: =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1) "Leonhardtk" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract first second "word" from a Cell..
I need to extract the second "word" from a Cell.
=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1) 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract first second "word" from a Cell..
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)" wrote: I need to extract the second "word" from a Cell. =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1) 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract first second "word" from a Cell..
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 "Leonhardtk" wrote in message ... 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)" wrote: I need to extract the second "word" from a Cell. =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1) 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extract first to "works" from a Cell. | Excel Worksheet Functions | |||
| How to setup "marching ants" border Excel cell? OK in MS Word. | Excel Discussion (Misc queries) | |||
| Help!!! Enter "7" in a cell and Excel changes the "7" to "11" immediately!!! | Excel Discussion (Misc queries) | |||
| Why doesn't Excel "word wrap" always expand the cell fully? | Excel Discussion (Misc queries) | |||
| Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
Linear Mode
