Excel 2007 : Formula to grab ip address from within text.

[jayboogee]

Hello all, I need a formula that will extract os sort only the first 4 octets of the ip address embedded within text on individual rows. I have included a few lines as an example. The info is for an ip blocklist for my smoothwall. Any assistance provided will be greatly appreciated.

General Electric Company:3.0.0.0-3.255.255.255
s0-0.ciscoseattle.bbnplanet.net:4.0.25.146-4.0.25.148
p1-0.cisco.bbnplanet.net:4.0.26.14-4.0.29.24
Level 3 Communications:4.0.38.0-4.0.38.255
Level 3 Communications:4.0.159.0-4.0.159.255
Level 3 Communications:4.0.181.0-4.0.182.255
Level 3 Communications:4.1.75.0-4.1.75.255
s0.mediasentry.bbnplanet.net:4.1.75.131-4.1.75.156
Level 3 Communications:4.1.129.0-4.1.140.255
Level 3 Communications:4.1.143.0-4.1.144.255
Cisco Systems, Inc:4.2.144.64-4.2.144.95
Drug Enforcement Adm:4.2.144.224-4.2.144.231
US Dept of Treasury - TIGTA:4.2.144.248-4.2.144.255
City of League City:4.2.145.224-4.2.145.239
Verizon/Intel-San Jose:4.2.153.0-4.2.153.7
Cisco Systems, Inc:4.2.153.32-4.2.153.63
Werner Media:4.2.160.64-4.2.160.79
Pike County Auditor:4.2.161.0-4.2.161.7
Delaware MRDD:4.2.161.64-4.2.161.71
Brown County Commissioners/Georgetown:4.2.162.128-4.2.162.135
Portsmouth Municipal Court:4.2.162.144-4.2.162.151
City of Circleville:4.2.162.160-4.2.162.191
Cisco Systems, Inc:4.2.163.96-4.2.163.127
Cisco Systems, Inc:4.2.169.0-4.2.169.31
Hilton Garden Inn:4.2.169.32-4.2.169.63
Cisco Systems, Inc:4.2.169.64-4.2.169.95

[shg]

Always follows the colon and ends with the hyphen?

=MID(A1, FIND(":",A1)+1, FIND("-", A1, FIND(":", A1) ) - FIND(":", A1) - 1)

[martindwilson]

just to take it a bit further
you've got some funny masks there!
but you may find this will extract 98% of them