{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Text2Column","Functional","VBA1","VBA2"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Text2Column","index":0,"n_rows":59,"n_cols":7,"rows_kept":59,"cut":false,"hidden":false,"merged":[]}
["FROM:","Mark Biegert"]
["SUBJECT:","Text-to-Column Approach"]
["DATE:","2014-11-25"]
[]
["Sample IP Address"]
["10.0.0.1"]
["10.255.255.254"]
["172.16.0.1"]
["172.16.255.254"]
["192.168.0.1"]
["192.168.255.254"]
["172.24.4.226"]
["172.24.4.227"]
["172.24.4.228"]
["172.24.4.229"]
["172.24.4.230"]
["172.24.4.231"]
["172.24.4.232"]
["172.24.4.233"]
["172.24.4.234"]
["172.24.4.235"]
["172.24.4.236"]
["172.24.4.237"]
["172.24.4.238"]
["192.168.253.1"]
["192.168.253.2"]
["192.168.253.3"]
["192.168.253.4"]
["192.168.253.5"]
["192.168.253.6"]
[]
["Split the Octets Apart Using Text-to-Columns"]
[]
["First Octet","Second Octet","Third Octet","Fourth Octet","IP Number","Back to Octet","Check"]
[10,0,0,1,{"$f":"SUM(A35:D35*(2^8)^{3,2,1,0})","v":167772161},{"$f":"TEXT(MOD(QUOTIENT(E35,2^24),2^8),\"0\")&\".\"&TEXT(MOD(QUOTIENT(E35,2^16),2^8),\"0\")&\".\"&TEXT(MOD(QUOTIENT(E35,2^8),2^8),\"0\")&\".\" & TEXT(MOD(E35,2^8),\"0\")","v":"10.0.0.1"},{"$f":"F35=A6","v":true}]
[10,255,255,254,{"$f":"SUM(A36:D36*(2^8)^{3,2,1,0})","v":184549374},{"$f":"TEXT(MOD(QUOTIENT(E36,2^24),2^8),\"0\")&\".\"&TEXT(MOD(QUOTIENT(E36,2^16),2^8),\"0\")&\".\"&TEXT(MOD(QUOTIENT(E36,2^8),2^8),\"0\")&\".\" & TEXT(MOD(E36,2^8),\"0\")","v":"10.255.255.254"},true]
[172,16,0,1,{"$f":"SUM(A37:D37*(2^8)^{3,2,1,0})","v":2886729729},"172.16.0.1",true]
[172,16,255,254,{"$f":"SUM(A38:D38*(2^8)^{3,2,1,0})","v":2886795262},"172.16.255.254",true]
[192,168,0,1,{"$f":"SUM(A39:D39*(2^8)^{3,2,1,0})","v":3232235521},"192.168.0.1",true]
[192,168,255,254,{"$f":"SUM(A40:D40*(2^8)^{3,2,1,0})","v":3232301054},"192.168.255.254",true]
[172,24,4,226,{"$f":"SUM(A41:D41*(2^8)^{3,2,1,0})","v":2887255266},"172.24.4.226",true]
[172,24,4,227,{"$f":"SUM(A42:D42*(2^8)^{3,2,1,0})","v":2887255267},"172.24.4.227",true]
[172,24,4,228,{"$f":"SUM(A43:D43*(2^8)^{3,2,1,0})","v":2887255268},"172.24.4.228",true]
[172,24,4,229,{"$f":"SUM(A44:D44*(2^8)^{3,2,1,0})","v":2887255269},"172.24.4.229",true]
[172,24,4,230,{"$f":"SUM(A45:D45*(2^8)^{3,2,1,0})","v":2887255270},"172.24.4.230",true]
[172,24,4,231,{"$f":"SUM(A46:D46*(2^8)^{3,2,1,0})","v":2887255271},"172.24.4.231",true]
[172,24,4,232,{"$f":"SUM(A47:D47*(2^8)^{3,2,1,0})","v":2887255272},"172.24.4.232",true]
[172,24,4,233,{"$f":"SUM(A48:D48*(2^8)^{3,2,1,0})","v":2887255273},"172.24.4.233",true]
[172,24,4,234,{"$f":"SUM(A49:D49*(2^8)^{3,2,1,0})","v":2887255274},"172.24.4.234",true]
[172,24,4,235,{"$f":"SUM(A50:D50*(2^8)^{3,2,1,0})","v":2887255275},"172.24.4.235",true]
[172,24,4,236,{"$f":"SUM(A51:D51*(2^8)^{3,2,1,0})","v":2887255276},"172.24.4.236",true]
[172,24,4,237,{"$f":"SUM(A52:D52*(2^8)^{3,2,1,0})","v":2887255277},"172.24.4.237",true]
[172,24,4,238,{"$f":"SUM(A53:D53*(2^8)^{3,2,1,0})","v":2887255278},"172.24.4.238",true]
[192,168,253,1,{"$f":"SUM(A54:D54*(2^8)^{3,2,1,0})","v":3232300289},"192.168.253.1",true]
[192,168,253,2,{"$f":"SUM(A55:D55*(2^8)^{3,2,1,0})","v":3232300290},"192.168.253.2",true]
[192,168,253,3,{"$f":"SUM(A56:D56*(2^8)^{3,2,1,0})","v":3232300291},"192.168.253.3",true]
[192,168,253,4,{"$f":"SUM(A57:D57*(2^8)^{3,2,1,0})","v":3232300292},"192.168.253.4",true]
[192,168,253,5,{"$f":"SUM(A58:D58*(2^8)^{3,2,1,0})","v":3232300293},"192.168.253.5",true]
[192,168,253,6,{"$f":"SUM(A59:D59*(2^8)^{3,2,1,0})","v":3232300294},"192.168.253.6",true]

