Stuck on a large nested function...

**kspan31105** · 08-27-2011, 04:55 PM

Hello - I am stuck on creating a function that I think I am pretty close with but I can't quite get the logic right. I have a large dataset (sample attached) with varying formats of a code field. What the codes do have in common is that there is a color code after the last dash. But sometimes the code has 1 or 2 dashes. Overall the goal is:
1. Isolate the characters after the last dash in the code field - this is the color code
2. Using VLOOKUP assign a new color code from the Lookup Table.
3. Replace the original color code from step1 with the returned new color code from the VLOOKUP in step2 - this will create a new code which is the same as the original with the exception of the color code (see attachment for example)
4. Remove all dashes from the new code and concatenate a K in the front.

I am a SAS programmer by trade and I can solve the problem in SAS via a SCAN function but this logic can't use SAS and needs to be maintained in Excel. Grrr...very frustrated with this one!

Any help would be much appreciated!

**martindwilson** · 08-27-2011, 06:32 PM

IN B9
="K"&SUBSTITUTE(LEFT(SUBSTITUTE(A9,"-","^",2),FIND("^",SUBSTITUTE(A9,"-","^",2))-1),"-","")&VLOOKUP(TRIM(RIGHT(SUBSTITUTE(A9,"-",REPT(" ",20)),3)),'Lookup Table'!$A$2:$B$98,2,FALSE)

**protonLeah** · 08-27-2011, 10:43 PM

Or maybe a UDF:

Option Explicit

Public Function ExtractCodes(ByVal OldString As String) As String
    Dim temparray   As Variant, _
        NewCode     As String, _
        tempstring  As String, _
        MaxIndex    As Long, _
        Ndx         As Long, _
        COLORTABLE  As Range
        
    Ndx = Sheets("lookup table").Cells(Rows.Count, "A").End(xlUp).Row
    Set COLORTABLE = Sheets("lookup table").Range("A2:B" & Ndx)
        
    Err.Clear
    temparray = Split(OldString, "-")
    MaxIndex = UBound(temparray)
    
    On Error Resume Next
    NewCode = WorksheetFunction.VLookup(temparray(MaxIndex), COLORTABLE, 2, False)
    If Err.Number = 1004 Then   'process zeros and "not found" errors
        If temparray(MaxIndex) = 0 Then
            NewCode = "WHI"
        ElseIf NewCode = "" Then
            NewCode = "XXX"
        End If
    End If
    
    For Ndx = 0 To MaxIndex - 1
        tempstring = tempstring & temparray(Ndx)
    Next Ndx
    tempstring = "K" & tempstring & NewCode
    ExtractCodes = tempstring
End Function

**Marcol** · 08-28-2011, 06:04 AM

Or this in B9, to allow for the cases where there are no middle characters (i.e. -??- )

="K"&SUBSTITUTE(LEFT(A9,LEN(A9)-LEN(TRIM(RIGHT(SUBSTITUTE(A9,"-",REPT(" ",10)),10)))-1),"-","")&INDEX('Lookup Table'!A:B,MATCH(TRIM(RIGHT(SUBSTITUTE(A9,"-",REPT(" ",10)),10)),'Lookup Table'!A:A,0),2)

The new colour codes for S1,S2, & S5 are not in your lookup table and will return #N/A

Hope this helps.