Search for Specific Words in a Cell

[pilot330]

Hello,

I'm using the following formula to extract specific words from a cell, in this case anything that contains ": stays". I would also like the same function to search and return other specific text in the same cell, for example anything that also contains ": should stay" or ": will stay:", but I'm having trouble doing so.

=TRIM(LEFT(SUBSTITUTE(MID(Z2,SEARCH(": stays",Z2)+2,255),":",REPT(" ",255)),255))

Any help appreciated please!

 

[shift-del]

Hi

=SUBSTITUTE(LOOKUP(9^9,SEARCH({": stays".": should stay".": will stay"},Z2),{": stays".": should stay".": will stay"}),": ","")

 

[pilot330]

Thanks for the reply. Apologies for perhaps not fully explaining what I need...

The formula would need to return *all* words between the colons that begin with ": stays ...."" , ": will stay...." e.g..... ": stays at the white house:" , ": will stay at the hotel:"

Thanks again

 

[jtakw]

Hi,

I think you should show at least a handful of sample text strings, preferably with XL2BB or at least in table format, so helpers can copy for testing, and explain exactly what you want extracted.

 

[shift-del]

It's always good to present up to 10 real life examples with the wished results.
It there more text before or after'? Are there more colons?
Are there numbers/dates?

 

[pilot330]

Thanks for the replies. A cells contents would typically look like this...

modest handicap hurdler: won at Galway (by 6½ lengths from Yamato) in September: 150/1, tailed-off fifth of 7 to Haut En Couleurs in maiden at Leopardstown on chasing debut: stays 25f: acts on heavy going.

smallish gelding: winning hurdler: fair form over fences: left Joseph Patrick O'Brien/off 22 months, looked rusty when well held last time: will stay 2¾m: acts on good to soft going: has worn tongue tie, including usually of late.

useful-looking gelding: winning hurdler: fairly useful chaser: let down by jumping when pulled up last time: should stay 23f: acts on soft going.

 

[jtakw]

Hi,

Thanks for posting samples, I'm assuming those are 3 sample text strings in 3 separate cells, will this work for you:

Book3.xlsx
	A	B	C
1	modest handicap hurdler: won at Galway (by 6½ lengths from Yamato) in September: 150/1, tailed-off fifth of 7 to Haut En Couleurs in maiden at Leopardstown on chasing debut: stays 25f: acts on heavy going.		stays 25f
2	smallish gelding: winning hurdler: fair form over fences: left Joseph Patrick O'Brien/off 22 months, looked rusty when well held last time: will stay 2¾m: acts on good to soft going: has worn tongue tie, including usually of late.		will stay 2¾m
3	useful-looking gelding: winning hurdler: fairly useful chaser: let down by jumping when pulled up last time: should stay 23f: acts on soft going.		should stay 23f
Sheet973

Cell Formulas
Range	Formula
C1:C3	C1	=TRIM(LEFT(SUBSTITUTE(MID(A1,1/LOOKUP(2,1/SEARCH({": stay",": will stay",": should stay"},A1))+2,99),":",REPT(" ",99)),99))

 

[pilot330]

That's perfect. Thanks very much. Appreciate it!

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B
1
2	modest handicap hurdler: won at Galway (by 6½ lengths from Yamato) in September: 150/1, tailed-off fifth of 7 to Haut En Couleurs in maiden at Leopardstown on chasing debut: stays 25f: acts on heavy going.	stays 25f
3	smallish gelding: winning hurdler: fair form over fences: left Joseph Patrick O'Brien/off 22 months, looked rusty when well held last time: will stay 2¾m: acts on good to soft going: has worn tongue tie, including usually of late.	will stay 2¾m
4	useful-looking gelding: winning hurdler: fairly useful chaser: let down by jumping when pulled up last time: should stay 23f: acts on soft going.	should stay 23f
Lists

Cell Formulas
Range	Formula
B2:B4	B2	=FILTERXML("<k><m>"&SUBSTITUTE(A2,":","</m><m>")&"</m></k>","//m[contains(.,'stay')]")

 

[jtakw]

That's perfect. Thanks very much. Appreciate it!

You're welcome.