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.