Recherche de code article

%3CLINGO-SUB%20id%3D%22lingo-sub-2017753%22%20slang%3D%22fr-FR%22%3ESearch%20for%20article%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2017753%22%20slang%3D%22fr-FR%22%3E%3CP%3EHello%2C%20I%20have%20a%20database%20that%20takes%20the%20vendors%20item%20codes%20but%20with%20an%20extension%20(which%20may%20vary)%20to%20specify%20the%20option%20of%20the%20article%20code.%20I%20would%20like%20to%20reorganize%20my%20articles%20base%20by%20having%20in%20one%20column%20the%20vendor%20code%20and%20in%20the%20other%20column%20the%20additional%20code%20that%20corresponds%20to%20its%20option.%20Example%3A%20we%20have%20a%20code%20LMFAO32BM19%2C%20the%20vendor%20code%20here%20is%20LMFAO32%20and%20the%20option%20BM19.%20A%20simple%20vertical%20search%20can%20only%20find%20identical%20codes%2C%20not%20codes%20whose%20beginning%20is%20correct%20and%20not%20the%20rest.%20How%20can%20I%20do%20that%3F%20I%20have%20over%205%2C000%20lines%20and%20doing%20them%20manually%20seems%20to%20me%20a%20waste%20of%20time.%20A%20big%20thank%20you%20for%20your%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2017753%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2017984%22%20slang%3D%22en-US%22%3ERe%3A%20Recherche%20de%20code%20article%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2017984%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F912693%22%20target%3D%22_blank%22%3E%40Caroline_Simonis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPart%20of%20answering%20this%20depends%20on%20how%20consistent%20such%20things%20as%20vendor%20and%26nbsp%3B%20item%20codes%20are.%20Are%20they%20all%20the%20same%20length%2C%20for%20example%3F%20Is%20every%20vendor%20code%207%20characters%20long%3F%20Does%20every%20item%20code%20begin%20with%20%22B%22%3F%3C%2FP%3E%3CP%3EExcel%20can%20easily%20separate%20the%20first%20seven%20characters%20of%20LMFAO32BM19%20by%20using%20the%20LEFT%20function.%20For%20example%2C%20assuming%20the%20code%20is%20in%20cell%20A1%2C%20this%20formula%20in%20cell%20B1%20would%20extract%20the%20vendor%20code%3A%3C%2FP%3E%3CP%3E%3DLEFT(A1%2C7)%3C%2FP%3E%3CP%3Eand%20this%2C%20in%20cell%26nbsp%3B%20C1%2C%20would%20extract%20the%20item%20code%3A%3C%2FP%3E%3CP%3E%3DRIGHT(A1%2C4)%3C%2FP%3E%3CP%3EThere%20are%20more%20sophisticated%20text%20functions%20that%20could%20be%20written%2C%20but%20you%20need%20to%20give%20more%20examples%20of%20how%20much%20these%20combined%20codes%20change%2C%20how%20consistent%20or%20inconsistent%20they%20are%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Bonjour, j'ai une base de données qui reprend les codes articles fournisseurs mais avec une extension (qui peut varier) pour préciser l'option du code article. J'aimerais réorganiser ma base articles en ayant dans une colonne le code fournisseur et dans l'autre colonne le complément de code qui correspond à son option. Exemple : nous avons un code LMFAO32BM19, le code fournisseur est ici LMFAO32 et l'option BM19. Une simple recherche verticale ne permet que de trouver des codes identiques, pas des codes dont le début est correct et pas la suite. Comment puis je faire? J'ai plus de 5.000 lignes et les faire manuellement me semble une perte de temps. Un tout grand merci pour votre aide

2 Replies

@Caroline_Simonis 

Part of answering this depends on how consistent such things as vendor and  item codes are. Are they all the same length, for example? Is every vendor code 7 characters long? Does every item code begin with "B"?

Excel can easily separate the first seven characters of LMFAO32BM19 by using the LEFT function. For example, assuming the code is in cell A1, this formula in cell B1 would extract the vendor code:

=LEFT(A1,7)

and this, in cell  C1, would extract the item code:

=RIGHT(A1,4)

There are more sophisticated text functions that could be written, but you need to give more examples of how much these combined codes change, how consistent or inconsistent they are

@Caroline_Simonis and to add to @mathetes comments, I presume you have a list of all possible vendor codes, right?  That list could be used, but it would be much easier to help if you provided the workbook or a sanitized version of it (mask any individual people info like names, addresses, etc... or other confidential info with dummy info).  Please also let us know if you have the FILTER() and LET() functions available in your version of Excel (most all should at this point).  You can test by typing = followed by the function name and see if excel shows that function in the quick fill/help info box.