ignore last 18 characters & use as search string

ignore last 18 characters of cell contents and use everything before to search on...

for example i current have this line of code currently

Please Login or Register  to view this content.

In list :
Apple.in.spintal.com.au <---- this is used to seach against the users input

User input
Apple


Need the search to be able ignore the last 18 characters (.in.spintal.com.au) and check if Apple appears in the Users input range.

ive tried xlpart though with & ".", though this doesnt work either.. not sure how to use Len, Left and Right type paramaters etc.

Can someone please advise what i need to modify the above line of code to in order to do the above?

thanks

hi shg,

Thanks for your reponse,

I was thinking rather than ignoring the last 18 chars, it would be better to ignore everything from the '.' and onwards.

ce.offset(01).Value is the current cell, and i want to be able to do the above to the contents of it which is being used as the search item.

The search item (ce) then looks into the users input range --->

Please Login or Register  to view this content.

.

The user input usually only have the name for example: 'apple' and not 'apple.in.spintal.com.au'

This is i try and see if 'apple.in.spintal.com.au' matches with 'apple' there isnt a match, when in reality there should be.

is there a way to some how only use the apple part of ce value and ignore the '.XXXXXXXXX'?

Hi

If you want the word Apple to be found if it is anywhere in the text string, then change

Please Login or Register  to view this content.

to be

Please Login or Register  to view this content.

Or do you really only want to look at the string before the first "."?

rylo

That would be the solution if i was using the users input 'apple' as the Ce to find in the list.


Though I am actually using the list with the 'apple.in.spintal.com.au' to see if it matches with the users input 'apple'.

Hi

Got things wrong way round.

If ce.offset(0,1).value has Apple.in.spintal.com.au
and you want to cut that down to Apple
there are various ways to break out that part. Here's one

Use SPLIT to break it out into the parts, and use the first bit

Please Login or Register  to view this content.

then the search would be

Please Login or Register  to view this content.

rylo

Sure thing,

I have attached the xls doc.

Result page = lists to search on
Cross ref data = users input
Cross ref results = finds all matches and non matches.


When you open the document you will see in the Cross ref results page that 'playstation' and 'shoe' are actually matches, though the current code I have to reverse lookup works for everything without a '.xxxxxxx' perfrectly, though if there is a '.xxxxxxxxxx' the part of the code that writes out the non matches currently inst equiped to under stand that 'playsation' and 'shoe' do actually occure.

Thanks for your input rylo,

See what you are suggesting though i am having some trouble getting it to work, i tried to delare the array though i am not sure if it will work with my code as i keep getting array expected errors even when I delare one.

hi shg,

It should go to Results column E, get the cell value name.something.sd.sd.com and check if it is in Cross Refer data column A.

its does this currently though as Results column E has '.XXXXX.XX.XX.XX' (as above) it does not find the match and will write out the line to the Cross Ref Results page.

So the best way i thought is to some how ignore the '.' and every thing after it so that if there is a match then the line wont be written.

The only other way which is okay (if we can do the above) is to some how trim down column E results page so that if there are cells in column E with a '.XXXX.XX.XX.' to remove it and only leave everything ebore the '.XXXX.XX.XX.'.

That is a work around... either of those soultions would be handy if you can some how incorporate into my code, but the 1st is the most desirable if i was a fussy bother

Hi

What you have to do is a test on the entry in results before you try to search for it in Cross Ref Data. Use INSTR, and look for a ".". If one is found (the result will be > 0), then substring the entry from Cross Ref Data to extract out only that part before the ".". Then put that into your search.

Something like

Please Login or Register  to view this content.

I didn't work it through, but I think that should resolve it.

rylo

i incorportated it (used the same names and declared thingtofind) though got object required error on

on this line :

Please Login or Register  to view this content.

Please Login or Register  to view this content.

its okay,

Thanks for all your help as it helped me get to this point. you guys are really helpful and put up with my 'greeness' so to speak

Ive just
put another sub in and called it to remove the everything after the '.' from Column E.

Please Login or Register  to view this content.

That removed all the .'s so that i didnt have to worry about the other part.