+ Reply to Thread
Results 1 to 9 of 9

Strategy for Find & Replace

  1. #1
    Registered User
    Join Date
    06-28-2016
    Location
    Malaysia
    MS-Off Ver
    Excel for Mac 2011
    Posts
    24

    Strategy for Find & Replace

    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

  2. #2
    Registered User
    Join Date
    06-15-2016
    Location
    here, there
    MS-Off Ver
    2010
    Posts
    59

    Re: Strategy for Find & Replace

    If yout reeference table starts in the K columne then put in B2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I have also added a file showing this.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-28-2016
    Location
    Malaysia
    MS-Off Ver
    Excel for Mac 2011
    Posts
    24

    Re: Strategy for Find & Replace

    Thank you. I'll check it out!

    Cheers

  4. #4
    Registered User
    Join Date
    06-15-2016
    Location
    here, there
    MS-Off Ver
    2010
    Posts
    59

    Re: Strategy for Find & Replace

    Mistype when hitting enter.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Strategy for Find & Replace

    Try

    =IFERROR(VLOOKUP(LEFT(A2,3),$I$2:$J$16,2,0),IFERROR(VLOOKUP(LEFT(A2,4),$I$2:$J$16,2,0),""))
    Chassis number in A

    your reference table in I2:J16 in my example

  6. #6
    Registered User
    Join Date
    06-28-2016
    Location
    Malaysia
    MS-Off Ver
    Excel for Mac 2011
    Posts
    24

    Re: Strategy for Find & Replace

    Hey crushdrinker06, that is a fantastic method. Brilliant!

    Thank you very much for that amazing formula. It makes the whole thing so easy. Wow.

  7. #7
    Registered User
    Join Date
    06-28-2016
    Location
    Malaysia
    MS-Off Ver
    Excel for Mac 2011
    Posts
    24

    Re: Strategy for Find & Replace

    Thanks John. That also works brilliantly. Excellent.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Strategy for Find & Replace

    Thank you for the feedback and rep.

    Could you please mark thread as solved ("Thread Tools" at top of first post).

  9. #9
    Registered User
    Join Date
    06-28-2016
    Location
    Malaysia
    MS-Off Ver
    Excel for Mac 2011
    Posts
    24

    Re: Strategy for Find & Replace

    Done. Solved.

    Thanks to all those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using find replace to first find two double quotes and replace with a single double quote
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-17-2015, 10:13 AM
  2. [SOLVED] Find and replace where contents a formula so can't be seen by Find and replace.
    By markDuffy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-26-2015, 02:24 PM
  3. Replies: 16
    Last Post: 12-02-2014, 03:38 PM
  4. Replies: 3
    Last Post: 11-25-2014, 06:08 AM
  5. find in excel replace in word: find/replace text in text boxes and headers
    By dean.rogers in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2012, 12:40 PM
  6. Multiple Find and Replace to replace a list of strings
    By WalterP34 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-11-2011, 07:41 PM
  7. [SOLVED] find and replace - replace data in rows to separated by commas
    By msdker in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-14-2006, 08:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1