I need a smart strategy for smart Find & Replace to work with a column with around 40,000 cells. They are car automotive chassis numbers.
I need to work out the car models for each chassis number. The complexity is that some models can be determined by just the first 3 letters, whereas the remained can only be determined from the first 4 letters (see Reference Table* below).
My objective is to use Find & Replace for every chassis no. and get the results displayed in a single column. So my problem is one of strategy and figuring out which steps will get me there?
In the example below, using the LEFT function, I have trimmed each cell in the second column to 4 characters, and trimmed the third column to 3 characters. It seems okay, but this isn’t so clever because I will finish up with 2 columns of car models instead of 1 column. I only need one column. (Perhaps MERGE is an option?)
Chassis No. Trim to 4 Trim to 3
C97SNR6B151329 C97S C97
C21LNR7B186062 C21L C21
C22SNR6B167933 C22S C22
BT3SRR9B061610 BT3S BT3
BT6SNR9B058251 BT6S BT6
BT2LCR7G026834 BT2L BT2
CF1SRR7F281990 CF1S CF1
CM3LRR7G107352 CM3L CM3
CM6LNR7G102543 CM6L CM6
CM6SRR8G116200 CM6S CM6
BT3SRR9B061610 BT3S BT3
BS6MNR8G014764 BS6M BS6
E54ARR7B073999 E54A E54
FZ6YRR9F002256 FZ6Y FZ6
C97PNR7B012734 C97P C97
Reference Table
Model Prefix Model
C97 WIRA
C21 ISWARA 1.3
C22 ISWARA 1.6
BT3 SAGA BLM 1.3
BT6 SAGA BLM 1.6
BT2 SAVVY
CF1 WAJA
CM3L GEN.2 1.3
CM6L GEN.2 1.6
CM6S PERSONA
BS3 SATRIA NEO 1.3
BS6 SATRIA NEO 1.6
E54 PERDANA
FZ6 EXORA
C97P ARENA
I hope that makes sense.
Thanks for any advice given.
Pete
Bookmarks