Mar 07 2022 02:24 PM
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!
Mar 07 2022 02:49 PM
=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.
Mar 07 2022 02:51 PM
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
Mar 08 2022 08:10 AM - edited Mar 08 2022 08:12 AM
@OliverScheurich & @mathetes
I love those answers. They don't exactly fit but that is my fault. I failed to specify that:
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.
Mar 08 2022 08:55 AM
Solution=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.
Mar 08 2022 10:40 AM
Mar 08 2022 08:55 AM
Solution=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.