Spreadsheet: 119515d1314478399-stuck-on-a-large-nested-function-kspan31105_excel_ques082711
Sheet: Code Examples

Need to create a new field by:
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 Lookup Table worksheet)
3. Replace the original color code from step1 with the returned new color code from the VLOOKUP - this will create a new code which is the same as the original with the exception of the color code
4. Remove all dashes from the new code and concatenate a K in the front.
Example: 4615-AF-0 should become K4615AFWHI
CODE
4615-AF-0
4615-AF-33
4615-AF-47
4615-AF-55
4615-AF-58
4615-AF-6
4615-AF-7
4615-AF-95
4615-AF-96
4615-AF-G9
4615-AF-K4
4615-AF-NG
4615-AF-S1
4615-AF-S2
4615-AF-S5
4615-AF-W2
4615-AF-Y2
4615-BN-0
4615-BN-33
4615-BN-47
4615-BN-55
4615-BN-58
4615-BN-6
4615-BN-7
4615-BN-95
4615-BN-96
4615-BN-G9
4615-BN-K4
4615-BN-NG
4615-BN-S1
4615-BN-S2
4615-BN-S5
4615-BN-W2
4615-BN-Y2
4615-BR-0
4615-BR-33
4615-BR-47
4615-BR-55
4615-BR-58
4615-BR-6
4615-BR-7
4615-BR-95
4615-BR-96
4615-BR-G9
4615-BR-K4
4615-BR-NG
4615-BR-S1
4615-BR-S2
4615-BR-S5
4615-BR-W2
4615-BR-Y2
4615-BV-0
4615-BV-33
4615-BV-47
4615-BV-55
4615-BV-58
4615-BV-6
4615-BV-7
4615-BV-95
4615-BV-96
4615-BV-G9
4615-BV-K4
4615-BV-NG
4615-BV-S1
4615-BV-S2
4615-BV-S5
4615-BV-W2
4615-BV-Y2
4615-CP-0
4615-CP-33
4615-CP-47
4615-CP-55
4615-CP-58
4615-CP-6
4615-CP-7
4615-CP-95
4615-CP-96
4615-CP-G9
4615-CP-K4
4615-CP-NG
4615-CP-S1
4615-CP-S2
4615-CP-S5
4615-CP-W2
4615-CP-Y2
4615-G-0
4615-G-33
4615-G-47
4615-G-55
4615-G-58
4615-G-6
4615-G-7
4615-G-95
4615-G-96
4615-G-G9
4615-G-K4
4615-G-NG
4615-G-S1
4615-G-S2
4615-G-S5
4615-G-W2
4615-G-Y2
4615-SN-0
4615-SN-33
4615-SN-47
4615-SN-55
4615-SN-58
4615-SN-6
4615-SN-7
4615-SN-95
4615-SN-96
4615-SN-G9
4615-SN-K4
4615-SN-NG
4615-SN-S1
4615-SN-S2
4615-SN-S5
4615-SN-W2
4615-SN-Y2
4649-0
4649-33
4649-47
4649-55
4649-58
4649-6
4649-7
4649-95
4649-96
4649-G9
4649-K4
4649-Y2
4665-Y2
4701-AF-0
4701-AF-7
4701-AF-96
4701-AF-NG
4701-AF-NY
4701-BN-0
4701-BN-7
4701-BN-96
4701-BN-NG
4701-BN-NY
4701-CP-0
4701-CP-7
4701-CP-96
4701-CP-NG
4701-CP-NY
4701-SN-0
4701-SN-7
4701-SN-96
4701-SN-NG
4701-SN-NY
4722-T-NY
4726-T-NY
4761-BN-DAW
4761-BN-LAW
4761-CP-DAW
4761-CP-LAW
4636-0
4636-33
4636-47
4636-55
4636-58
4636-6
4636-7
4636-95
4636-96
4636-G9
4636-K4
4636-NG
4636-NY
4636-SZ-0
4636-Y2
4639-0
4639-33
4639-47
4639-55
4639-58
4639-6
4639-7
4639-95
4639-96
4639-G9
4639-K4
4639-NG
4639-NY
4639-Y2
4643-BN-0
4643-BN-33
4643-BN-47
4643-BN-55
4643-BN-58
4643-BN-6
4643-BN-7
4643-BN-95
4643-BN-96
4643-BN-G9
4643-BN-K4
4643-BN-NG
4643-BN-Y2
4643-CP-0
4643-CP-33
4643-CP-47
4643-CP-55
4643-CP-58
4643-CP-6
4643-CP-7
4643-CP-95
4643-CP-96
4643-CP-G9
4643-CP-K4
4643-CP-NG
4643-CP-Y2
4643-G-0
4643-G-33
4643-G-47
4643-G-55
4643-G-58
4643-G-6
4643-G-7
4643-G-95
4643-G-96
4643-G-G9
4643-G-K4
4643-G-NG
4643-G-Y2
4643-PB-0
4643-PB-33
4643-PB-47
4643-PB-55
4643-PB-58
4643-PB-6
4643-PB-7
4643-PB-95
4643-PB-96
4643-PB-G9
4643-PB-K4
4643-PB-NG
4643-PB-Y2
4644-0
4644-33
4644-47
4644-55
4644-58
4644-6
4644-7
4644-95
4644-96
4644-G9
4644-K4
4644-NG
4644-NY
4644-Y2
4650-0
4650-33
4650-47
4650-55
4650-58
4650-6
4650-7