Disons que vous avez une feuille de calcul avec beaucoup de codes produit dans la colonne A.

Ces codes sont au format A4, B12, AD4, etc. En raison d’un changement dans le mode de fonctionnement de votre entreprise, vous êtes invité à modifier tous les codes produit afin qu’ils contiennent un tiret entre les lettres et les chiffres.

Vous pouvez effectuer cette tâche de plusieurs manières. Si la structure de vos codes produit est cohérente, l’insertion des tirets est un jeu d’enfant.

Par exemple, s’il y aura toujours une seule lettre suivie de chiffres, vous pouvez utiliser une formule comme celle-ci:

=LEFT(A1,1) & "-" & RIGHT(A1,LEN(A1)-1)

Il y a de fortes chances que vos données ne soient pas structurées, ce qui signifie que vous pourriez avoir une ou deux lettres suivies de trois chiffres maximum. Ainsi, A4 et QD284 seraient tous deux des codes de produit valides. Dans ce cas, une formule de solution demande un peu plus de créativité.

Une façon de gérer cela consiste à utiliser une formule matricielle. Considérons la formule suivante:

=REPLACE(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)),0),0,"-")

Si les valeurs sont comprises entre A1 et A10, vous pouvez placer cette formule dans B1, puis la copier dans la colonne. Puisqu’il s’agit d’une formule matricielle, elle doit être saisie en appuyant sur Ctrl + Maj + Entrée. La formule trouve l’emplacement du premier nombre dans la cellule et insère un tiret devant lui.

Supposons, à titre d’exemple, que la cellule A1 contient BR27. La partie la plus interne de la formule, INDIRECT (« 1: 100 »), convertit le texte 1: 100 en plage. Ceci est utilisé pour que l’insertion ou la suppression de lignes n’affecte pas la formule. La partie suivante de la formule, ROW (INDIRECT (« 1: 100 »)), crée essentiellement un tableau des valeurs 1-100: 1,2,3, …​, 99,100. Ceci est utilisé pour agir sur chaque caractère de la cellule.

La partie suivante, MID (A1, ROW (INDIRECT (« 1: 100 »)), 1), fait référence à chaque caractère individuel de la chaîne. Il en résulte le tableau: « B », « R », « 2 » et « 7 ». Multiplier le tableau par 1 (la partie suivante de la formule)

entraîne la conversion de chacun des caractères individuels en un nombre. Si le caractère n’est pas un nombre, cette conversion génère une erreur. Dans le cas de la chaîne en cours de conversion (BR27), cela donne: #VALUE, #VALUE, 2 et 7.

L’étape suivante consiste à appliquer la fonction ISERROR aux résultats de la multiplication. Cela convertit les erreurs en TRUE et les non-erreurs en FALSE, ce qui donne TRUE, TRUE, FALSE et FALSE. La fonction MATCH recherche dans le tableau des valeurs TRUE et FALSE une correspondance exacte de FALSE. Dans cet exemple, la fonction MATCH renvoie le nombre 3, car la première valeur FALSE se trouve à la troisième position du tableau. À ce stade, nous connaissons essentiellement l’emplacement du premier nombre dans la cellule.

La fonction finale est REPLACE, qui est utilisée pour insérer réellement le tiret dans la chaîne source, en commençant au troisième caractère.

Comme vous pouvez le constater, la formule pour effectuer la transformation peut être un peu difficile à déchiffrer. Pour ceux qui sont si enclins, il peut être plus facile de simplement créer une fonction définie par l’utilisateur. La macro suivante est un exemple de celle qui renverra une chaîne avec le tiret au bon endroit:

Function DashIn(myText As String)

Dim i As Integer     Dim myCharCode As Integer     Dim myLength As Integer

Application.Volatile     myLength = Len(myText)

For i = 1 To myLength         myCharCode = Asc(Mid(myText, i, 1))

If myCharCode >= 48 And myCharCode <= 57 Then             Exit For         End If     Next i     If i = 1 Or i > myLength Then         DashIn = myText     Else         DashIn = Left(myText, i - 1) & "-" _           & Mid(myText, i, myLength - 1)

End If End Function

La macro examine chaque caractère de la chaîne d’origine et, lorsqu’elle trouve le premier caractère numérique, elle insère un tiret à ce stade. Vous utiliseriez la fonction de cette manière:

=DashIn(A1)

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (2613) s’applique à Microsoft Excel 97, 2000, 2002 et 2003. Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

link: / excelribbon-Inserting_Dashes_between_Letters_and_Numbers [Insertion de tirets entre les lettres et les nombres].