Announcement

Collapse
No announcement yet.

Filter Specific Parts of Excel Cells

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Filter Specific Parts of Excel Cells

    Does anyone know how to filter excel from a specific part of the cells? For example:

    In colum "D" I have IP's that are listed but they are displayed like this when queried:

    IP_10.1.2.191

    How would I fix all the IP's so they are just displayed as:

    10.1.2.191

  • #2
    Re: Filter Specific Parts of Excel Cells

    Hiya!
    A question I can answer!

    Highlight the column in Excel, go to Edit->Replace
    Then just input IP_ as the text to Find: and leave the Replace with: field blank.

    Have fun!
    I don't know anything about (you or your) computers.
    Research/test for yourself when listening to free advice.

    Comment


    • #3
      Re: Filter Specific Parts of Excel Cells

      That works! But do you know how to do it with vbscript?

      Comment


      • #4
        Re: Filter Specific Parts of Excel Cells

        I thought this would work...but it's giving me an "Expected Statement Error"

        Code:
        objExcel.Columns("D:D").Select
        objExcel.Selection.Replace What:="IP_", Replacement:=""

        Comment


        • #5
          Re: Filter Specific Parts of Excel Cells

          Ok so I've moved on and tried this:

          Code:
          objExcel.Range("D:D").Replace What="_IP", Replacement="", LookAt=xlPart, SearchOrder=xlByRows, MatchCase=False, SearchFormat=False, ReplaceFormat=False
          I'm getting a "Subscript out of range" error. I feel like I'm really close with this...can anyone help?

          Comment


          • #6
            Re: Filter Specific Parts of Excel Cells

            I dont know anything about scripting but use =MID(A1,3,15)
            "...if I turn out to be particularly clear, you've probably misunderstood what I've said” - Alan Greenspan

            Comment


            • #7
              Re: Filter Specific Parts of Excel Cells

              I got it...it's this:

              Code:
              objExcel.Range("D:D").Replace "IP_", ""

              Comment


              • #8
                Re: Filter Specific Parts of Excel Cells

                Wtg!
                I don't know anything about (you or your) computers.
                Research/test for yourself when listening to free advice.

                Comment

                Working...
                X