Find specific text

[mattyman]

Hello All

I need to pull a specific word from a string of text in a cell and have that word shown in an adjacant cell.
For example A1 will contain the text "Smith Sun Alliance Pension Fund"
I need B2 to show "Pension". I cannot use any filtering or text to columns as the word Pension can be anywhere within the text in A1 and I have thousands of entries. So I need a function.

Help would be as always greatly appreciated.

Thanks

Matt

 

[Mala]

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.

 

[Aladin Akyurek]

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","")

 

[mattyman]

Thanks Aladin that's it precisely.

 

[mattyman]

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

 

[just_jon]

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.

 

[Aladin Akyurek]

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

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

 

[mattyman]

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

 

[just_jon]

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

 

[mattyman]

I think thats it Jon

Thanks