Search for text within text

Mashtonsmith

Board Regular
Joined
Oct 1, 2004
Messages
164
Hi,

I have a situation where I need to confirm that an email address in column D matches the surname in column A.

Clearly the email address will contain the Surname, First name and the remainder of the email address. I ideally need it to return a true result if column A contains the surname contained within the text of column D.

Help?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,868
Office Version
  1. 365
Platform
  1. Windows
Or if you have a consistent format in the e-mail address, e.g. firstname.lastname@email.com, then perhaps something like this?
Book9
ABCD
1E-mailLastNameVlookup
2firstname.lastname@email.comlastnameOk
3jon.vonderheyden@siemens.comvonderheydenOk
4
5
6Other list
7lastname
8vonderheyden
Sheet1


To get the surname: MID(A2,FIND(".",A2)+1,FIND("@",A2)-FIND(".",A2)-1)
The lookup: =IF(ISNA(VLOOKUP(B2,$A$7:$A$8,1,0)),"Error","Ok")

Regards,
Jon :)
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174

ADVERTISEMENT

Or try: =IF(ISNA(MATCH("*"&A1&"*",D:D,0)),"no match","ok")
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Or if you are likely to get blanks in column A try: =IF(OR(ISNA(MATCH("*"&A1&"*",D:D,0)),ISBLANK(A1)),"no match","ok")
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, all,

alternatively
  A                            B     C  D             
1                                       checklist     
2 firstname.lastname@group.com FALSE    hello         
3 checkme.von.goat@seller.be   TRUE     dear          
4 anotherone.nothing@board.fr  TRUE     correspondent 
5                              FALSE    checkme       
6                                       what          
7                                       other         

test

[Table-It] version 06 by Erik Van Geit

Code:
RANGE FORMULA (1st cell)
B2:B7 =NOT(ISNA(LOOKUP(99999,SEARCH($D$2:$D$7,A2),$D$2:$D$7)))

[Table-It] version 06 by Erik Van Geit

REMARK: beware of partial matches like "other"

kind regards,
Erik
 

Forum statistics

Threads
1,147,734
Messages
5,742,861
Members
423,760
Latest member
photogfrog

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
Top