XL 2016 Scinder cellule alphanumérique en plusieurs colonnes chiffres ou lettres

Vero2782 · 5 Octobre 2017

Bonjour,
J'ai une liste de cellule contenant le nom, numéro civique, ville, et un no de client. Malheureusement, il n'y a ni virgule, ni espaces pour séparer les informations...
ex. : TREMBLAY0258MONTREAL357896541258A.jpg

J'aimerais retirer les chiffres de la fin et le .jpg et obtenir TREMBLAY0258MONTREAL

J'ai réussit à retirer le "A.jpg" avec la formule matricielle suivante :
=LEFT(B1,MAX((MID(B1,ROW(INDIRECT("1:" & LEN(B1))),1)>="0")*(MID(B1,ROW(INDIRECT("1:" & LEN(B1))),1)<="9")*ROW(INDIRECT("1:" & LEN(B1)))))

Maintenant, je vois deux solutions :
1- La première, une macro qui séparerait l'information à chaque fois qu'on passe d'une lettre à un chiffre ou d'un chiffre à une lettre . J'aurais ainsi plusieurs colonne et il me resterait qu'à "Concacener" celles que j'ai besoin.

2- La deuxième, une macro qui compterait le nombre de chiffre à la fin de la cellule pour que je puisse ensuite la "trimmer" selon le nombre de digit.

J'ai trouvé cette macro sur ce forum mais elle compte aussi les chiffres du centre

Public Function CompteNumericDigit(ByRef Cell As Range)
Dim Expression As String, ExpressionC As String
Dim TotCar As Byte
Dim Compteur As Byte
Dim Car As String
Application.Volatile

Expression = Cell.Value
TotCar = Len(Expression)
For Compteur = 1 To TotCar
Car = Right(Left(Expression, Compteur), 1)
If IsNumeric(Car) = True Then
ExpressionC = ExpressionC & Car
End If
Next
CompteNumericDigit = Len(ExpressionC)
End Function

Pouvez-vous m'aider ?

Merci

zebanx · 7 Octobre 2017

Bonjour Staple1600, Bonjour MaPomme, Bonjour Vero2782

Un essai avec une plage non définie.
Cela permet de choisir le délimiteur (ici " ") et sa position (ici 3 après utilisation d'une fonction qui sépare les chiffres et les lettres).

Cdlt
zebanx

Edit : il est indispensable pour utiliser l'UDF ReNSpace de cocher une case dans la bibliothèques (outils / référence) :
MicrosoftVBScript Regular Expressions 5.5 Library

Tout est expliqué ici.
https://superuser.com/questions/632619/how-to-split-character-digit-in-excel

mapomme · 7 Octobre 2017

Bonjour zebanx ,

Ton code est simple et court. Étant d'une nullité crasse en expressions régulières et voyant que tu les maitrises, puis-je te demander d'expliciter les codes de RegExp et les patterns ?

Pour le fun, en réutilisant tes codes, j'ai transformé l'extraction en fonction personnalisée.

Tu es sans doute le gagnant de la plus petite procédure.

Staple1600 · 7 Octobre 2017

Bonjour zebanx

mapomme à dit:
Bonjour zebanx ,
Tu es sans doute le gagnant de la plus petite procédure.

[pj]Avec mes endives (as usual) et en late binding [/pj]

on peut la raccourcir un chouia

VB:

Function ReNSpace(text$)
With CreateObject("vbscript.regexp")
    .Pattern = "(?:(\D+)?(\d+))|(?:(\D+)(\d+)?)"
    .Global = True: .IgnoreCase = True
    If .Test(text) Then
        ReNSpace = WorksheetFunction.Trim(.Replace(text, "$1 $2 $3 $4"))
    Else
        ReNSpace = CVErr(xlErrValue)
    End If
End With
End Function

Tout comme mapomme, je veux bien que tu expliques ton pattern

zebanx · 7 Octobre 2017

Re-bonjour MaPomme,

Merci pour ton retour et d'avoir regardé et utilisé cette proposition de code.

J'aurais vraiment souhaité te répondre en détail après le post ultra-détaillé que tu m'as fait sur les SD mais mes connaissances "techniques" sont basses -).

Dans les faits, j'ai utilisé un UDF pour les besoins d'un fil (résolu par la suite par JOB75 notamment) en allant chercher des informations en anglais car je ne trouvais rien en francais .
C'est un oubli de ne pas rappeler l'auteur d'un UDF, désolé!.

Et afin d'éviter de te dire des inepties sur ce type de terme et de pouvoir t'apporter des réponses satisfaisantes, il est toutefois possible :

- de partir du lien internet qui présentait l'UDF (site qui mériterait surement qu'on le regarde de près! ) :
https://superuser.com/questions/632619/how-to-split-character-digit-in-excel

- d'un commentaire détaillé sur les REGEX (REGULAR EXPRESSIONS)
https://stackoverflow.com/questions...gex-in-microsoft-excel-both-in-cell-and-loops

J'espère que ces éléments pourront t'apporter des réponses satisfaisantes et des sources nouvelles de codes qui pourraient t'intéresser sur d'autres sujets.

Bonne journée à toi
zebanx.

