+ Reply to Thread
Results 1 to 12 of 12

Sperate an IP from a String of cell

  1. 02-04-2014, 09:59 AM #1
    BraveR
    BraveR is offline
    Registered User
    Join Date
    02-04-2014
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    6

    Post Sperate an IP from a String of cell

    Hi All,

    I have a string that has an IP that doesn't follow a fixed length.

    Eg:
    10.248.242.65Microsoft SQL Server Compact 3.5 Service
    10.248.242.5EOL/Obsolete Software: Oracle Java SE/JRE/JDK 6/1.6
    10.248.251.144Microsoft Sync Framework Service Pack

    Please help me seperate that IP and the String.
    I tried the below formula
    =REPLACE(A2,MIN((FIND({"M","E","S"},A2))),0,"?")

    It works fine for the above examples, but i want a formula that can seperate the string after the IP ranging from A-Z or a-z

    Please help me getting any formula so that i can determine atleast the position of the First Letter(A-Z,a-z) from left.
    Reply With QuoteRegister To Reply
  2. 02-04-2014, 10:11 AM #2
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sperate an IP from a String of cell

    Hi,

    Perhaps:

    =LEFT(A1,MATCH(FALSE,INDEX(ABS(51-CODE(MID(A1,ROW($1:$1000),1)))<7,,),0)-1)

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  3. 02-04-2014, 10:23 AM #3
    BraveR
    BraveR is offline
    Registered User
    Join Date
    02-04-2014
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Sperate an IP from a String of cell

    Thanks XOR LX.....


    Its working, thanks a lot
    Reply With QuoteRegister To Reply
  4. 02-04-2014, 10:28 AM #4
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sperate an IP from a String of cell

    You're welcome!
    Reply With QuoteRegister To Reply
  5. 02-28-2014, 02:49 AM #5
    BraveR
    BraveR is offline
    Registered User
    Join Date
    02-04-2014
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Sperate an IP from a String of cell

    Hi again kinda same request. Now I have to seperate the other part from the same data i.e the string part.

    10.248.242.65Microsoft SQL Server Compact 3.5 Service (i want to seperate Microsoft SQL Server Compact 3.5 Service )
    If you could explain the formula and how it works that will help me a lot to modify by my own.
    Reply With QuoteRegister To Reply
  6. 02-28-2014, 03:23 AM #6
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sperate an IP from a String of cell

    Hi,

    If you've used the first formula to extract just the IP Address in B1, then in C1, simply:

    =SUBSTITUTE(A1,B1,"")

    Regards
    Reply With QuoteRegister To Reply
  7. 02-28-2014, 03:25 AM #7
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,380

    Re: Sperate an IP from a String of cell

    If the original is in A1, and the IP Address is in B1, C1:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  8. 02-28-2014, 03:26 AM #8
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sperate an IP from a String of cell

    @TMShucks

    Reply With QuoteRegister To Reply
  9. 02-28-2014, 03:56 AM #9
    BraveR
    BraveR is offline
    Registered User
    Join Date
    02-04-2014
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Sperate an IP from a String of cell

    Hey thanks XOR LX and TMS

    XOR can u please explain the formula u used for IP seperation.
    Reply With QuoteRegister To Reply
  10. 02-28-2014, 06:24 AM #10
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sperate an IP from a String of cell

    Sure.

    The basic gist is to find the occurrence of the first character in the string which is neither a numeric from 0 to 9, nor a full-stop. In this case, I used knowledge of the ANSI codes of these characters combined with a MATCH(FALSE... approach to determine this value.

    Regards
    Reply With QuoteRegister To Reply
  11. 02-28-2014, 07:18 AM #11
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,380

    Re: Sperate an IP from a String of cell

    @XOR LX: just too quick ... but I was trying to help
    Reply With QuoteRegister To Reply
  12. 02-28-2014, 07:51 AM #12
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sperate an IP from a String of cell

    Of course! Two answers is always better than one - even if they're the same!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Compare two cells - determine if string in cell 2 is contained within string in cell 1
    By anakaine in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-22-2013, 02:09 AM
  2. [SOLVED] how to produce a list on a sperate worksheet froma list using criteria
    By Tradesman in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-19-2013, 11:25 AM
  3. VBA Code to insert dynamic data from sperate workbook to chart
    By Policeme21 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-02-2012, 04:33 PM
  4. sperate from the space, in text, in the same cell
    By Fam via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-31-2006, 12:25 PM
  5. Using a sperate sheet to save daily revenue totals
    By justchris in forum Excel General
    Replies: 1
    Last Post: 03-09-2005, 01:49 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1