Forum Discussion
Solved
Excel - Comparing the first 19 letters of strings in cell to see if they match
I am trying to create an evaluation cell that says something akin to true or false if the first 19 characters of the value of the cell are equal. For context, The values are directory paths, for argu...
=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.
=LEFT(B3,19)=$E$1A simple solution could be this formula as shown in the attached file. In cell E1 you can enter the main directory path. Then copy the formula down as required. The formula returns TRUE (WAHR in german Excel) if there is a match for the left 19 characters.