Viewing 5 posts - 1 through 5 (of 5 total)
  • Excel query of the day
  • FuzzyWuzzy
    Full Member

    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

    dissonance
    Full Member

    Misread it.
    This gives true/false

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

    rogermoore
    Full Member

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

    reggiegasket
    Free Member

    or a combination …

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

    FuzzyWuzzy
    Full Member

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

Viewing 5 posts - 1 through 5 (of 5 total)

The topic ‘Excel query of the day’ is closed to new replies.

Thanks for popping by - why not stay a while?IT'S FREE

Sign up as a Singletrack Member and you can leave comments on stories, use the classified ads, and post in our forums, do quizzes and more.

Join us, join in, it’s free, and fun.