Closed Thread
Results 1 to 6 of 6

Extracting the class C address from an IP address via a formula in Excel

  1. #1
    Registered User
    Join Date
    07-27-2006
    Posts
    23

    Extracting the class C address from an IP address via a formula in Excel

    Hi Guys,

    I want to extract the class C address from an IP address via a formula in Excel. For example the IP address 10.10.10.10 should return a class C address 10.10.10.0 and the IP address 10.171.55.253 should return a class C address 10.171.55.0. Is this possible ?

    Floep

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097

    Thumbs up

    Quote Originally Posted by floep
    Hi Guys,

    I want to extract the class C address from an IP address via a formula in Excel. For example the IP address 10.10.10.10 should return a class C address 10.10.10.0 and the IP address 10.171.55.253 should return a class C address 10.171.55.0. Is this possible ?

    Floep
    Hi Floep,

    Try this one, assumming data in column A

    =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))&"0"

    oldchippy

  3. #3
    Registered User
    Join Date
    07-27-2006
    Posts
    23
    This works like a charm ... thx a million !

    I had to correct one small detail : LE N should be LEN

  4. #4
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Glad to be of help, with regard to LE N, I've come across this before, sometimes when you paste a formula on a forum for some reason extra spaces sometimes creep in, good job you spotted it.

  5. #5
    Registered User
    Join Date
    03-04-2023
    Location
    Vancouver, Canada
    MS-Off Ver
    Office 365 for Mac
    Posts
    3

    Re: Extracting the class C address from an IP address via a formula in Excel

    This is exactly what I'm looking for, except I want the C class output to not have the .0 ending and only consist of two decimals so that there are just three series of numbers like this: 10.171.55

    I understand what I need to delete from the formula to remove the 0 at the end, but how do I remove the third decimal as well?

    P.S. Can't believe this thread is almost 20 years old!
    Last edited by biztyke; 03-04-2023 at 05:40 PM.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Extracting the class C address from an IP address via a formula in Excel

    @biztyke
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

Closed 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