You assumed correctly, and the resulting code works perfectly. Would
it be too much to ask for an explanation of how this formula works? I
understand parts of it, but I am trying to learn, instead of just
ripping the code off.
Again, thank you for this help!
Z
On Oct 24, 3:05 pm, Sam Mathai Chacko <samde...@gmail.com> wrote:
> No response. Had to assume. Here's the assumption as well as the file.
>
> =IF(LEN(A1)<>LEN(SUBSTITUTE(UPPER(A1),"REMARK","")),A1,LEFT(A1,MIN(IF(ISERROR(SEARCH("
> 0x",A1)),255,SEARCH(" 0x",A1)),IF(ISERROR(SEARCH(" (h",A1)),255,SEARCH("
> (h",A1)))))
>
> Regards,
>
> Sam Mathai Chacko (GL)
>
> On Tue, Oct 25, 2011 at 12:13 AM, Sam Mathai Chacko <samde...@gmail.com>wrote:
>
>
>
>
>
>
>
>
>
> > so if the line has a remark in it, then leave it as it is, otherwise,
> > remove anything that starts with (hitcnt or Ox?
>
> > Sam
>
> > On Tue, Oct 25, 2011 at 12:06 AM, Zeunasc <timothy.ry...@gmail.com> wrote:
>
> >> My apologies, I don't see how to attach a workbook to this post.
> >> Below is some sample code, as would be contained in column A. I tried
> >> to pick lines that would contain examples of all the different ways
> >> the data could be presented. No, there will not always be an eq 80.
> >> Best, I can tell, these are the absolutes:
>
> >> If a line has the word "remark" in it, it won't have anything at the
> >> end of the line
> >> All other lines will contain a 10 character "0x" string at the very
> >> end
> >> Lines that stand alone, or are breakouts of another line, will contain
> >> a "(hitcnt=xxx)" string before the 0x string to indicate how many
> >> times they have been used.
>
> >> access-list BldA-I line 313 remark 10/20/2011 permit tcp host user1
> >> host user2 range 1433 1434
> >> access-list BldA-I line 314 extended permit tcp host user1 host user2
> >> range 1433 1434 (hitcnt=0) 0xe306fa2b
> >> access-list BldA-I line 315 remark 10/20/2011 permit tcp host user3
> >> host user2 range 1433 1434
> >> access-list BldA-I line 316 extended permit tcp host user3 host user2
> >> range 1433 1434 (hitcnt=0) 0xc1268fad
> >> access-list BldA-I line 317 remark 10/20/2011 permit tcp host user4
> >> host user2 range 1433 1434
> >> access-list BldA-I line 318 extended permit tcp host user4 host user2
> >> range 1433 1434 (hitcnt=0) 0xee50e46b
> >> access-list BldA-I line 319 remark 10/20/2011 permit tcp host user5
> >> host user2 range 1433 1434
> >> access-list BldA-I line 320 extended permit tcp host user5 host user2
> >> range 1433 1434 (hitcnt=0) 0xb8fb5f62
> >> access-list BldA-I line 321 remark 10/20/2011 permit tcp host user6
> >> host user2 range 1433 1434
> >> access-list BldA-I line 322 extended permit tcp host user6 host user2
> >> range 1433 1434 (hitcnt=134) 0x134db637
> >> access-list BldA-I line 323 remark 10/20/2011 permit tcp host user7
> >> host user2 range 1433 1434
> >> access-list BldA-I line 324 extended permit tcp host user7 host user2
> >> range 1433 1434 (hitcnt=121) 0xfdcbb633
> >> access-list BldA-I line 325 remark 10/20/2011 permit ip host pc1
> >> object-group LAN1
> >> access-list BldA-I line 326 extended permit ip host pc1 object-group
> >> LAN1 0x45b67f42
> >> access-list BldA-I line 326 extended permit ip host pc1 10.9.9.0
> >> 255.255.255.0 (hitcnt=1986) 0x92e0ec3f
> >> access-list BldA-I line 326 extended permit ip host pc1 72.88.125.0
> >> 255.255.224.0 (hitcnt=2) 0xb5a1039f
> >> access-list BldA-I line 327 remark 10/20/2011 permit ip host pc2
> >> object-group LAN1
> >> access-list BldA-I line 328 extended permit ip host pc2 object-group
> >> LAN1 0x616f74d2
> >> access-list BldA-I line 328 extended permit ip host pc2 10.9.9.0
> >> 255.255.255.0 (hitcnt=0) 0xf86f7331
> >> access-list BldA-I line 328 extended permit ip host pc2 72.88.125.0
> >> 255.255.224.0 (hitcnt=0) 0x67724065
> >> access-list BldA-I line 329 remark 10/20/2011 permit ip host pc3
> >> object-group LAN1
> >> access-list BldA-I line 330 extended permit ip host pc3 object-group
> >> LAN1 0x37e5896
> >> access-list BldA-I line 330 extended permit ip host pc3 10.9.9.0
> >> 255.255.255.0 (hitcnt=0) 0x8ef8fef3
> >> access-list BldA-I line 330 extended permit ip host pc3 72.88.125.0
> >> 255.255.224.0 (hitcnt=0) 0x60499291
> >> access-list BldA-I line 331 remark 10/20/2011 permit ip host pc4
> >> object-group LAN1
> >> access-list BldA-I line 332 extended permit ip host pc4 object-group
> >> LAN1 0x19a0e9c5
> >> access-list BldA-I line 332 extended permit ip host pc4 10.9.9.0
> >> 255.255.255.0 (hitcnt=0) 0x78a7035
> >> access-list BldA-I line 332 extended permit ip host pc4 72.88.125.0
> >> 255.255.224.0 (hitcnt=0) 0x21453036
> >> access-list BldA-I line 333 remark 10/20/2011 permit ip host pc5
> >> object-group LAN1
> >> access-list BldA-I line 334 extended permit ip host pc5 object-group
> >> LAN1 0xac297a76
> >> access-list BldA-I line 334 extended permit ip host pc5 10.9.9.0
> >> 255.255.255.0 (hitcnt=0) 0xe378f504
> >> access-list BldA-I line 334 extended permit ip host pc5 72.88.125.0
> >> 255.255.224.0 (hitcnt=0) 0xb25942c5
> >> access-list BldA-I line 335 remark 10/20/2011 permit tcp host user10
> >> host pc5
> >> access-list BldA-I line 336 extended permit tcp host user10 host pc5
> >> (hitcnt=0) 0xc97b61c2
> >> access-list BldA-I line 337 remark 10/20/2011 permit tcp host user11
> >> host pc5
> >> access-list BldA-I line 338 extended permit tcp host user11 host pc5
> >> (hitcnt=1) 0xb8565ac7
>
> >> On Oct 24, 2:09 pm, Sam Mathai Chacko <samde...@gmail.com> wrote:
> >> > So does that mean that all your lines will always have the eq 80 in it?
> >> If
> >> > not, can you attach a sample workbook with a few more examples that
> >> clearly
> >> > depicts the possible list in your complete set of data.
>
> >> > Regards,
> >> > Sam Mathai Chacko (GL)
>
> >> > On Mon, Oct 24, 2011 at 11:33 PM, Zeunasc <timothy.ry...@gmail.com>
> >> wrote:
> >> > > You guys were such a help on the first part of this project, I thought
> >> > > I would ask another question. The project I am working on is still a
> >> > > review of firewall access-list lines. Quarterly, I have to review
> >> > > their use and purge the ones that haven't been used. That said, the
> >> > > output I get to review has 3 types of lines. One is a remark line,
> >> > > one is the access list line, and the last is the exploded view of the
> >> > > access list line:
>
> >> > > access-list BldA-I line 5 remark 10/19/2011 permit tcp host computer1
> >> > > host computer2 eq 80
> >> > > access-list BldA-I line 5 remark 10/19/2011 permit tcp host computer1
> >> > > host computer2 eq 80 0xa6f30e14
> >> > > access-list BldA-I line 5 remark 10/19/2011 permit tcp host computer1
> >> > > host computer2 eq 80 (hitcnt=39975) 0x11f2294d
>
> >> > > Basically, I will be dumping a couple of thousand lines of this kind
> >> > > of text into column A of an Excel spreadsheet. The lines will always
> >> > > be one of those 3 types, though there is no telling which types of
> >> > > lines will appear where. The very first thing I need done is for the
> >> > > "0xa6f30e14" (the string is unique per line, but is always 10
> >> > > characters long) and the "(hitcnt=39975) 0x11f2294d" (again, hitcnt
> >> > > can be any number from 0 to infinity, so the length changes).
>
> >> > > Any help is appreciated!
> >> > > Z
>
> >> > > --
>
> >> ----------------------------------------------------------------------------------
> >> > > Some important links for excel users:
> >> > > 1. Follow us on TWITTER for tips tricks and links :
> >> > >http://twitter.com/exceldailytip
> >> > > 2. Join our LinkedIN group @
> >>http://www.linkedin.com/groups?gid=1871310
> >> > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> >> > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> >> > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> >> > > To post to this group, send email to excel-macros@googlegroups.com
>
> >> > > <><><><><><><><><><><><><><><><><><><><><><>
> >> > > Like our page on facebook , Just follow below link
> >> > >http://www.facebook.com/discussexcel
>
> >> > --
> >> > Sam Mathai Chacko
>
> >> --
>
> >> ----------------------------------------------------------------------------------
> >> Some important links for excel users:
> >> 1. Follow us on TWITTER for tips tricks and links :
> >>http://twitter.com/exceldailytip
> >> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> >> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> >> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> >> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> >> To post to this group, send email to excel-macros@googlegroups.com
>
> >> <><><><><><><><><><><><><><><><><><><><><><>
> >> Like our page on facebook , Just follow below link
> >>http://www.facebook.com/discussexcel
>
> > --
> > Sam Mathai Chacko
>
> --
> Sam Mathai Chacko
>
> Parse Access List firewall lines.xlsx
> 13KViewDownload
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
