New Contributor

# Looking for Similar Characters in Cell

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

# Re: Looking for Similar Characters in Cell

In C1:

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

# Re: Looking for Similar Characters in Cell

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

# Re: Looking for Similar Characters in Cell

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

# Re: Looking for Similar Characters in Cell

It will be

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

# Re: Looking for Similar Characters in Cell

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

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