Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting cell data without deleting formula | Excel Discussion (Misc queries) | |||
how prevent formula in cell from deleting when deleting value???? | New Users to Excel | |||
deleting a Text Box in a worksheet | Excel Worksheet Functions | |||
deleting certain text from cells | Excel Worksheet Functions | |||
Deleting 3 Text characters from the right | Excel Worksheet Functions |