Find specific text

M

Mala

MrExcel MVP

Joined

Apr 4, 2002

Messages

627

• May 16, 2004

• #2

Please specify if you need to extract the word by position (e.g. say the 4th word) - or by text (e.g. 'myword')

And....
Of course you can use auto-filter to find / filter records containing the word you are looking for in one specific column (say A) - in the filter conditions dropdown just specify (for example)Contains 'Pension' - this'll find all records containing the word anywhere within the individual cell entries in that column.

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• May 16, 2004

• #3

The way you describe your query boils down to testing a string whether it contains a substring of interest...

=IF(COUNT(SEARCH("Pension",A1)),"Pension","")

 

Upvote 0

M

mattyman

New Member

Joined

Feb 15, 2004

Messages

22

• May 16, 2004

• #4

Thanks Aladin that's it precisely.

 

Upvote 0

M

mattyman

New Member

Joined

Feb 15, 2004

Messages

22

• May 16, 2004

• #5

Hi Aladin et all

The formula works well but how would I adapt it to check different words in the same cell. In other words to nest IFS to check say four words in a string.
EG =IF(COUNT(SEARCH("Pension",A1)),"Pension","") Works to pick out Pension in the cell that contains Sun Pension but if I wanted to check other words in the same column that I know it will contain if it does not contain Pension ie Hedge or something

Cheers

Matt

 

Upvote 0

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• May 16, 2004

• #6

Maybe --

x1.xls
	A	B	C	D
1	thishastwokeywords	Yes	key
2	thishasnone	No	word
Sheet1

This is an array formula, which requires entry via Control+Shift+Enter, not just Enter.

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• May 16, 2004

• #7

mattyman said:

Hi Aladin et all

The formula works well but how would I adapt it to check different words in the same cell. In other words to nest IFS to check say four words in a string.
EG =IF(COUNT(SEARCH("Pension",A1)),"Pension","") Works to pick out Pension in the cell that contains Sun Pension but if I wanted to check other words in the same column that I know it will contain if it does not contain Pension ie Hedge or something

Cheers

Matt

Click to expand...

Four different words meaning the same thing or different things? What is the complete list of substrings you want test a cell against?

 

Upvote 0

M

mattyman

New Member

Joined

Feb 15, 2004

Messages

22

• May 16, 2004

• #8

Hi Aladin

EG =IF(COUNT(SEARCH("Pension",A1)),"Pension","")

Works perfectly it takes the keyword from the string.But
i need to nest the IFS so they search for more than the "Pension" criteria

So in each adjacent cell the formula does exactly as the above but searches for Pension and then Arbritage and then Investment returning each as it is found.Remember that each cell will ONLY contain "Pension" or "Arbritage" or "Investment " etc

Cheers

Matt

 

Upvote 0

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• May 16, 2004

• #9

On 2nd thought --

x1.xls
	A	B	C	D
1	thisonehaspension	pension		pension
2	andthisonehasinvestment	investment		arbitrage
3	andthishasnoneofthem	#N/A		investment
4	butthishasarbitrage	arbitrage
Sheet1

 

Upvote 0

M

mattyman

New Member

Joined

Feb 15, 2004

Messages

22

• May 16, 2004

• #10

I think thats it Jon

Thanks

 

Upvote 0