Forum Discussion
Excel - Comparing the first 19 letters of strings in cell to see if they match
=SUMPRODUCT(--(LEFT(B2:E2,19)=$F$1))=COUNTA(B2:E2)Maybe with this formula that can easily be adapted to e.g. 17 columns.
The formula compares the count of cells where the left 19 characters are " C:\Directory1\abcd\ " with the number of cells that aren't empty in range B2:E2.
In addition you can apply conditional formatting to highlight cells if the left 19 characters aren't " C:\Directory1\abcd\ " which is the value in cell F1 in this example.
Without seeing how your data are arrayed, it's hard to be really specific.
But if you have one cell with the constant 19 character string [ for sake of illustration, I'll use your C:\Directory1\abcd\ example ], and if we assume that is in cell A1 and you want to compare that same string with entries in column B, specifically B1 through B20
You could put this into cell C1 and copy in down to C20
=IF($A$1=LEFT(B1,19),TRUE,FALSE)
That $A$1 reference is what's called an absolute reference, so it doesn't change as you copy down from cell C1, whereas the B1 does change...
https://exceljet.net/glossary/absolute-reference