{"sheet":"Functional","index":1,"n_rows":31,"n_cols":4,"rows_kept":31,"cut":false,"hidden":false,"merged":[]}
["FROM:","Mark Biegert"]
["SUBJECT:","No VBA Functional Solution"]
["DATE:","2014-11-25"]
[]
["Sample IP Address"]
["Original Octet","Binary Equivalent","Back to Octet","Check"]
["10.0.0.1",{"$f":"     LEFT(         A7,FIND(\".\",A7)-1)*2^24 + N(\"Extract the first octet and shift over 3 bytes\")\n    + MID(         A7, FIND(CHAR(1),SUBSTITUTE(A7,\".\",CHAR(1),1))+1,FIND(CHAR(1),SUBSTITUTE(A7,\".\",CHAR(1),2))-FIND(CHAR(1),SUBSTITUTE(A7,\".\",CHAR(1),1))-1)*2^16 + N(\"Extract the 2nd octet and shift over 2 bytes\")\n    +MID(          A7, FIND(CHAR(1),SUBSTITUTE(A7,\".\",CHAR(1),2))+1,FIND(CHAR(1),SUBSTITUTE(A7,\".\",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE(A7,\".\",CHAR(1),2))-1)*2^8+ N(\"Extract the 3rd octet and shift over 1 bytes\")\n    +RIGHT(      A7,LEN(A7)-FIND(CHAR(1),SUBSTITUTE(A7,\".\",CHAR(1),3)))+N(\"Extract the 4th octet\")","v":167772161},{"$f":"TEXT(MOD(QUOTIENT(B7,2^24),2^8),\"0\")&\".\"&TEXT(MOD(QUOTIENT(B7,2^16),2^8),\"0\")&\".\"&TEXT(MOD(QUOTIENT(B7,2^8),2^8),\"0\")&\".\" & TEXT(MOD(B7,2^8),\"0\")","v":"10.0.0.1"},{"$f":"C7=A7","v":true}]
["10.255.255.254",{"$f":"     LEFT(         A8,FIND(\".\",A8)-1)*2^24 + N(\"Extract the first octet and shift over 3 bytes\")\n    + MID(         A8, FIND(CHAR(1),SUBSTITUTE(A8,\".\",CHAR(1),1))+1,FIND(CHAR(1),SUBSTITUTE(A8,\".\",CHAR(1),2))-FIND(CHAR(1),SUBSTITUTE(A8,\".\",CHAR(1),1))-1)*2^16 + N(\"Extract the 2nd octet and shift over 2 bytes\")\n    +MID(          A8, FIND(CHAR(1),SUBSTITUTE(A8,\".\",CHAR(1),2))+1,FIND(CHAR(1),SUBSTITUTE(A8,\".\",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE(A8,\".\",CHAR(1),2))-1)*2^8+ N(\"Extract the 3rd octet and shift over 1 bytes\")\n    +RIGHT(      A8,LEN(A8)-FIND(CHAR(1),SUBSTITUTE(A8,\".\",CHAR(1),3)))+N(\"Extract the 4th octet\")","v":184549374},{"$f":"TEXT(MOD(QUOTIENT(B8,2^24),2^8),\"0\")&\".\"&TEXT(MOD(QUOTIENT(B8,2^16),2^8),\"0\")&\".\"&TEXT(MOD(QUOTIENT(B8,2^8),2^8),\"0\")&\".\" & TEXT(MOD(B8,2^8),\"0\")","v":"10.255.255.254"},{"$f":"C8=A8","v":true}]
["172.16.0.1",2886729729,"172.16.0.1",true]
["172.16.255.254",2886795262,"172.16.255.254",true]
["192.168.0.1",3232235521,"192.168.0.1",true]
["192.168.255.254",3232301054,"192.168.255.254",true]
["172.24.4.226",2887255266,"172.24.4.226",true]
["172.24.4.227",2887255267,"172.24.4.227",true]
["172.24.4.228",2887255268,"172.24.4.228",true]
["172.24.4.229",2887255269,"172.24.4.229",true]
["172.24.4.230",2887255270,"172.24.4.230",true]
["172.24.4.231",2887255271,"172.24.4.231",true]
["172.24.4.232",2887255272,"172.24.4.232",true]
["172.24.4.233",2887255273,"172.24.4.233",true]
["172.24.4.234",2887255274,"172.24.4.234",true]
["172.24.4.235",2887255275,"172.24.4.235",true]
["172.24.4.236",2887255276,"172.24.4.236",true]
["172.24.4.237",2887255277,"172.24.4.237",true]
["172.24.4.238",2887255278,"172.24.4.238",true]
["192.168.253.1",3232300289,"192.168.253.1",true]
["192.168.253.2",3232300290,"192.168.253.2",true]
["192.168.253.3",3232300291,"192.168.253.3",true]
["192.168.253.4",3232300292,"192.168.253.4",true]
["192.168.253.5",3232300293,"192.168.253.5",true]
["192.168.253.6",3232300294,"192.168.253.6",true]

