Is it possible to count the number of shared characters in two cells / strings?

Question

I have a large number of strings in a pair of columns in a spreadsheet. I'd like to write a formula to tell me whether "enough" initial characters of one cell are also initial characters of the other cell. Basically, I'm looking for something that can say "these two strings start with the same N characters" and then I get to choose what N I think means "enough" so I can flag the row using magic text, conditional-formatting or whatever.

Since the "choose the N" and magic text/conditional-formatting are well understood, I only need to figure out the "count shared characters" part so I'll focus on that.

For example:

Column A	Column B	Formula Output
Foo	Bar	0
Foo	Food	3
Foo	Fud	1
Bar	Bark	3
Bar	baz	2
Bar	abar	0

What formula could be used to produce Column C from Columns A and B?

I can easily write a script/program outside of a spreadsheet to do this kind of thing, or maybe even a sufficiently complex SQL query if I load the data into a database first. But I'd like to deal with this at the spreadsheet level because (a) the data is already in a spreadsheet) and (b) this formula will be used by non-programmers once I get it working.

Answer 1

Unfortunately, as far I know, there is no quick way to achieve this. Please see the codes below for each cell:

=COUNTIF(B1,"*f*")+COUNTIF(B1,"*o*")+COUNTIF(B1,"*o*")
=COUNTIF(B2,"*f*")+COUNTIF(B2,"*o*")+COUNTIF(B2,"*o*")
=COUNTIF(B3,"*f*")+COUNTIF(B3,"*o*")+COUNTIF(B3,"*o*")
=COUNTIF(B4,"*b*")+COUNTIF(B4,"*a*")+COUNTIF(B4,"*r*")
=COUNTIF(B5,"*b*")+COUNTIF(B5,"*a*")+COUNTIF(B5,"*r*")
=COUNTIF(B6,"*b*")+COUNTIF(B6,"*a*")+COUNTIF(B6,"*r*")

---

Explanation:

• =COUNTIF counts how many times something appears

• B1, counts how many times it appears in cell B1

• *f* counts how many times the letter "f" appears, even if it preceded or succeeded by other text