Hi,
i want to convert IP address into decimal number so that if i have IP's in Col A then i get decimal numbers in Col B.
The formula is like this:
if IP address is 192.168.1.2
then the formula to get its decimal number is: 192*16777216 + 168*65536 + 1*256 + 2
which comes out to be = 3232235778
So if A1 has 192.168.1.2 then B1 will come out to be 3232235778.
Any idea how to write above formula?
Last edited by naeemdotcom; 08-24-2015 at 08:10 AM.
Here!
=(LEFT(A1,3)*16777216 )+(MID(A1,5,3)*65536 )+(MID(A1,FIND(".",A1,6)+1,FIND(".",A1,FIND(".",A1,6)+1)-FIND(".",A1,6)-1)*256 )+RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,6)+1))
Cheers!
Deep Dave
Yes its working but only when all the numbers in the IP are 3 digits, for example: 123.345.567.890
but what if the numbers are not 3 digits as this: 12.34.4.5 ?
Try this, then...
Formula:
Please Login or Register to view this content.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Let me point out that there is an inherit problem with displaying IP addresses as decimal numbers. If the first octet of the IP address (the number displayed before the first decimal point / dot) is any value less than 100 (i.e. 2.13.99.102) then you would have to format the cell displaying that content to show leading zeroes (12 digits) so it would display as 002013099102. Otherwise it would display as 2013099102, 10 digits rather than 12, which can be misleading. One basic reason for wanting to convert IP address ranges in cells to decimal numbers or strings without dotted decimal notation is in order to properly sort them by hierarchy.
With that in mind let me give you two sets of formulas, one to display the IP address as a decimal value (must be formatted to display 12 digits) and one to display it as a string (text), and then convert those results back to a valid IP address string.
For DECIMAL VALUE RESULTS:
If cell A1 contains the IP address string, let cell B1 contain the following formula to convert it to a decimal value:
=VALUE(CONCATENATE(TEXT(MID(A1,1,FIND(".",A1,1)-1),"000"),TEXT(MID(A1,FIND(".",A1,1)+1,FIND(".",A1,1+FIND(".",A1,1))-FIND(".",A1,1)-1),"000"),TEXT(MID(A1,FIND(".",A1,1+FIND(".",A1,1))+1,FIND(".",A1,1+FIND(".",A1,1+FIND(".",A1,1)))-FIND(".",A1,1+FIND(".",A1,1))-1),"000"),TEXT(MID(A1,FIND(".",A1,1+FIND(".",A1,1+FIND(".",A1,1)))+1,FIND(".",A1,1+FIND(".",A1,1+FIND(".",A1,1)))-FIND(".",A1,1+FIND(".",A1,1))+1),"000")))
To convert the decimal value now in cell B1 back to a properly formatted string for an IP Address, put the following formula in to cell C1:
=CONCATENATE(INT(B1/1000000000),".",INT(B1/1000000)-(INT(B1/1000000000)*1000),".",INT(B1/1000)-(INT(B1/1000000)*1000),".",B1-(INT(H23/1000)*1000))
For STRING CONTENT RESULTS:
To convert cell A1 to a 12 digit string (text), put the following formula into cell B1:
=CONCATENATE(TEXT(MID(A1,1,FIND(".",A1,1)-1),"000"),TEXT(MID(A1,FIND(".",A1,1)+1,FIND(".",A1,1+FIND(".",A1,1))-FIND(".",A1,1)-1),"000"),TEXT(MID(A1,FIND(".",A1,1+FIND(".",A1,1))+1,FIND(".",A1,1+FIND(".",A1,1+FIND(".",A1,1)))-FIND(".",A1,1+FIND(".",A1,1))-1),"000"),TEXT(MID(A1,FIND(".",A1,1+FIND(".",A1,1+FIND(".",A1,1)))+1,FIND(".",A1,1+FIND(".",A1,1+FIND(".",A1,1)))-FIND(".",A1,1+FIND(".",A1,1))+1),"000"))
To convert the cell B1 twelve digit string back to a properly formatted IP address string put the following formula into cell C1:
=CONCATENATE(TEXT(MID(B1,1,3),"##0"),".",TEXT(MID(B1,4,3),"##0"),".",TEXT(MID(B1,7,3),"##0"),".",TEXT(MID(B1,10,3),"##0"))
I hope you find these formulas useful.
Good luck!
Dan Callaghan (Server Engineer / Web Designer)
Last edited by DanCallaghan; 06-01-2016 at 02:00 AM.
Or try this ...
=SUMPRODUCT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",100)),ROW($1:$4)*100-99,100))*256^(4-ROW($1:$4)))
I think there is a piece missing from your formula to convert decimal back to IP string. You reference a cell H23 but don't give us any data for that cell.
I took 10.164.44.252 & converted it to 10164044252 with the first formula. The 2nd made it back into 10.164.44.10164044252
Please let me know what I'm doing wrong.
I know it's bad form resurrecting an old thread, but I just had to go make an account specifically to tell you that this is brilliant!
I know it's bad form resurrecting an old thread, but I just had to go make an account specifically to tell you that this is brilliant!Originally Posted by Phuocam
I know it's poor form to re-open an old discussion but since someone else did I will too.
If anyone came here looking for a formula for use in Google Sheets, there are two additional functions available that do not exist in Excel (TRANSPOSE, and SPLIT) that make this much easier, without needing to do the text padding approach above:
Please Login or Register to view this content.
That ROW($1:$4) trick is pretty clever though, thanks for that!
In B2. This works irrespective of number of digits in PC address.
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Here is complete calculation for each of the 4 qualifiers
Assuming IP value is in d161 and domain qualifiers are in M161 through P161
M161: =INT(D161 / 256^3)
N161: =INT((D161-(M161*256^3)) / 256^2)
O161: =INT(( D161-(M161*256^3)-(N161*256^2))/256)
P161: =INT(( D161-(M161*256^3)-(N161*256^2) -(O161*256) ))
Verified against IP Geo table values.
For IP long 35090431 result is: 2 23 111 255
Hope that helps.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks