Pull back a particular sub string from within a text cell 2

[slobad23]

I am using Excel 2007.

I have a list of cells which may contain an IP address or a web address - What I need to do is reference that column and within a new column, pull back only either of (or both of) those values.

For example

These two cells:

A1:
lorem ipsum lorem ipsum 127.0.0.1 lorem ipsum

http://google.com

lorem ipsum
A2:
lorem ipsum lorem ipsum lorem ipsum

http://google.com

lorem ipsum

Would give this as a result:

B1:
IP: 127.0.0.1 WEB:

http://google.com

B2:
IP: NONE WEB:

http://google.com

I was going to pass this to a perl script and learn how to do it through that (as I thought the tutorial information on being able to do something like that would be easier but I do not have access to Perl and the website for it is currently down.

Any help would be GREATLY appreciated. Thanks.

 

[SkipVought]

hi,

Well does it not depend on what's contained in the lorem ipsums in your sheet?

Suppose you explicitly state the logic you want to impose, to parse these strings. Then with that stated logic, we can proceed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Is it possible for Periods to be in other parts of the string, outside the web addresses and IP addresses? if not, then you could use something like this:

I'm going to assume Column 1 or A contains the value to grab the string from, and Row1 contains the column headers:

Sub PullAddress
  Dim wb as Workbook
  Dim ws as Worksheet
  Dim lngRow as Long 'Row Number
  Dim strFullString() As String 'Array variable for Full String in cell
  Dim strNewString As String
  Dim x As Integer 'For looping through Array variable

  Set wb = ActiveWorkbook
  Set ws = wb.ActiveSheet

  For lngRow = 2 To ws.Range("A65000").End(xlUp).Row 
     'Assuming you don't have more than 65k rows of data
    strFullString = Split(ws.Cells(x,1)," ")
    strNewString = vbNullString
    For x = 0 to UBound(strFullString)-1
      If Instr(strFullString(x),".") Then
        If strNewString = vbNullString Then
          strNewString = strFullString(x)
        Else
          strNewString = strNewString & " " & strFullString(x)
        End If
      End IF
      ws.Cells(x,2).Formula = strNewString
    Next x
  Next lngRow

  Erase strFullString
  If ws Is Nothing Then Else Set ws = Nothing
  If wb Is Nothing Then Else Set wb = Nothing
End Sub

This code is fully untested, so post back if you have problems or questions if you try it.

The code also assumes that everything is split with spaces... or that they exist. I'm assuming you don't care about anything outside the addresses.

 

[slobad23]

Thank you very much for the assistance.

The "lorem ipsum" part of the file could be anything. There could be periods contained within it. The addresses (be them http or IP) will be seperated by white space from the rest of the text.

I do not care about anything else in the cell - just either of the addresses. I think that the second response is going to be a good starting point (if not the answer). I will test this. I am very new to this side of excel but a starting point is probably better than the answer - it will give me a chance to learn!

I appreciate the first response - I should have been a little more complete with my question.

Thank you both.

 

[SkipVought]

I'd use a function. argument 1 is the string to parse, argument 2 is TRUE to return the IP address of FALSE to return the URL...

Function ParseStrings(s As String, Optional bIP As Boolean = True)
    Dim a, i As Integer, b, j As Integer, bHasIP As Boolean
    
    a = Split(s, " ")
    
    For i = 0 To UBound(a)
        If bIP Then
            b = Split(a(i), ".")
            bHasIP = True
            For j = 0 To UBound(b)
                If Not IsNumeric(b(j)) Then
                    bHasIP = False
                End If
            Next
            If bHasIP Then
                ParseStrings = a(i)
                Exit Function
            End If
        Else
            If Left(a(i), 7) = "[URL unfurl="true"]http://"[/URL] Then
                ParseStrings = a(i)
                Exit Function
            End If
        End If
    Next
End Function

Paste in a MODULE and use like any other spreadsheet funtion.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bluedragon2]

The trouble with using formulas is that if it is not very standard, it is hard to pick out lumps of text. This can be easily done with perl, but if you have some sort of consistency, this formula will strip out the IP, still working on the address:

="IP: "& IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))>3,IF(AND(CODE(MID(A1,FIND(".",A1)-3,1))<58,CODE(MID(A1,FIND(".",A1)-3,1))>47),MID(A1,FIND(".",A1)-3,FIND("l",A1,FIND(".",A1))-1-(FIND(".",A1)-3)),IF(AND(CODE(MID(A1,FIND(".",A1)-2,1))<58,CODE(MID(A1,FIND(".",A1)-2,1))>47),MID(A1,FIND(".",A1)-2,FIND("l",A1,FIND(".",A1))-1-(FIND(".",A1)-2)),MID(A1,FIND(".",A1)-1,FIND("l",A1,FIND(".",A1))-1-(FIND(".",A1)-2)))),"NONE")

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Try this formula:

="IP: "&IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))>3,IF(AND(CODE(MID(A1,FIND(".",A1)-3,1))<58,CODE(MID(A1,FIND(".",A1)-3,1))>47),MID(A1,FIND(".",A1)-3,FIND(" ",A1,FIND(".",A1))-(FIND(".",A1)-3)),IF(AND(CODE(MID(A1,FIND(".",A1)-2,1))<58,CODE(MID(A1,FIND(".",A1)-2,1))>47),MID(A1,FIND(".",A1)-2,FIND(" ",A1,FIND(".",A1))-(FIND(".",A1)-2)),MID(A1,FIND(".",A1)-1,FIND(" ",A1,FIND(".",A1))-(FIND(".",A1)-2)))),"NONE") & " WEB: " & IF(FIND("http",A1),MID(A1,FIND("http",A1),FIND(" ",A1,FIND("http",A1))-FIND("http",A1)),"")

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Blue, very nice! [purple]--> *[/purple]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[slobad23]

Absolutely brilliant! Thank you Blue!

Thank you everyone for your help! VBA is not really my thing and this formula has worked wonders.

Once again, thank you to each of you who dedicated some time to helping me out.

Slobad

 

[MCuthill]

*picks up jaw*
Wow Blue! Slobad phrased it perfect... absolutely brilliant.

Has a pointy-purple thing *!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[bluedragon2]

Explaining the logic of the formula to a coworker, I thought of a way to shorten the formula and add error checking for no web address:

="IP: "&IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))>3,IF(AND(CODE(MID(A1,FIND(".",A1)-1,1))<58,CODE(MID(A1,FIND(".",A1)-1,1)))>47,MID(A1,FIND(" ",A1,FIND(".",A1)-4)+1,FIND(" ",A1,FIND(".",A1))-FIND(" ",A1,FIND(".",A1)-4)-1)),"NONE")&" WEB: "&IF(ISERROR(FIND("http",A1)),"NONE",MID(A1,FIND("http",A1),FIND(" ",A1,FIND("http",A1))-FIND("http",A1)))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...