Forum Discussion

Copper Contributor

Mar 07, 2022

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...

excel

Formulas and Functions

office 365

OliverScheurich
Mar 08, 2022
AkiraYama
=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.

OliverScheurich

Gold Contributor

Mar 08, 2022

AkiraYama

=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.

AkiraYama

Copper Contributor

Mar 08, 2022

That one works. The only adjustment to your example would be that I made the $F$1 into G1 and made G1 =LEFT(B1,19) so that the variable would track along with each line and could be adjusted per line if say, one of the lines had 10 consistant symbols instead of 19, or if the consistant symbols were on the right side instead, which of course means a little adjustment to the larger formula as well, but thank you, you definiately put me right next to the goal!