Event logs are a pain to manipulate (but I also find it fun...).

My office manager is trying to figure out who's utilizing our color printer the most, and has asked me for some info on it. I've gotten pretty far, but now I'm struggling with the more advanced formulas.

So far I've taken a csv of the event viewer, created a function to tell me which print jobs were sent to one specific printer, sorted by that column, and removed the other print jobs. So now I have a spreadsheet with date, time, username, and the print job.

The problem is that the description of the print job ends in the number of pages that were printed. In the column to the left of this description, I'd like to have a simple number representing the number of pages printed in that job. If it helps, the standard formatting of the messaged is, for example: "pages printed: 5" So the number is always the very last word/character in the cell (of course, many print jobs have over 200 pages, so it ranges from one digit to three).

Would someone be so kind as to help me with a formula to extract this information? I'd really like to avoid going through 554 lines and entering them manually This also might be something that needs to be done regularly, so this will save me a lot of time in the future.


Question #2:

This one is simpler, I'm just rusty with the graphing functions. The scenario is that I have a graph like the following:

John 4
Mary 2
Mary 10
Pete 15
John 7
Mary 12

How do I end up with a graph that shows users on the bottom and their total pages printed on the left? For example, going across the X axis we'd have John with 11 pages, Pete with 15 pages, and Mary with 24 pages.

Thanks for your help everyone. I like manipulating spreadsheets, but I'm bad about remembering how to create formulas. Does anyone have a good site for learning this stuff?

If you have the same text string for each (Pages Printed:) that you want trimmed, you can do a:

Code:

---

=RIGHT(A1, LEN(A1) - 14)

---

The A1 was my test cell and the 14 is the number of characters until the :

For the graphs, are the columns split such as name in one and the number in the other?

I believe that will just leave him with a text value that is "5" or whatever. Assuming he wants to use them as numbers, it would probably make sense to wrap that in a VALUE():
Code:

---

=VALUE(RIGHT(A1, LEN(A1) - 14))

---

Yeah, that's only for that exact string. If the only thing you know is that the number will be at the end and immediately preceded by "Pages printed: ", how about this:

Code:

---

=VALUE(RIGHT(A1,13-FIND(": ",RIGHT(A1,14))))

---

This assumes that the number is at the end of the text, immediately preceded by ": " (colon space), that it will be the only occurrence of ": " in the last 14 characters of the text (which is safe, assuming it's always preceded by "Pages Printed"), and that the number is less than 13 digits long.

This should really be a lot easier. It ought to be:

Code:

---

=VALUE(REGEX("\d+?$", A1))

---

But Excel's functions are limited.

Thanks, Andrew, but I went with Bitt's because it was shorter. That worked! Thanks to everyone for your help.

Any ideas on the graphing part?

Well, you have to get it in a more normalized form first. You could use DSUM() to add the matching values up for you, but you'd still have to know each of the names beforehand, and I can't think of any way to do that. I don't think that Excel provides a way to put values in multiple cells with a single function, which is what you'd have to do in order to get all the unique names. Perhaps there's some VB something you could do within Excel, though.

If people want to do this kind of thing - perl, awk, grep etc are all available on windows via cygwin.

They would have made light work of some of the pre-processing and allowed Excel to do the presentation....

Obviously 'job done' now - but it may be worth considering adding a few tools to your quiver.

Example spreadsheet attached.