Question Conversion de codes-barres en format spécifique à l'aide de la fonction IF, FIND & MID d'Excel


Dans Excel, j'ai un certain nombre de colonnes contenant des caractères de différents types tels que:

WS-S5-S-L1-C31-F-U5-S9-P14 
WS-S5-S-L1-C31-F-U5-S8-P1 
WS-S5-N-L1-C29-V-U16-S6-P6 

Je veux les convertir en 8 caractères en utilisant les règles suivantes:

  • ne garder que les trois derniers segments
  • supprimer le U et ajouter le préfixe 0 le cas échéant
  • supprimer S et ajouter le préfixe 0 le cas échéant
  • supprimer P et ajouter le préfixe 0 le cas échéant

Par exemple:

  • WS-S5-S-L1-C31-F-U5-S9-P14 convertir en 05-09-14
  • WS-S5-S-L1-C31-F-U5-S8-P1 convertir en 05-08-01
  • WS-S5-N-L1-C29-V-U16-S6-P6 convertir en 16-06-06

Je crois qu'il y a un moyen d'utiliser IF, FIND Et MID fonction pour les convertir dans Excel, mais ne sais pas comment démarrer. Toute aide sera très appréciée.

Mettre à jour

Enfin, je voulais convertir cela en 13 caractères si possible, par exemple:

  • WS-S5-S-L1-C31-F-U5-S9-P14 convertir en S1-F-05-09-14
  • WS-S5-N-L2-C31-D-U5-S8-P1 convertir en N2-D-05-08-01
  • WS-S5-N-L1-C29-V-U16-S6-P6 convergent vers N1-V-16-06-06

4
2018-05-23 04:08


origine


Les cordes ont-elles toujours la même longueur? - Kevin Anthony Oppegaard Rose
@Kevin: non, voir "U5" vs "U16" - Máté Juhász
Les chaînes sont de longueur différente et j'ai utilisé la formule suivante qui renvoie "05" de WS-S5-S-L1-C31-F-U5-S9-P14. Mais comment revenir "05-09-14"? = IF (MID (E13, FIND ("- U", E13) +3,1) = "-", "0" & ​​MID (E13, FIND ("- U", E13) +2,1), MID ( E13, FIND ("- U", E13) +2,2)) - Indy


Réponses:


Comme @ygaft l'a fait remarquer, c'est possible, mais les fonctions standard d'Excel vont être longues.

J'utilise gratuitement RegEx Rechercher / Remplacer add-in dans une situation comme celle-là, en utilisant une expression régulière, vous pouvez y arriver plus facilement.

La formule:
=RegExReplace(RegExReplace(A1,".*U([0-9]+)-S([0-9]+)-P([0-9]+)","0$1-0$2-0$3"),"0([0-9]{2})","$1")

Comment ça marche:

  • fonction interne:
    • A1: à partir du contenu de la cellule A1
    • ".*U([0-9]+)-S([0-9]+)-P([0-9]+)" rechercher un motif "... U # -S # -P #" où "#" représente un ou plusieurs nombres et mémorise les nombres (les crochets créent des groupes de référence)
    • "0$1-0$2-0$3" fusionne les nombres trouvés à l'étape précédente, en ajoutant un 0 à tous.
  • fonction externe:
    • RegExReplace(...) - fonctionne avec les résultats de la fonction interne
    • "0([0-9]{2})" - recherche 0 suivi de deux chiffres (= cas où le premier 0 n'est pas nécessaire)
    • "$1" - ne conserve que les deux chiffres, en laissant tomber le premier 0 (uniquement dans les cas qui ont été comparés à l’étape précédente)

enter image description here

Vous pouvez également voir plus d'explications sur les expressions régulières en ligne:

Note: Je ne suis en aucun cas affilié à ce complément, utilisez-le simplement car cela me simplifie la vie.

Mettre à jour

Vous pouvez utiliser cette formule pour votre code de 13 caractères:
=RegExReplace(RegExReplace(A3,".*-([A-Z])-[A-Z]([0-9]).*-([A-Z])-U([0-9]+)-S([0-9]+)-P([0-9]+)","$1$2-$3-0$4-0$5-0$6"),"0([0-9]{2})","$1")


6
2018-05-23 08:21



C'est un excellent complément à utiliser. Travaillé parfaitement Enfin, je voulais convertir cela en 13 caractères si possible, par exemple: * WS-S5-S-L1-C31-F-U5-S9-P14 convertir en S1-F-05-09-14 * WS-S5- N-L2-C31-D-U5-S8-P1 convertir en N2-D-05-08-01 * WS-S5-N-L1-C29-V-U16-S6-P6 convertir en N1-V-16- 06-06 - Indy
comment tu veux dire 13 caractères? Veuillez mettre à jour votre question et mettre à jour ma réponse - Máté Juhász
Conversion de base: • WS-S5-S-L1-C31-F-U5-S9-P14 à S1-F-05-09-14 • WS-S5-N-L2-C31-D-U5-S8-P1 à N2-D-05-08-01 • WS-S5-N-L1-C29-V-U16-S6-P6 à N1-V-16-06-06 En utilisant les caractères 7, 10, 15, 16 et 17 - Indy


Assez laid,
mais vous pouvez y parvenir par la suite, cela suppose que vos données de travail sont dans la colonne A:

=TEXT(LEFT(RIGHT(A1,LEN(A1)-FIND("U",A1,1)),FIND("-",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1)-1),"00")&"-"&TEXT(MID(RIGHT(A1,LEN(A1)-FIND("U",A1,1)),FIND("-",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1)+2,(FIND("P",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1)-2-FIND("S",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1))),"00")&"-"&TEXT(RIGHT(RIGHT(A1,LEN(A1)-FIND("U",A1,1)),LEN(RIGHT(A1,LEN(A1)-FIND("U",A1,1)))-FIND("P",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1)),"00")

5
2018-05-23 07:22



excellente technique utilisée mais le code est un peu long. - Indy
pour le raccourcir, vous pouvez ajouter une colonne auxiliaire avec cette fonction: '= RIGHT (A1, LEN (A1) -FIND ("U", A1,1)', puis faites référence à cette colonne à la place de ce morceau de code - ygaft