Let us break it in to parts.
=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)))))
Assuming you know how an If function works
*LEN(A1)<>LEN(SUBSTITUTE(UPPER(A1),"REMARK",""))*, returns a TRUE if the
text contains the word remark. I just used UPPER here because the function
substitute is case sensitive.
So if the above is true, it will return A1 (which is explanatory)
on to the second part
LEFT(A1,MIN(IF(ISERROR(SEARCH(" 0x",A1)),255,SEARCH("
0x",A1)),IF(ISERROR(SEARCH(" (h",A1)),255,SEARCH(" (h",A1))))
Assuming you know how a LEFT function works, the MIN used below returns the
minimum of the position of (h or 0x in the entire text. The ISERROR is just
used to handle cases where either of (h or 0x is missing.
*MIN(IF(ISERROR(SEARCH(" 0x",A1)),255,SEARCH(" 0x",A1)),IF(ISERROR(SEARCH("
(h",A1)),255,SEARCH(" (h",A1)))*
So putting the evaluated part of the MIN function in the LEFT function, we
get
LEFT(A1,MIN(75,84)). The 75 and 84 are hypothetical positions of (h and 0x,
which then gives LEFT(A1,75)
Hope you now have deciphered the entire bit.
Regards,
Sam Mathai Chacko (GL)
On Tue, Oct 25, 2011 at 12:47 AM, Zeunasc <timothy.ry...@gmail.com> wrote:
> 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
>
--
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 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
