SOLVED

Excel - Comparing the first 19 letters of strings in cell to see if they match

New Contributor

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 arguements sake lets say "C:\Directory1\abcd\" but the sub folder inside abcd changes in each cell. I need to confirm that the parent path above said sub folder (imagine C:\Directory1\abcd\subfolder1) is the same across the board with no typos, while everything following the first 19 characters is expected to be different. I know this involves something like LEFT(B3,19) but do not know how to evaluate further than that. Of the tutorials I have found, most seem to just compare two cells, not multiple. Any help would be amazing! Thank you in advance!

5 Replies

@AkiraYama 

=LEFT(B3,19)=$E$1

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

@AkiraYama 

 

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

 

 

@Quadruple_Pawn & @mathetes 
I love those answers. They don't exactly fit  but that is my fault. I failed to specify that:

  1. The list of directories are listed directories in a row and don't have to be imported
  2. 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.
  3. 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.

 

Analysis Sample.PNG

best response confirmed by AkiraYama (New Contributor)
Solution

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

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!