{"sheet":"VBA1","index":2,"n_rows":31,"n_cols":4,"rows_kept":31,"cut":false,"hidden":false,"merged":[]}
["FROM:","Mark Biegert"]
["SUBJECT:","Solution Using Char-by-Char Parsing"]
["DATE:","2014-11-25"]
[]
["Sample IP Address"]
["Original Octet","Binary Equivalent","Back to Octet","Check"]
["10.0.0.1",{"$f":"[0]!IP2Dbl.IP2Dbl(A7)","v":167772161},{"$f":"Dbl2IP(B7)","v":"10.0.0.1"},{"$f":"C7=A7","v":true}]
["10.255.255.254",{"$f":"[0]!IP2Dbl.IP2Dbl(A8)","v":184549374},{"$f":"Dbl2IP(B8)","v":"10.255.255.254"},{"$f":"C8=A8","v":true}]
["172.16.0.1",{"$f":"[0]!IP2Dbl.IP2Dbl(A9)","v":2886729729},"172.16.0.1",true]
["172.16.255.254",{"$f":"[0]!IP2Dbl.IP2Dbl(A10)","v":2886795262},"172.16.255.254",true]
["192.168.0.1",{"$f":"[0]!IP2Dbl.IP2Dbl(A11)","v":3232235521},"192.168.0.1",true]
["192.168.255.254",{"$f":"[0]!IP2Dbl.IP2Dbl(A12)","v":3232301054},"192.168.255.254",true]
["172.24.4.226",{"$f":"[0]!IP2Dbl.IP2Dbl(A13)","v":2887255266},"172.24.4.226",true]
["172.24.4.227",{"$f":"[0]!IP2Dbl.IP2Dbl(A14)","v":2887255267},"172.24.4.227",true]
["172.24.4.228",{"$f":"[0]!IP2Dbl.IP2Dbl(A15)","v":2887255268},"172.24.4.228",true]
["172.24.4.229",{"$f":"[0]!IP2Dbl.IP2Dbl(A16)","v":2887255269},"172.24.4.229",true]
["172.24.4.230",{"$f":"[0]!IP2Dbl.IP2Dbl(A17)","v":2887255270},"172.24.4.230",true]
["172.24.4.231",{"$f":"[0]!IP2Dbl.IP2Dbl(A18)","v":2887255271},"172.24.4.231",true]
["172.24.4.232",{"$f":"[0]!IP2Dbl.IP2Dbl(A19)","v":2887255272},"172.24.4.232",true]
["172.24.4.233",{"$f":"[0]!IP2Dbl.IP2Dbl(A20)","v":2887255273},"172.24.4.233",true]
["172.24.4.234",{"$f":"[0]!IP2Dbl.IP2Dbl(A21)","v":2887255274},"172.24.4.234",true]
["172.24.4.235",{"$f":"[0]!IP2Dbl.IP2Dbl(A22)","v":2887255275},"172.24.4.235",true]
["172.24.4.236",{"$f":"[0]!IP2Dbl.IP2Dbl(A23)","v":2887255276},"172.24.4.236",true]
["172.24.4.237",{"$f":"[0]!IP2Dbl.IP2Dbl(A24)","v":2887255277},"172.24.4.237",true]
["172.24.4.238",{"$f":"[0]!IP2Dbl.IP2Dbl(A25)","v":2887255278},"172.24.4.238",true]
["192.168.253.1",{"$f":"[0]!IP2Dbl.IP2Dbl(A26)","v":3232300289},"192.168.253.1",true]
["192.168.253.2",{"$f":"[0]!IP2Dbl.IP2Dbl(A27)","v":3232300290},"192.168.253.2",true]
["192.168.253.3",{"$f":"[0]!IP2Dbl.IP2Dbl(A28)","v":3232300291},"192.168.253.3",true]
["192.168.253.4",{"$f":"[0]!IP2Dbl.IP2Dbl(A29)","v":3232300292},"192.168.253.4",true]
["192.168.253.5",{"$f":"[0]!IP2Dbl.IP2Dbl(A30)","v":3232300293},"192.168.253.5",true]
["192.168.253.6",{"$f":"[0]!IP2Dbl.IP2Dbl(A31)","v":3232300294},"192.168.253.6",true]

