How do I remove decimals of IP address in excel?

[Guest]

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?

 

[Myrna Larson]

As long as you only want the first section

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

 

[Rob van Gelder]

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

 

[JC]

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.