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
<riffmastr@discussions.microsoft.com> 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" <riffmastr@discussions.microsoft.com> wrote in message
news:B4E3AB72-C0D0-4B0B-A582-9D387C8EA637@microsoft.com...
> 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
<riffmastr@discussions.microsoft.com> 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