How can i split a string

[zabiullakhan]

How can i split a string and vlookup each element at a time and output the results for the data below.

My Lookup data

<TABLE style="WIDTH: 140pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=186><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 92pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20 width=122><TABLE style="WIDTH: 140pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=186><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 92pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=122>vlookup_email ids</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>val</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>pqr.efg</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>sam</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>csk.ijk</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>dan</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>xyz.abc</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>can</TD></TR></TBODY></TABLE>
my output data

<TABLE style="WIDTH: 172pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=229><COLGROUP><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" width=145><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 109pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=145>email id</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=84>output val</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20>xyz.abc;pqr.efg</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>can;sam</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>pqr.efg</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>sam</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>pqr.efg;csk.ijk;xyz.abc</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>sam;dan;can</TD></TR></TBODY></TABLE>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20>Please help me with a macro</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD></TR></TBODY></TABLE>

 

[Krishnakumar]

Hi

Try this UDF.

Function LOOKUPM(ByRef LukUps As Range, ByRef LukUpRng As Range, ByVal LukUpCol As Long, _
                ByVal RsltCol As Long, Optional ByVal Delim As String = ";") As String
    
    Dim k, q, i As Long, j As Long
    
    k = LukUpRng
    q = Split(LukUps, Delim)
    
    If IsArray(k) Then
        For i = 1 To UBound(k, 1)
            For j = 0 To UBound(q)
                If LCase(k(i, LukUpCol)) = LCase$(q(j)) Then
                    LOOKUPM = LOOKUPM & Delim & k(i, RsltCol)
                End If
            Next
        Next
    End If
    If Len(LOOKUPM) > 1 Then LOOKUPM = Mid$(LOOKUPM, 2)
    
End Function

ad use like

=LOOKUPM(A9,$A$2:$B$4,1,2)

HTH

 

[zabiullakhan]

it is not working.. please help.. i have added a module and pasted the code u gave and the formal in the cell i want the result as below

=LOOKUPM(A2,$G$2:$H$4,1,2)

 

[Krishnakumar]

Hi,

it is not working.. please help..

What's not working ? Did you get any error ? Or incorrect results ?

 

[zabiullakhan]

Hey sorry.. My Excel calculation options were set to manual so i was geting #NA.

Thanks a lot.

 

[Rick Rothstein]

Here is another UDF you can consider...

Function ReplaceIDs(LookUpIDs As String, EmailIDsTable As Range) As String
  Dim X As Long, CellContent As String
  ReplaceIDs = LookUpIDs
  For X = 0 To EmailIDsTable.Rows.Count - 1
    CellContent = EmailIDsTable(1).Offset(X).Value
    If InStr(1, LookUpIDs, CellContent, vbTextCompare) Then
      ReplaceIDs = Replace(ReplaceIDs, EmailIDsTable(1).Offset(X).Value, EmailIDsTable(1).Offset(X, 1).Value)
    End If
  Next
End Function

This UDF takes two arguments... the first is the ID string you want to do the replacements in and the second is the address for the table containing the substitution list. So, assuming your output email_id list started in A9 and the substitution table was located at A2:B4, you would put this formula in B9 and copy it down as needed...

=ReplaceIDs(A9,A$2:B$4)

 

[Krishnakumar]

Hi Rick,

I think the Instr will return incorrect results since it acts as a wildcard.

for e.g. LookupIds = 'pqr.efg123', UDF returns 'sam123'

 

[Rick Rothstein]

Hi Rick,

I think the Instr will return incorrect results since it acts as a wildcard.

for e.g. LookupIds = 'pqr.efg123', UDF returns 'sam123'

Thanks for pointing that out. Yes, if the "email id" list contains "non-email ids" (that is, IDs not in the substitution table), then that would be true... my code assumes this would not be the case. However, your message did make me realize that I had forgotten about shorter IDs possibly being a substring of longer IDs and possibly causing incorrect substitutions (although I guess this could be handled by making sure the table was sorted from longer IDs on top to shorter ones on the bottom). I don't know if that is a possibility for the OP's data structure or not. In any case, the following modification to my UDF will handle this possibility as well as the one you raised...

Function ReplaceIDs(LookUpIDs As String, EmailIDsTable As Range, Optional Delimiter As String = ";") As String
  Dim X As Long
  ReplaceIDs = LookUpIDs
  For X = 0 To EmailIDsTable.Rows.Count - 1
    If InStr(1, Delimiter & LookUpIDs & Delimiter, Delimiter & EmailIDsTable(1).Offset(X).Value & Delimiter, vbTextCompare) Then
      ReplaceIDs = Replace(ReplaceIDs, EmailIDsTable(1).Offset(X).Value, EmailIDsTable(1).Offset(X, 1).Value)
    End If
  Next
End Function