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.
OliverScheurich & mathetes
I love those answers. They don't exactly fit but that is my fault. I failed to specify that:
- The list of directories are listed directories in a row and don't have to be imported
- The fact that these directories are indeed in a row and I am trying to evaluate if they ALL match within one cell to the right.
- There are multiple similar rows like this that I would want to copy this formula down to dynamically work on another
Your answers were very useful though. I especially didn't know I could use $A$1 to make that specified cell static across the copy/paste operation so that is pretty cool. I was going to attach the excel file but that didn't work in here, and somehow neither did a pdf even though that seemed to work for Quadruple, so instead hopefully this screenshot png suffices. Oh and the lists in reality (opposed to the sample image below) are about 17 entries long btw so doing a copy paste for each horizontal element would be kind of a mess in this particular instance.
=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.
- 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!