Searching by a single word in a cell

[andyfresh]

Hi,

Im working within excel and have 2000+ lines of data. What im trying to do is have the system automatically search for a match between the variant and any other cells. However the vairent may have between two and four words and I would like to to always search by the second word in that cell only.

What I have so far is:

Dim value As Variant
Dim cellrange As Variant

value = Range("A680").value
cellrange = Range("A680").Address

Cells.Find(What:= _
value _
, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:= _
xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) _
.Activate

Is there any way of searching by a particular word within a cell??

Many thanks

Andy

 

[Loomah]

Hi
This rather cumbersome formula should give you the 2nd word in a cell provideing there is only one space between words. I am in no doubt that it could be tidied by someone better than me.

=IF(ISERROR(TRIM(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)))),RIGHT(A1,LEN(A1)-FIND(" ",A1)),TRIM(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1))))

However, I'd look at using the Join function to create an array of your cell data and taking the second element of that array. At least that's what I think I would do but as I only have xl97 here and that function didn't exist then I'm unable to confirm it as a good way forward!!

Good Luck!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[PHV]

You may try to replace this:
value = Range("A680").value
by something like this:
value = Split(Range("A680").value)(1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Loomah]

PHV
Just the sort of thing I was trying to get at but couldn't prove it to myself!!!!
Apart from me suggesting using Join.
MMMMMMMmmmmmmmmmmmmm

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​