{"sheet":"VBA2","index":3,"n_rows":31,"n_cols":4,"rows_kept":31,"cut":false,"hidden":false,"merged":[]}
["FROM:","Mark Biegert"]
["SUBJECT:","VBA Method Using String Functions to Parse"]
["DATE:","2014-11-25"]
[]
["Sample IP Address"]
["Original Octet","Binary Equivalent","Back to Octet","Check"]
["10.0.0.1",{"$f":"[0]!IP2Dbl2.IP2Dbl(A7)","v":167772161},{"$f":"Dbl2IP(B7)","v":"10.0.0.1"},{"$f":"A7=C7","v":true}]
["10.255.255.254",{"$f":"[0]!IP2Dbl2.IP2Dbl(A8)","v":184549374},{"$f":"Dbl2IP(B8)","v":"10.255.255.254"},{"$f":"A8=C8","v":true}]
["172.16.0.1",{"$f":"[0]!IP2Dbl2.IP2Dbl(A9)","v":2886729729},"172.16.0.1",true]
["172.16.255.254",{"$f":"[0]!IP2Dbl2.IP2Dbl(A10)","v":2886795262},{"$f":"Dbl2IP(B10)","v":"172.16.255.254"},true]
["192.168.0.1",{"$f":"[0]!IP2Dbl2.IP2Dbl(A11)","v":3232235521},"192.168.0.1",true]
["192.168.255.254",{"$f":"[0]!IP2Dbl2.IP2Dbl(A12)","v":3232301054},"192.168.255.254",true]
["172.24.4.226",{"$f":"[0]!IP2Dbl2.IP2Dbl(A13)","v":2887255266},"172.24.4.226",true]
["172.24.4.227",{"$f":"[0]!IP2Dbl2.IP2Dbl(A14)","v":2887255267},"172.24.4.227",true]
["172.24.4.228",{"$f":"[0]!IP2Dbl2.IP2Dbl(A15)","v":2887255268},"172.24.4.228",true]
["172.24.4.229",{"$f":"[0]!IP2Dbl2.IP2Dbl(A16)","v":2887255269},"172.24.4.229",true]
["172.24.4.230",{"$f":"[0]!IP2Dbl2.IP2Dbl(A17)","v":2887255270},"172.24.4.230",true]
["172.24.4.231",{"$f":"[0]!IP2Dbl2.IP2Dbl(A18)","v":2887255271},"172.24.4.231",true]
["172.24.4.232",{"$f":"[0]!IP2Dbl2.IP2Dbl(A19)","v":2887255272},"172.24.4.232",true]
["172.24.4.233",{"$f":"[0]!IP2Dbl2.IP2Dbl(A20)","v":2887255273},"172.24.4.233",true]
["172.24.4.234",{"$f":"[0]!IP2Dbl2.IP2Dbl(A21)","v":2887255274},"172.24.4.234",true]
["172.24.4.235",{"$f":"[0]!IP2Dbl2.IP2Dbl(A22)","v":2887255275},"172.24.4.235",true]
["172.24.4.236",{"$f":"[0]!IP2Dbl2.IP2Dbl(A23)","v":2887255276},"172.24.4.236",true]
["172.24.4.237",{"$f":"[0]!IP2Dbl2.IP2Dbl(A24)","v":2887255277},"172.24.4.237",true]
["172.24.4.238",{"$f":"[0]!IP2Dbl2.IP2Dbl(A25)","v":2887255278},"172.24.4.238",true]
["192.168.253.1",{"$f":"[0]!IP2Dbl2.IP2Dbl(A26)","v":3232300289},"192.168.253.1",true]
["192.168.253.2",{"$f":"[0]!IP2Dbl2.IP2Dbl(A27)","v":3232300290},"192.168.253.2",true]
["192.168.253.3",{"$f":"[0]!IP2Dbl2.IP2Dbl(A28)","v":3232300291},"192.168.253.3",true]
["192.168.253.4",{"$f":"[0]!IP2Dbl2.IP2Dbl(A29)","v":3232300292},"192.168.253.4",true]
["192.168.253.5",{"$f":"[0]!IP2Dbl2.IP2Dbl(A30)","v":3232300293},"192.168.253.5",true]
["192.168.253.6",{"$f":"[0]!IP2Dbl2.IP2Dbl(A31)","v":3232300294},"192.168.253.6",true]