Using Instr to compare 2 fields

Vexorg

Board Regular
Joined
Oct 5, 2010
Messages
116
Hello,

I need a query that will compare the 'Work Trading Name' to the 'Email1' field. I want to find all emails that do not have a similar domain.

<TABLE style="WIDTH: 543pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=723 border=0><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-LEFT-COLOR: #d4d0c8; WIDTH: 543pt; BORDER-TOP-COLOR: #d4d0c8; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" width=723 colSpan=5 height=17>CORE

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: silver" height=20>First Name</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT-COLOR: black; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Last Name</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT-COLOR: black; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Team Name</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT-COLOR: black; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Work Trading Name</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT-COLOR: black; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Email1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Rey</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">ING INVESTMENT MANAGEMENT AMERICAS</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@inginvestment.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Andrés</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">MON GESTIO</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@andbanc.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abhijit</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">BLUEBAY ASSET MANAGEMENT</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@globop.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Michelle</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">BROADMARK ASSET MANAGEMENT</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@broadmarkasset.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Michael</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">WADDELL & REED</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@waddell.com</TD></TR></TBODY></TABLE>



In the results I would like the following:


<TABLE style="WIDTH: 543pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=723 border=0><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 212pt; mso-width-source: userset; mso-width-alt: 10313" width=282><COL style="WIDTH: 143pt; mso-width-source: userset; mso-width-alt: 6985" width=191><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=74 height=20>Andrés</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; WIDTH: 71pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=95>abc</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; WIDTH: 61pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=81></TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; WIDTH: 212pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=282>MON GESTIO</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; WIDTH: 143pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=191>abc@andbanc.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abhijit</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">BLUEBAY ASSET MANAGEMENT</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@globop.com</TD></TR></TBODY></TABLE>



I tried the one below but was unable to get it to work properly.

Code:
select core.*
from core
WHERE instr(1,core.[Work Trading Name],core.[email1],1)=0
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
The key word in your post is similar. What do you mean by that, exactly?



Rich (BB code):
select core.*
from core
WHERE 
InStr([email1],Mid(Replace([Work Trading Name]," ",""),1,5))=0


This will select records where

after removing spaces form the [Work Trading Name]
try to find the string of the first 5 characters of the [Work Trading Name]
anywhere in the [email1]

If you want to change the length of the string to match
change the 5 with your choice.
 
Last edited:
Upvote 0
I went ahead and created two functions: one to get the company name out of the email address and one to strip spaces.

Code:
Public Function GetCompanyEmailName(sEmail As String) As String
'return the company name from an email address
Dim temp As String
temp = Split(Split(sEmail, "@")(1), ".com")(0)
GetCompanyEmailName = temp
End Function

Public Function StripSpaces(str As String) As String
StripSpaces = Replace(str, " ", "")
End Function

These are called from the query like in jack's reply.

Code:
SELECT Core.*
FROM Core
WHERE (((InStr(StripSpaces([WorkTradingName]),GetCompanyEmailName([Email1])))=0));

hth,

Rich
 
Upvote 0
Hmm im having trouble making the function work Revans. Im trying to insert it has a module (the same way i would do a normal macro) but i cant seem to get it to work.
 
Upvote 0
Couldn't you use the Like operator?
 
Upvote 0
By "macro" I hope you mean VBA; I haven't written a macro in ... 12+ years. Open up a new module and paste them in there. Save the module with whatever name you like, "Module1", "basUtility", or something.

I suppose Like would work too, but you'd still need to take out the spaces so the email domain would match. I like the function route because these kinds of requirements have a way of creeping and it's easier to play in a function than in a query, imo. Mileage varies.

hth,

Rich
 
Upvote 0
Rich

Agree about the code but most text functions that are available in VBA are also available for expressions.

Not all of course.:)
 
Upvote 0
By "macro" I hope you mean VBA; I haven't written a macro in ... 12+ years. Open up a new module and paste them in there. Save the module with whatever name you like, "Module1", "basUtility", or something.

I suppose Like would work too, but you'd still need to take out the spaces so the email domain would match. I like the function route because these kinds of requirements have a way of creeping and it's easier to play in a function than in a query, imo. Mileage varies.

hth,

Rich

Yes, thats what i meant. Ive copied the code into a new module but when I select run, A box appears that asks me for the name of a macro.
 
Upvote 0
Yes, thats what i meant. Ive copied the code into a new module but when I select run, A box appears that asks me for the name of a macro.
That sounds strange. What exactly does the message say ... ? (I'm afraid I don't really believe it says "What is the name of a macro?" ;) )
 
Upvote 0
That's what Run does; I've never found it all that useful. You'll want to call the function from your query like this

Code:
SELECT Core.*
FROM Core
WHERE (((InStr(StripSpaces([WorkTradingName]),GetCompanyEmailName([Email1])))=0));

hth,

Rich
 
Upvote 0

Forum statistics

Threads
1,212,138
Messages
6,106,167
Members
448,002
Latest member
stig20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top