+ Reply to Thread
Results 1 to 9 of 9

IP address conversion formula

  1. #1
    Registered User
    Join Date
    12-21-2010
    Location
    Lahore, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    73

    IP address conversion formula

    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.

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2013:2016
    Posts
    3,695

    Re: IP address conversion formula

    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

    www.NeedForExcel.com

  3. #3
    Registered User
    Join Date
    12-21-2010
    Location
    Lahore, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    73

    Re: IP address conversion formula

    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 ?

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,867

    Re: IP address conversion formula

    Try this, then...

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Glenn



  5. #5
    Registered User
    Join Date
    05-31-2016
    Location
    Austin, Texas, USA
    MS-Off Ver
    2013
    Posts
    1

    Re: IP address conversion formula

    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.

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    3,858

    Re: IP address conversion formula

    Or try this ...

    =SUMPRODUCT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",100)),ROW($1:$4)*100-99,100))*256^(4-ROW($1:$4)))

  7. #7
    Registered User
    Join Date
    06-06-2017
    Location
    Missouri
    MS-Off Ver
    2013
    Posts
    1

    Re: IP address conversion formula

    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.

  8. #8
    Registered User
    Join Date
    08-23-2019
    Location
    Nowhere
    MS-Off Ver
    3
    Posts
    2

    Re: IP address conversion formula

    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!

  9. #9
    Registered User
    Join Date
    08-23-2019
    Location
    Nowhere
    MS-Off Ver
    3
    Posts
    2

    Re: IP address conversion formula

    Quote Originally Posted by Phuocam View Post
    Or try this ...

    =SUMPRODUCT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",100)),ROW($1:$4)*100-99,100))*256^(4-ROW($1:$4)))
    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1