Looking for Similar Characters in Cell

Copper 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.