dotted decimal ip addresses in excel

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

I have a cell that contains a typical IP address in the
form 123.456.789.012 (dotted decimal).

I want to break each octect into a seperate cell, so that
if a1=123.456.789.123, then a2=123, a3=456, a4=789, and
a5=123. note that the four octets may not always contain 3
digits each (i.e. 1.2.3.4 is possible).

I can get the first two octects out, but my formula falls
down for the third and fourth octets, I think it is
exceeding the 'nested 7 times' rule.

example:
H17= 192.168.1.120
H18= =LEFT(H17,(FIND(".",H17)-1)) = 192
H19= =MID(H17,((FIND(".",H17))+1),(FIND(".",H17,(FIND
(".",H17)+1))-(FIND(".",H17)))) = 168
but everytime i try to expand this approach, to get the
third octect, i get errors.

I can retrieve the _positions_ of the dots, using the
following:

first occurance: =FIND(".",H17)
second occurance: =FIND(".",H17,(FIND(".",H17)+1))
third occuarance: =FIND(".",H17,(FIND(".",H17,(FIND
(".",H17)+1))+1))
note that the +1 is to give the position of the dot, if
you start at 1

help appreciated,

Thanks,

Jason
 
Hi Jason
if you're happy with a split in columns try
- goto 'Data - Text to Columns'
- choose the dot as delimiter
 
Jason said:
I have a cell that contains a typical IP address in the
form 123.456.789.012 (dotted decimal).

I want to break each octect into a seperate cell, so that
if a1=123.456.789.123, then a2=123, a3=456, a4=789, and
a5=123. ...
Click to expand...

Do you have a column of these, perhaps?

One way to split the addresses into different cells is select the column
and use
Data >> Text to columns >> Delimited
And choose the "dot" character as the delimiter.

This is different from the approach you were trying, but maybe it'll suit
your needs.
 
Back
Top