Marty a une feuille de calcul avec une longue série de références dans la colonne A.

Ceux-ci se composent de lettres et de chiffres, tels que A123BC, AB123C, etc. Marty veut diviser les données en trois colonnes, de sorte que le texte avant les nombres soit dans une colonne, les nombres dans la deuxième colonne et le texte après les nombres dans le troisième.

Le facteur qui complique la division du numéro de pièce en segments est qu’il n’y a pas de longueur définie pour chaque composant du numéro de pièce combiné. Si les composants étaient de longueurs standard, vous pouvez utiliser la fonction Texte en colonnes dans Excel. Puisqu’ils ne le sont pas et qu’il n’y a pas de délimiteur entre les composants, cette avenue potentielle pour une solution est fermée.

Si vous souhaitez utiliser des formules pour séparer les numéros de pièce, vous en aurez besoin de trois, un pour chaque composant que vous souhaitez extraire.

En supposant que les numéros de pièce suivent le modèle indiqué (texte, chiffres, texte) et que le premier numéro de pièce est dans la cellule A1, vous pouvez utiliser ce qui suit dans la cellule B1:

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

Cela doit être entré sous forme de formule matricielle, ce qui signifie que vous devez l’entrer en utilisant Ctrl + Maj + Entrée. La formule trouve le premier chiffre numérique dans le numéro de pièce, puis renvoie tout ce qui précède ce chiffre.

Il fonctionnera sur n’importe quel numéro de pièce qui ne dépasse pas 100 caractères.

Pour extraire le deuxième composant du numéro de pièce, vous pouvez mettre la formule suivante dans la cellule C1:

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

Encore une fois, il s’agit d’une formule unique et elle doit être saisie sous forme de formule matricielle (Ctrl + Maj + Entrée) afin qu’elle puisse fonctionner sur chaque caractère du numéro de pièce d’origine. Il examine le numéro de pièce et détermine le point de départ des chiffres, puis extrait tous ces chiffres. Il renvoie une chaîne de texte, même si cette chaîne est composée de chiffres. Si vous voulez qu’il soit réellement traité comme un nombre (ce qui éliminerait les zéros non significatifs, bien sûr), vous devez alors enfermer la formule entière dans une fonction Value, comme indiqué ici:

=VALUE(MID(A1,MATCH(FALSE,ISERROR(1MID(A1,ROW(INDIRECT("1:100")), 1)),0),COUNT(1MID(A1,ROW(INDIRECT("1:100")),1))))

Pour obtenir le dernier composant du numéro de pièce, vous devez utiliser la formule suivante, à nouveau saisie sous forme de formule matricielle:

=MID(A1,MATCH(FALSE,ISERROR(1MID(A1,ROW(INDIRECT("1:100")), 1)),0)+COUNT(1MID(A1,ROW(INDIRECT("1:100")),1)),100)

Bien que cette approche fonctionne très bien, les formules matricielles sont notoirement intensives en calcul, en particulier lorsque vous avez beaucoup de formules dans votre feuille de calcul. Si vous avez besoin de séparer un millier de références, cela signifie que vous vous retrouvez avec 3000 formules matricielles, ce qui peut être très, très lent à recalculer.

Si vous vous trouvez dans cette situation, vous pouvez utiliser une macro pour séparer les numéros de pièce. La macro suivante devrait fonctionner sur les numéros de pièce qui suivent le modèle de texte, chiffres, texte, comme déjà décrit.

Sub Split1()

Dim C As Range     Dim sNew As New     Dim i As Integer          For Each C In Selection         sNew = ""

i = 1

' Get first part, which is text         Do While IsNumeric(Mid(C, i, 1)) = False             sNew = sNew & Mid(C, i, 1)



i = i + 1             If i > Len(C) Then Exit Do         Loop         C.Offset(0, 1).Value = sNew

' Pull next part, which should be digits         sNew = ""

Do While IsNumeric(Mid(C, i, 1)) = True             sNew = sNew & Mid(C, i, 1)



i = i + 1             If i > Len(C) Then Exit Do         Loop         C.Offset(0, 2).Value = sNew

' Use rest of original cell         sNew = Mid(C, i, Len(C))

C.Offset(0, 3).Value = sNew     Next C End Sub

Pour utiliser la macro, sélectionnez simplement les numéros de pièce et exécutez-la. La macro utilise le concept de recherche des changements entre les valeurs numériques / non numériques dans une chaîne de caractères dans la cellule. Lorsqu’une de ces limites est atteinte, la partie de la chaîne d’origine avant la limite est insérée dans une nouvelle cellule. Ce concept peut être un peu raccourci, comme cela est fait dans l’exemple suivant.

Sub Split2()

Dim C As Range     Dim j As Integer     Dim k As Integer

For Each C In Selection         j = 1         Do While Not (IsNumeric(Mid(C.Value, j, 1))) And j <= Len(C)

j = j + 1         Loop         k = j

Do While IsNumeric(Mid(C.Value, k, 1)) And k <= Len(C)

k = k + 1         Loop

C.Offset(0, 1) = Left(C, j - 1)

C.Offset(0, 2) = Mid(C, j, k - j)

C.Offset(0, 3) = Mid(C, k, Len(C) - (k - 1))

Next C End Sub

La différence entre cette version de la macro et la précédente, bien sûr, est que cette version parcourt la cellule d’origine et détermine les limites en même temps. Lorsqu’ils sont connus, les composants du numéro de pièce d’origine sont insérés dans les cellules.

Une approche intéressante pour séparer les numéros de pièce consiste à utiliser quelques courtes fonctions définies par l’utilisateur qui déterminent où se trouvent les limites entre les composants. Considérez les deux fonctions suivantes:

Function pNumber(X)

i = 1     Do Until Mid(X, i, 1) Like "#": i = i + 1: Loop     pNumber = i End Function
Function pAlpha(X)

X = UCase(X)

i = pNumber(X)

Do Until Mid(X, i, 1) Like "[A-Z]": i = i + 1: Loop     pAlpha = i End Function

Celles-ci sont beaucoup plus courtes que les macros précédentes, et tout ce qu’elles font est de renvoyer la limite où les nombres commencent (dans le cas de pNumber) et la limite où le deuxième groupe de texte commence (dans le cas de pAlpha). Pour utiliser les fonctions, vous utilisez les trois formules suivantes pour renvoyer, respectivement, les premier, deuxième et troisième composants du numéro de pièce d’origine:

=MID(A1,1,pNumber(A1)-1)

=MID(A1,pNumber(A1),pAlpha(A1)-pNumber(A1))

=MID(A1,pAlpha(A1),LEN(A1)-pAlpha(A1)+1)

_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 (10369) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365. Vous pouvez trouver une version de cette astuce pour l’ancienne interface de menu d’Excel ici:

link: / excel-Breaking_Up_Variable-Length_Part_Numbers [Rupture des numéros de pièces de longueur variable].