Spreadsheet: 119528d1314499398-stuck-on-a-large-nested-function-kspan31105_bvj_082711
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
Old Code | New Code
4615-AF-0 | K4615AFWHI
4615-AF-33 | K4615AFMES
4615-AF-47 | [NAME]
4615-AF-55 | K4615AFINB
4615-AF-58 | [NAME]
4615-AF-6 | K4615AFSKY
4615-AF-7 | K4615AFBLA
4615-AF-95 | K4615AFICG
4615-AF-96 | K4615AFBIS
4615-AF-G9 | K4615AFSAN
4615-AF-K4 | K4615AFCAS
4615-AF-NG | [NAME]
4615-AF-S1 | [NAME]
4615-AF-S2 | [NAME]
4615-AF-S5 | [NAME]
4615-AF-W2 | K4615AFEAW
4615-AF-Y2 | K4615AFSUN
4615-BN-0 | K4615BNWHI
4615-BN-33 | K4615BNMES
4615-BN-47 | K4615BNALM
4615-BN-55 | K4615BNINB
4615-BN-58 | K4615BNTHG
4615-BN-6 | K4615BNSKY
4615-BN-7 | K4615BNBLA
4615-BN-95 | K4615BNICG
4615-BN-96 | K4615BNBIS
4615-BN-G9 | K4615BNSAN
4615-BN-K4 | K4615BNCAS
4615-BN-NG | K4615BNTGR
4615-BN-S1 | K4615BNXXX
4615-BN-S2 | K4615BNXXX
4615-BN-S5 | K4615BNXXX
4615-BN-W2 | [NAME]
4615-BN-Y2 | K4615BNSUN
4615-BR-0 | K4615BRWHI
4615-BR-33 | K4615BRMES
4615-BR-47 | [NAME]
4615-BR-55 | K4615BRINB
4615-BR-58 | [NAME]
4615-BR-6 | K4615BRSKY
4615-BR-7 | K4615BRBLA
4615-BR-95 | K4615BRICG
4615-BR-96 | K4615BRBIS
4615-BR-G9 | K4615BRSAN
4615-BR-K4 | K4615BRCAS
4615-BR-NG | [NAME]
4615-BR-S1 | K4615BRXXX
4615-BR-S2 | K4615BRXXX
4615-BR-S5 | K4615BRXXX
4615-BR-W2 | K4615BREAW
4615-BR-Y2 | K4615BRSUN
4615-BV-0 | K4615BVWHI
4615-BV-33 | K4615BVMES
4615-BV-47 | [NAME]
4615-BV-55 | K4615BVINB
4615-BV-58 | [NAME]
4615-BV-6 | K4615BVSKY
4615-BV-7 | K4615BVBLA
4615-BV-95 | K4615BVICG
4615-BV-96 | K4615BVBIS
4615-BV-G9 | K4615BVSAN
4615-BV-K4 | K4615BVCAS
4615-BV-NG | K4615BVTGR
4615-BV-S1 | K4615BVXXX
4615-BV-S2 | K4615BVXXX
4615-BV-S5 | K4615BVXXX
4615-BV-W2 | K4615BVEAW
4615-BV-Y2 | K4615BVSUN
4615-CP-0 | K4615CPWHI
4615-CP-33 | K4615CPMES
4615-CP-47 | K4615CPALM
4615-CP-55 | K4615CPINB
4615-CP-58 | K4615CPTHG
4615-CP-6 | K4615CPSKY
4615-CP-7 | K4615CPBLA
4615-CP-95 | K4615CPICG
4615-CP-96 | [NAME]
4615-CP-G9 | K4615CPSAN
4615-CP-K4 | K4615CPCAS
4615-CP-NG | K4615CPTGR
4615-CP-S1 | K4615CPXXX
4615-CP-S2 | K4615CPXXX
4615-CP-S5 | K4615CPXXX
4615-CP-W2 | K4615CPEAW
4615-CP-Y2 | K4615CPSUN
4615-G-0 | K4615GWHI
4615-G-33 | K4615GMES
4615-G-47 | K4615GALM
4615-G-55 | [NAME]
4615-G-58 | K4615GTHG
4615-G-6 | K4615GSKY
4615-G-7 | K4615GBLA
4615-G-95 | K4615GICG
4615-G-96 | K4615GBIS
4615-G-G9 | K4615GSAN
4615-G-K4 | K4615GCAS
4615-G-NG | K4615GTGR
4615-G-S1 | K4615GXXX
4615-G-S2 | K4615GXXX
4615-G-S5 | K4615GXXX
4615-G-W2 | K4615GEAW
4615-G-Y2 | K4615GSUN
4615-SN-0 | K4615SNWHI
4615-SN-33 | K4615SNMES
4615-SN-47 | [NAME]
4615-SN-55 | [NAME]
4615-SN-58 | K4615SNTHG
4615-SN-6 | K4615SNSKY
4615-SN-7 | K4615SNBLA
4615-SN-95 | K4615SNICG
4615-SN-96 | K4615SNBIS
4615-SN-G9 | K4615SNSAN
4615-SN-K4 | K4615SNCAS
4615-SN-NG | K4615SNTGR
4615-SN-S1 | [NAME]
4615-SN-S2 | [NAME]
4615-SN-S5 | [NAME]
4615-SN-W2 | K4615SNEAW
4615-SN-Y2 | K4615SNSUN
4649-0 | K4649WHI
4649-33 | K4649MES
4649-47 | K4649ALM
4649-55 | K4649INB
4649-58 | K4649THG
4649-6 | K4649SKY
4649-7 | K4649BLA
4649-95 | K4649ICG
4649-96 | K4649BIS
4649-G9 | K4649SAN
4649-K4 | K4649CAS
4649-Y2 | K4649SUN
4665-Y2 | K4665SUN
4701-AF-0 | K4701AFWHI
4701-AF-7 | K4701AFBLA
4701-AF-96 | K4701AFBIS
4701-AF-NG | K4701AFTGR
4701-AF-NY | K4701AFDUN
4701-BN-0 | K4701BNWHI
4701-BN-7 | K4701BNBLA
4701-BN-96 | K4701BNBIS
4701-BN-NG | K4701BNTGR
4701-BN-NY | K4701BNDUN
4701-CP-0 | K4701CPWHI
4701-CP-7 | K4701CPBLA
4701-CP-96 | K4701CPBIS
4701-CP-NG | K4701CPTGR
4701-CP-NY | K4701CPDUN
4701-SN-0 | K4701SNWHI
4701-SN-7 | K4701SNBLA
4701-SN-96 | K4701SNBIS
4701-SN-NG | K4701SNTGR
4701-SN-NY | K4701SNDUN
4722-T-NY | K4722TDUN
4726-T-NY | K4726TDUN
4761-BN-DAW | K4761BNDAW
4761-BN-LAW | K4761BNLAW
4761-CP-DAW | [NAME]
4761-CP-LAW | K4761CPLAW
4636-0 | K4636WHI
4636-33 | K4636MES
4636-47 | K4636ALM
4636-55 | K4636INB
4636-58 | K4636THG
4636-6 | K4636SKY
4636-7 | K4636BLA
4636-95 | K4636ICG
4636-96 | K4636BIS
4636-G9 | K4636SAN
4636-K4 | K4636CAS
4636-NG | K4636TGR
4636-NY | K4636DUN
4636-SZ-0 | K4636SZWHI
4636-Y2 | K4636SUN
4639-0 | K4639WHI
4639-33 | K4639MES
4639-47 | K4639ALM
4639-55 | K4639INB
4639-58 | K4639THG
4639-6 | K4639SKY
4639-7 | K4639BLA
4639-95 | K4639ICG
4639-96 | K4639BIS
4639-G9 | K4639SAN
4639-K4 | K4639CAS
4639-NG | K4639TGR
4639-NY | K4639DUN
4639-Y2 | K4639SUN
4643-BN-0 | K4643BNWHI
4643-BN-33 | K4643BNMES
4643-BN-47 | [NAME]
4643-BN-55 | K4643BNINB
4643-BN-58 | [NAME]
4643-BN-6 | K4643BNSKY
4643-BN-7 | K4643BNBLA
4643-BN-95 | K4643BNICG
4643-BN-96 | [NAME]
4643-BN-G9 | K4643BNSAN
4643-BN-K4 | K4643BNCAS
4643-BN-NG | K4643BNTGR
4643-BN-Y2 | K4643BNSUN
4643-CP-0 | K4643CPWHI
4643-CP-33 | K4643CPMES
4643-CP-47 | K4643CPALM
4643-CP-55 | K4643CPINB
4643-CP-58 | [NAME]
4643-CP-6 | K4643CPSKY
4643-CP-7 | K4643CPBLA
4643-CP-95 | K4643CPICG
4643-CP-96 | K4643CPBIS
4643-CP-G9 | [NAME]
4643-CP-K4 | K4643CPCAS
4643-CP-NG | K4643CPTGR
4643-CP-Y2 | K4643CPSUN
4643-G-0 | K4643GWHI
4643-G-33 | K4643GMES
4643-G-47 | K4643GALM
4643-G-55 | K4643GINB
4643-G-58 | [NAME]
4643-G-6 | K4643GSKY
4643-G-7 | K4643GBLA
4643-G-95 | K4643GICG
4643-G-96 | K4643GBIS
4643-G-G9 | K4643GSAN
4643-G-K4 | K4643GCAS
4643-G-NG | K4643GTGR
4643-G-Y2 | K4643GSUN
4643-PB-0 | K4643PBWHI
4643-PB-33 | K4643PBMES
4643-PB-47 | K4643PBALM
4643-PB-55 | K4643PBINB
4643-PB-58 | [NAME]
4643-PB-6 | K4643PBSKY
4643-PB-7 | K4643PBBLA
4643-PB-95 | K4643PBICG
4643-PB-96 | K4643PBBIS
4643-PB-G9 | K4643PBSAN
4643-PB-K4 | K4643PBCAS
4643-PB-NG | K4643PBTGR
4643-PB-Y2 | K4643PBSUN
4644-0 | K4644WHI
4644-33 | K4644MES
4644-47 | K4644ALM
4644-55 | K4644INB
4644-58 | K4644THG
4644-6 | K4644SKY
4644-7 | K4644BLA
4644-95 | K4644ICG
4644-96 | K4644BIS
4644-G9 | K4644SAN
4644-K4 | K4644CAS
4644-NG | K4644TGR
4644-NY | K4644DUN
4644-Y2 | K4644SUN
4650-0 | K4650WHI
4650-33 | K4650MES
4650-47 | K4650ALM
4650-55 | K4650INB
4650-58 | [NAME]
4650-6 | K4650SKY
4650-7 | K4650BLA