Deleting some text from a cell

[Tom Petersen]

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!

 

[Ron Rosenfeld]

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

 

[PCLIVE]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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