IP addresses manipulation in Excel 1

[DannyTEM]

Been trying to find a solution to this one all morning. Have a column of IP's in Excel (for Mac if it makes any difference) and I wanted to shorten them all down to C class level.

I.e

255.255.255.255

becomes

255.255.255

I have played with lots of built in functions but because the last octet can be 1, 2 or 3 numbers long I have not had successful results.

Can anyone suggest a solution? I am thinking some form of regex but I only have very rusty PHP...

Thanks!

DT

Dan Morgan -

http://www.ski-review.com

 

[TomCologne]

If only the last three digits can vary, how about:

LEFT(Cell,11)

TomCologne

 

[DannyTEM]

Hi Tom,

Thanks for your reply. Only problem is that the last octet can be 1,2 or 3 numbers long so that solution would only work for some IPs, not all.

Cheers anyway,

DT

Dan Morgan -

http://www.ski-review.com

 

[TomCologne]

Hi Dan,

A cold is obviously not only congesting my sinuses...

How about:

=LEFT(A1,FIND(".",A1,9)-1)


TomCologne

 

[DannyTEM]

I am not worthy, that works perfectly.

Thanks ever so much!

DT

Dan Morgan -

http://www.ski-review.com

 

[yogia]

Hi Dan:

In addition to the fine solution by Tom, here is another...

=LEFT(A1,LEN(A1)-4)

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com