Deleting some text from a cell
 

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!

Deleting some text from a cell
 

On Mon, 19 Mar 2007 07:58:56 -0500, "Tom Petersen"
wrote:

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!


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

Deleting some text from a cell
 

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)))



"Tom Petersen" wrote in message
...
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!

Deleting some text from a cell
 

On Mon, 19 Mar 2007 12:52:36 -0400, "PCLIVE"
wrote:

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

Deleting some text from a cell
 

On Mon, 19 Mar 2007 12:13:18 -0400, Ron Rosenfeld
wrote:

On Mon, 19 Mar 2007 07:58:56 -0500, "Tom Petersen"
wrote:

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!


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

Oops. Should be:

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


--ron

Deleting some text from a cell
 

On Mon, 19 Mar 2007 12:52:36 -0400, "PCLIVE"
wrote:

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)))




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