Hi,
I have a text in a cell which is a series of company codes. Like this: "C1 C2 C3 C4". Each of these codes correspond to a real company name in a table. I'd like to extract the individual codes from the string, use them to look up company names in the table, and concatenate the results into a string and storing that in another cell.
I plan to do this in 3 UDFs:
1) Extract substrings from original text.
I make use of the Split() function in VBA.
2) Vlookup the substrings then store the results in an array
Not sure how to do this yet. Any ideas?
3) Concatenate the array members back into one string
After that, I'll just nest the 3 UDFs together, something like this:
=MyConc(myVLookup(mySplit(C2, "")), " ")
Is there anything wrong in what I'm doing?
And can you fill in the gap for step 2?
Thank you for your help
I have a text in a cell which is a series of company codes. Like this: "C1 C2 C3 C4". Each of these codes correspond to a real company name in a table. I'd like to extract the individual codes from the string, use them to look up company names in the table, and concatenate the results into a string and storing that in another cell.
I plan to do this in 3 UDFs:
1) Extract substrings from original text.
I make use of the Split() function in VBA.
Code:
Public Function mySplit(inSplit As Range, Sep As String) As Variant
mySplit = Split(inSplit.Value, Sep)
End Function
Not sure how to do this yet. Any ideas?
Code:
Public Function myVLookup(inV() As Variant) As Variant
...
End Function
Code:
Public Function MyConc(InConc() As Variant, Optional Sep As String) As String
On Error Resume Next
Dim OutStr As String
Dim i As Integer
For i = LBound(InConc, 1) To UBound(InConc, 1)
If InConc(i) <> "" Then OutStr = OutStr & InConc(i) & Sep
Next i
MyConc = Left(OutStr, Len(OutStr) - Len(Sep))
End Function
=MyConc(myVLookup(mySplit(C2, "")), " ")
Is there anything wrong in what I'm doing?
And can you fill in the gap for step 2?
Thank you for your help