Compare 2 cells MID unknown location

[poikl]

Hi, Can you please help me structure my Conditional Format? I have (with MREXCEL's help, Marcelo)-OR(MID(G2,5,3)=LEFT(H2,3),MID(G2,5,3)=LEFT(I2,3),MID(G2,5,3)=LEFT(J2,3),MID(G2,5,3)=LEFT(K2,3)).
The problem is though that ColG source is from Manual entry where operators enter name, sometimes with Prefix "Mr.", "Mrs.", "Dr." or even "Doctor" and other times with minor misspellings. I'm trying to compare those names to various Customer Data Base, ColH, thru ColK. (That's why I created the "MID" function but as you see it won't catch all probable variations)
Is there a way to include in MID "seemingly alike" entries which may start not necessarily in position 5, but maybe 4, 6 or 7?

 

[poikl]

Important, Re: Compare 2 cells MID unknown location

Hi, Can you please try helping me here as you've most always in the past?

 

[JackDanIce]

Re: Important, Re: Compare 2 cells MID unknown location

You can use FIND, e.g.

A1: Two Words
A2: Three Words

=MID(A1,FIND(" ", A1)+1, 5)

 

[poikl]

Re: Important, Re: Compare 2 cells MID unknown location

Jack, Thank you for answering. Can you please though structure the formula so that it looks in G2 (somewhere in the cel) to find a match in 3 left letters of either H2, I2, J2 or K2?

 

[poikl]

Re: Important, Re: Compare 2 cells MID unknown location

Hi, I appologize but can you please give me (I'm not excel learned) the "exact" formula to put in the conditional format so if there's a match in either H2, I2,J2, or K2 for 4 continued letters in G2? So If H2 contains Harris Jones and G2 contains either Mr. Harris or Doctor Harris it will still recognize as a match?
I tried the "FIND" from Jack and I see how it might help but don't know how to structure the formula.

 

[poikl]

Hi, Please forgive me for remention but I haven't received a reply and the issue is quite important.
I'm trying to Conditional Format G2 if any 5 consecutive letters in G2 match the leftmost letters in either H2, I2, J2, or K2.
I had used MRExcels (Marcelo) formula OR(MID(G2,5,3)=LEFT(H2,3),MID(G2,5,3)=LEFT(I2,3),MID(G2,5,3)=LEFT(J2,3),MID(G2,5,3)=LEFT(K2,3)) but that only matches if if it's in the 5th location but truthfully the match can be anywhere in Cel G2 (the 3rd, 4th 5th 6th etc.
Is there any way that I can find the match even though the location in respective cel is unknown at that time?

 

[poikl]

With EXAMPLE Please help! Re: Compare 2 cells MID unknown location

Hi, Please forgive me for remention but I haven't received a reply and the issue is quite important.
I'm trying to Conditional Format G2 if any 5 consecutive letters in G2 match the leftmost letters in either H2, I2, J2, or K2.
I had used MRExcels (Marcelo) formula OR(MID(G2,5,3)=LEFT(H2,3),MID(G2,5,3)=LEFT(I2,3),MID(G2,5,3)=LEFT(J2,3),MID(G2,5,3)=LEFT(K2,3)) but that only matches if if it's in the 5th location but truthfully the match can be anywhere in Cel G2 (the 3rd, 4th 5th 6th etc.
Is there any way that I can find the match even though the location in respective cel is unknown at that time?

Hi It seems to me that I wasn't clear with my original question so I'm including an example and hoping a MREXCEL member will help resolve my urgent problem:
A------------B--- C---- D---- E---- F------------G-----------------------H----------------------I------------------------ J K
Cust#------Date--Site--Qty--Zip--Apt--------Name-------------------File1-------------------File2-------------------File3
123---------------------------------------------P.T. Barnum---------- Barnum & Baily------ Hollywood
123---------------------------------------------Barnum Baily--------- Barn. & Baily-------Hollywood
123---------------------------------------------Circus, Barnum Baily---Stromer-------------- Daisy-----------------NY/Barnum & Baily
123---------------------------------------------Farragut-----------------Barnum & Baily
Can you please help me enter a Conditional Format which will identify as a match and color Cels; G2, G3,and G3 "blue" due to acceptable "similar"match found in either Cols H, I, J, or K in respective rows (even though inexact)?

 

[FormR]

I'm trying to Conditional Format G2 if any 5 consecutive letters in G2 match the leftmost letters in either H2, I2, J2, or K2.

Hi, here is one option you could try with the "With a formula" method:

=ISNUMBER(LOOKUP(1,-SEARCH(MID(G2,ROW(INDIRECT("1:"&LEN(G2)-4)),5),"|"&H2&"|"&I2&"|"&J2&"|")))

 

[poikl]

Formr, Simply genius, thank you very much

 

[poikl]

Formr, Simply genius, thank you very much

Hi Formr, thank you again for your reply which mostly works perfect, but I'm stumped why it doesn't recognize the following (2), G2-BELL H2-Bell Materials and similarly G2-V.TANE H2-V. Tane
as a match???