Edit : @Staple 1600. Merci pour la simplification. Idem pour les explications sur l'UDF.

mapomme · 7 Octobre 2017

Re zebanx ,

OK, je vais voir du côté du lien que tu as fourni.

mapomme · 7 Octobre 2017

Bonjour à tous,

Pour le concours de celui qui a la plus petite procédure :

Une fonction groupeN(ByVal x$, xn&) qui retourne le xn ème groupe de lettres ou de chiffres du texte x.
On ignore le premier caractère "." et tous les caractères qui suivent.
La fonction est écrite exclusivement en VBA, le code est relativement concis.

VB:

Function groupeN(ByVal x$, xn&) As String
Dim T(), Ltr As Boolean, v As Boolean, n&, i&
   x = Split(Replace(x, " ", "") & ".", ".")(0)
   Ltr = Mid(x, 1, 1) < "0" Or Mid(x, 1, 1) > "9"
   n = 1: ReDim Preserve T(1 To n): T(1) = 1
   For i = 1 To Len(x)
      If (Mid(x, i, 1) < "0" Or Mid(x, i, 1) > "9") <> Ltr Then
         n = n + 1: ReDim Preserve T(1 To n): T(n) = i
         Ltr = Not Ltr
      End If
   Next i
   n = n + 1: ReDim Preserve T(1 To n): T(n) = i
   If xn <= UBound(T) - 1 Then groupeN = Mid(x, T(xn), T(xn + 1) - T(xn))
End Function

Staple1600 · 7 Octobre 2017

Re,

zebanx à dit:
Re-bonjour MaPomme,
https://superuser.com/questions/632619/how-to-split-character-digit-in-excel
- d'un commentaire détaillé sur les REGEX (REGULAR EXPRESSIONS)
https://stackoverflow.com/questions...gex-in-microsoft-excel-both-in-cell-and-loops
Edit : @Staple 1600. Merci pour la simplification. Idem pour les explications sur l'UDF.

Merci zebank pour ces liens forts intéressants.

Staple1600 · 7 Octobre 2017

Re

mapomme à dit:
Bonjour à tous,

Pour le concours de celui qui a la plus petite procédure :

Une fonction groupeN(ByVal x$, xn&) qui retourne le xn ème groupe de lettres ou de chiffres du texte x.
On ignore le premier caractère "." et tous les caractères qui suivent.
La fonction est écrite exclusivement en VBA, le code est relativement concis.

VB:

Function groupeN(ByVal x$, xn&) As String Dim T(), Ltr As Boolean, v As Boolean, n&, i& x = Split(Replace(x, " ", "") & ".", ".")(0) Ltr = Mid(x, 1, 1) < "0" Or Mid(x, 1, 1) > "9" n = 1: ReDim Preserve T(1 To n): T(1) = 1 For i = 1 To Len(x) If (Mid(x, i, 1) < "0" Or Mid(x, i, 1) > "9") <> Ltr Then n = n + 1: ReDim Preserve T(1 To n): T(n) = i Ltr = Not Ltr End If Next i n = n + 1: ReDim Preserve T(1 To n): T(n) = i If xn <= UBound(T) - 1 Then groupeN = Mid(x, T(xn), T(xn + 1) - T(xn)) End Function

Pas de doute, tu fais un blocage sur regexp

VBA rules the world !!

Ou comme on dirait dans le 35 : "Dame oui! VBA for ever "

zebanx · 7 Octobre 2017

Staple1600 à dit:
Re,
Merci zebank pour ces liens forts intéressants.

Ah tant mieux.
Pas évident avec votre expertise d'aller chercher des compléments mais on arrive parfois à trouver quelques UDF qui ne manquent pas d'intérêt ni sur le fond et souvent sur la forme.
Et on n'hésite pas à les faire partager !
Bonne soirée

mapomme · 7 Octobre 2017

Bonsoir zebanx ,

Je me joins à Staple1600 pour te remercier pour les deux liens. Je n'ai plus qu'à me mettre au boulot maintenant .

Vero2782 · 8 Octobre 2017

Bonjour à tous,
Je vois que vous vous êtes amusés à gallerer sur mon problème. Wow ! Merci beaucoup, toutes vos solutions fonctionnent super bien. Je vais devoir étudier vos codes pour bien les comprendre mais vous faites ma soirée.

Un Gros Gros merci pour vos programmations, vous être vraiment doués.

Véro

eriiic · 8 Octobre 2017

Bonjour,

avec une formule matricielle :

Code:

=GAUCHE(A2;MAX(ESTNUM(-STXT(A2;LIGNE(2:99);1))*NON(ESTNUM(-STXT(A2;LIGNE(1:98);1)))*LIGNE(1:98)))

eric

XL 2016 Scinder cellule alphanumérique en plusieurs colonnes chiffres ou lettres

Vero2782

XLDnaute Nouveau

zebanx

XLDnaute Accro

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

Staple1600

XLDnaute Barbatruc

zebanx

XLDnaute Accro

mapomme

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Pièces jointes

Staple1600

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

zebanx

XLDnaute Accro

mapomme

XLDnaute Barbatruc

Vero2782

XLDnaute Nouveau

eriiic

XLDnaute Barbatruc

Discussions similaires