Deleting some text from a cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom Petersen - 19 Mar 2007 13:58 GMT

I am trying to get a list of print jobs from event viewer scaled down to
just show what I want. I currently export this info into a spreadsheet

A 3/14/2007
B 8:09:21 AM
C SDSD\aldarajij
D Document 128, Microsoft Office Outlook - Memo Style owned by aldarajij was
printed on Lobby via port IP_10.1.1.104. Size in bytes: 553732; pages
printed: 1

Cell D has a lot of info I don't need, is there a function that I can just
extract the printer name (Lobby) and pages printed? I have about 8
printers, so I am guessing I would need some type of if statement, if =
lobby, if = color, if = toshiba etc. So when I am done it looks like:

3/14/2007 8:09:21 AM SDSD\aldarajij Lobby; pages printed: 1

TIA!

Reply to this Message

Ron Rosenfeld - 19 Mar 2007 17:13 GMT

>I am trying to get a list of print jobs from event viewer scaled down to
>just show what I want. I currently export this info into a spreadsheet
[quoted text clipped - 14 lines]
>
>TIA!

Here's one way:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use this formula:

(assumes your Cell D is A1, change as required):

=REGEX.SUBSTITUTE(
A1,".*printed on([\s\n]+?)(\w+)[\s\S]*(\d+$)","[2]; pages printed: [3]")

The formula also assumes that the page count is the very last numeric string in
Cell D. If this is not the case, we can certainly make a slight change.
--ron

Reply to this Message

Ron Rosenfeld - 19 Mar 2007 18:20 GMT

>>I am trying to get a list of print jobs from event viewer scaled down to
>>just show what I want. I currently export this info into a spreadsheet
[quoted text clipped - 30 lines]
>Cell D. If this is not the case, we can certainly make a slight change.
>--ron

Oops. Should be:

=REGEX.SUBSTITUTE(
A1,"[\S\s]*printed on([\s\n]+?)(\w+)[\s\S]*(\d+$)","[2]; pages printed: [3]")

--ron

Reply to this Message

PCLIVE - 19 Mar 2007 17:52 GMT

Here is an interesting formula that assumes there are no spaces in your
printer name. This is also based on the "D" information being in cell D1.

=MID(D1,FIND("printed on",D1)+11,(FIND(" ",D1,(FIND("printed
on",D1)+11))-(FIND("printed on",D1)+11)))

>I am trying to get a list of print jobs from event viewer scaled down to
>just show what I want. I currently export this info into a spreadsheet
[quoted text clipped - 14 lines]
>
> TIA!

Reply to this Message

Ron Rosenfeld - 19 Mar 2007 18:16 GMT

>Here is an interesting formula that assumes there are no spaces in your
>printer name. This is also based on the "D" information being in cell D1.
>
>=MID(D1,FIND("printed on",D1)+11,(FIND(" ",D1,(FIND("printed
>on",D1)+11))-(FIND("printed on",D1)+11)))

One potential problem with that method, which I ran in to when testing it
earlier, is that it can give unexpected results depending on where line breaks
exist in cell D1.

--ron

Reply to this Message

Ron Rosenfeld - 19 Mar 2007 18:22 GMT

And my original would also screw up depending on where line breaks existed. I
just posted a revision that takes care of that problem.
--ron

Reply to this Message