How do I remove decimals of IP address in excel?
 

I'm trying to remove decimal places from IP addresses (example 24.12.118.76)
in excel so I can treat them as regular numbers. I really just want to use
the beginning part (24). Does anybody know how to convert this to a regular
number?

As long as you only want the first section

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

On Mon, 31 Jan 2005 22:37:03 -0800, riffmastr
wrote:

I'm trying to remove decimal places from IP addresses (example 24.12.118.76)
in excel so I can treat them as regular numbers. I really just want to use
the beginning part (24). Does anybody know how to convert this to a regular
number?

You could remove decimal places like:

=SUBSTITUTE(A1, ".", "")

However, there is a reason the dotted notation exists. Each number
represents an 8 bit number (0-255)
So, for example, the IP address 121.12.123.123 without dots would be the
same as 12.112.123.123


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"riffmastr" wrote in message
...
I'm trying to remove decimal places from IP addresses (example
24.12.118.76)
in excel so I can treat them as regular numbers. I really just want to use
the beginning part (24). Does anybody know how to convert this to a
regular
number?

On Mon, 31 Jan 2005 22:37:03 -0800, riffmastr
wrote:

I'm trying to remove decimal places from IP addresses (example 24.12.118.76)
in excel so I can treat them as regular numbers. I really just want to use
the beginning part (24). Does anybody know how to convert this to a regular
number?

I am doing this now to sort the output of a firewall log. This has the source
IP address in the form Source:24.12.118.76, 2055, WAN - where 2055 is the port
number.

Here is what I am doing:-

J1= IF($A1="","",FIND(":",$C1))
K1= IF($A1="","",FIND(".",$C1))
L1= IF($A1="","",FIND(".",$C1,$K1+1))
M1= IF($A1="","",FIND(".",$C1,$L1+1))
N1= IF($A1="","",FIND(",",$C1))

These are used to determine two sort keys from the a, b, c and d values of the
IP address since Excel can't handle a 4 layer sort. The sort keys are
H1=1000*a+b and I1=1000*c+d as below:-

H1=IF($A1="","",1000*MID($C1,$J1+1, $K1-$J1-1) +MID($C1,$K1+1, $L1-$K1-1))
I1=IF($A1="","",1000*MID($C1,$L1+1, $M1-$L1-1) +MID($C1,$M1+1, $N1-$M1-1))

Read more in the articles under the heading Leading Zeros for more details.

--

Cheers . . . JC