Looking for Similar Characters in Cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2058448%22%20slang%3D%22en-US%22%3ELooking%20for%20Similar%20Characters%20in%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2058448%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20some%20help%20writing%20a%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20one%20cell%2C%20let's%20call%20it%20A1%2C%20is%20a%205-digit%20number.%20In%20another%20cell%2C%20let's%20call%20it%20B1%2C%20is%20a%20list%20of%205-digit%20numbers%20separated%20by%20commas.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA1%20-%2012345%3C%2FP%3E%3CP%3EB1%20-%2012222%2C23456%2C34531%2C54356%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20goal%20is%20to%20have%20a%20formula%20that%20will%20look%20at%20the%20first%202%20digits%20of%20every%205-digit%20number%20separated%20by%20a%20comma%20in%20B1%20and%20see%20if%20it%20matches%20the%20first%202%20digits%20of%20A1.%20If%20there%20is%20a%20match%2C%20I'd%20like%20a%20%221%22%2C%20and%20a%20%220%22%20if%20there%20is%20no%20match.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20me%20write%20this%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2058448%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2058485%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20Similar%20Characters%20in%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2058485%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928246%22%20target%3D%22_blank%22%3E%40jllucas2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20C1%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(ISNUMBER(FIND(%22%2C%22%26amp%3BLEFT(A1%2C2)%2C%22%2C%22%26amp%3BB1))%2C1%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2062156%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20Similar%20Characters%20in%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2062156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BIf%20the%20commas%20were%20replaced%20with%20carrots%2C%20(%5E)%20in%20cell%20B2%2C%20would%20this%20formula%20still%20work%20if%20I%20change%20the%20commas%20to%20carrots%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2062161%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20Similar%20Characters%20in%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2062161%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20anyone%20tell%20me%20how%20to%20do%20this%20if%20the%20commas%20were%20replaced%20with%20carrots%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all,

 

I need some help writing a formula.

 

In one cell, let's call it A1, is a 5-digit number. In another cell, let's call it B1, is a list of 5-digit numbers separated by commas. 

 

A1 - 12345

B1 - 12222,23456,34531,54356

 

My goal is to have a formula that will look at the first 2 digits of every 5-digit number separated by a comma in B1 and see if it matches the first 2 digits of A1. If there is a match, I'd like a "1", and a "0" if there is no match.

 

Can anyone help me write this? 

5 Replies

@jllucas2 

In C1:

 

=IF(ISNUMBER(FIND(","&LEFT(A1,2),","&B1)),1,0)

@Hans Vogelaar If the commas were replaced with carrots, (^) in cell B2, would this formula still work if I change the commas to carrots?

Can anyone tell me how to do this if the commas were replaced with carrots?

@jllucas2 

It will be

=IF(ISNUMBER(FIND("^"&LEFT(A1,2),"^"&B1)),1,0)

@jllucas2 

In general, replace both instances of "," with the delimiter in B2.

If it is ^, use "^". If it is |, use "|", etc.