Excel 2002 – text extraction issue

[robertsmyth100]

Hello everybody,

I have a small issue that I was hoping for some help with. Basically I have a list of words which contain certain words that I wish to extract and put in another column. The words are in the text in different places (start, middle, end) and some are separated by a space and sometimes not. The list of words that I want to extract are in separate table. So for example my list of words are:

Acid nig
acid hep
hat acid
alki hed
alkitex
ttpf alki
pet dev
pethep
nig pet

And the list of words I want to extract and put in a column next to them are:
acid
alki
pet

So my final table would look a like this.
Acid nig acid
acid hep acid
hat acid acid
alki hed alki
alkitex alki
ttpf alki alki
pet dev pet
pethep pet
nig pet pet

this table didnt come out very well but bascially the third word in each row is the 2nd column.

Any help would be mucho appreciated…

 

[Richard Schollar]

The 1/Search bit will return an array of error values and numbers less than or equal to 1 (and above zero), therefore the 2 as the lookup value will always return the position of the highest numerical value in the search array that is less than 2 (and they all will be because 1 is being divided by a number greater than or equal to 1).

Crap explanation, but does it help?

That's why I also posted the 9.999+E307 as a possibility, as this will always be greater than the length of a string.

Richard

 

[robertsmyth100]

cheers people! all works great... glad to see i have started a bit of discussion aswell!

 

[barry houdini]

As maximum number of characters in a cell is 32767 you can also use

=LOOKUP(2^15,SEARCH(D$1:D$41,A1),D$1:D$41)

For an explanation of the LOOKUP(2,1/..... construction see Aladin's contribution here

http://www.mrexcel.com/board2/viewtopic.php?t=103168&highlight=brilliant

 

[Richard Schollar]

Thanks Barry - the link filled in the gaps for me.

 

[plettieri]

Hi Barry and Richard,

Thanks for your explainations (and to Aladin) ...I will study them and save them for future reference....a great help to me and all who read this thread.

plettieri