I’m hoping this is a simple one…

In preparation for a data migration exercise I have several TreeSize reports down to file level, however TreeSize not only displays the files (and their sizes) but also the folders (and the size of the files and any sub-folders within) and also a wildcard *.* for the files in the root of the folder (and their total size). All fine when just viewing the info in Excel but I need to get the file size total based on certain criteria (initially those modified after a certain date) and if I don’t exclude these summary rows I end up counting file sizes multiple times. I can’t go through and manually remove these rows as there’s tens of thousands of rows

So basically I want to set a value in an empty column (e.g. Y or N) depending if the row relates to a file or a folder/wildcard. In a folder row the path always ends in “\” (and in a wildcard row it always ends in a “*”) the path string is of variable length though and “\” can appear within it multiple times.

I’m sure this is a simple but can anyone give me a query that checks the last character of a string in a column and sets a value in a new column based on that last character? The value to set just being Y or N depending if the last character matched Y = \ or * and N = anything else.

One I have the Y or N column populated I figure I can just convert to table, filter and sum the size column

Misread it.
This gives true/false

=OR((RIGHT(A2,1)=”*” ),(RIGHT(A2,1)=”/”))

Where A1 is the string you want to interrogate:
=IF(RIGHT(A1,1)=”*”,”Y”,IF(RIGHT(A1,1)=”/”,”Y”,”N”))
RM.

or a combination …

=IF(OR(RIGHT(A1,1)=”*”,RIGHT(A1,1)=”/”),”Y”,”N”)

Thanks all, went with Reggie’s version in the end and it works great 🙂