Séparer les lettres et les chiffres d'une cellule

[De Keyzer]

Bonjour à tous,

Je dispose d'un tableau avec des adresses. J'aimerais séparer l'adresse (partie texte) du numéro de rue (chiffre) (voir exemple ci-dessous). Le problème est que le nombre de lettres et de chiffres varie d'une adresse à l'autre et qu'il y a parfois une lettre après le chiffre (ex: rue de la Tulipe 523a ou 523 a). Quelqu'un a-t-il une idée de la procédure à suivre?

Cellule 1 = "Rue de la Tulipe 523" => Cellule 2 : "Rue de la Tulipe" et cellule 3 : "523".

Merci d'avance.

Jean-Luc

 

[andré]

Salut l'Empereur ... et compatriote,

C'est pas simple ce que tu demandes, mais je suis sur une piste.
Pour le moment elle ne fonctionne pas à ma guise, mais je ne désespère pas, ... alors patience.

Par contre, si le nom de la rue contient un chiffre (avenue du 5 ième Régiment), cela devient presqu'impossible.

Ândré.

 

[Dugenou]

Je pense qu'il faut rechercher le premier chiffre en partant de la gauche puis couper tout ce qui reste à droite.

Je mets au point la formule et je reviens.

Salut

 

[Dugenou]

Voici un essai un peu lourd car je recherche les chiffres de 1 à 9 un par un (j'ai pas trouvé mieux). Par contre, André a raison : s'il y a un chiffre dans le N° de rue : ça foire !

Peut être en partant par la gauche (rares sont les numéros à plus de 7chiffres) => J'essaye et je reviens

Salut

 

[andré]

Salut Dugenou,

J'ai effectivement la même approche, mais comment trouver la position du premier chiffre ?

Pour un chiffre spécifique, pas de problème :
Si c'est le 1 : =TROUVE(CAR(49);A1;1)

Mais comment écrire cette formule en y introduisant : ET(>CAR(48);<CAR(58)) ... et qu'XL ne refuse pas !

Ândré.

 

[Dugenou]

Ben voilà une solution (toujours un peu lourdingue) en imaginant que le N° ne peut pas avoir plus de 6 caractères (mais on peut faire plus si nécessaire) : on recherche un esapce suivi d'un chiffre par :

SI(ET(CODE(STXT($B5;NBCAR($B5)-C$4;1))=32;CODE(STXT($B5;NBCAR($B5)-C$4+1;1))>48;CODE(STXT($B5;NBCAR($B5)-C$4+1;1))<58)
en mettant en c4, d4 etc les chiffres de 1 à 6 et on renvoie le chiffre en question, ensuite on coupe avec gauche(b5;max(résultats du si))

exemple joint

 

[Mytå]

Salut De Keyzer, André et Dugenou

Pourquoi pas une petite macro sur les cellules sélectionées

Sub SplitAdresse()

Dim Cellule As Range
Dim txt As String
Dim x As Variant
Dim i As Byte

For Each Cellule In Selection

txt = Cellule.Text
x = Split(txt, " ")
Cellule.Offset(0, 1) = x(UBound(x))
Cellule.Offset(0, 2) = ""

For i = 0 To UBound(x) - 1
Cellule.Offset(0, 2) = Cellule.Offset(0, 2) + x(i) + " "
Next i

Next Cellule

End Sub


Mytå

 

[JL]

Merci pour vos propositions André, Dugenou et Mytä.

Je vais essayer tout ça et je vous tiens au courant.

JL

(Trop fort ce forum!!)

 

[JL]

Salut Mytä,

J'ai essayé de lancer ta macro mais je n'y arrive pas. Dois-je compléter quelque chose dans le programme? Lorsque que je là lance, Excel me signale que "Sub ou la fonction n'est pas définie" et il met en bleu Split de la ligne 8 et en jaune la première ligne.

Pour essayer ta macro, j'ai été dans macro => Visual Basic Editor et j'ai cliqué sur le petit cadre à gauche "fournir les programmes code". J'ai ensuite collé ta macro et cliqué sur le petit triangle bleu "lancer" en ayant auparavant sélectionné une cellule. (les termes utilisés ne sont peut-être pas tout à fait corrects (mon Excel est en néerlandais (visual basic est en anglais)).

Quel est le problème d'après toi (en dehors du fait que je ne maitrise pas les macros!)? Merci d'avance.

JL

 

[michel]

bonjour Jean-luc , André ,Dugenou et Myta

la macro de Myta fonctionne tres bien chez moi

ton soucis est peut etre lié à la version d'excel : il me semble que la fonction Split n'est disponible qu'a partir d'Excel2000


bonne soiree
michel

 

[Mytå]

Re bonjour le Forum

Pour compenser le manque de la fonction Split dans Excel 97

Remplacer dans la macro
x = Split(txt, " ") par x = Split_97(cell.Text, " ")
et ajouter la fonction suivante :

Function Split_97(Chaine$, Separateur$)
'FS, mpfe (renvoie un tableau de base 0)
Dim Tablo(), pos%, S$

S = Trim(Chaine): ReDim Tablo(0)
Recurse:
pos = InStr(1, S, Separateur)
If pos = 0 Then
Tablo(UBound(Tablo)) = S
Split_97 = Tablo()
Exit Function
Else
Tablo(UBound(Tablo)) = Left(S, pos - 1)
S = Right(S, Len(S) - pos)
ReDim Preserve Tablo(UBound(Tablo) + 1)
GoTo Recurse
End If
End Function

Mytå

 

[Siiann]

Re : Séparer les lettres et les chiffres d'une cellule

Bonjour, je cherche à effectuer la même chose, mais avec un nombre de caractère plus grand:

voici ce que j'ai :
A1: AGENCEMENTS INSTALLATION 117 758,92 175 869,02 1 859,90
A2: MATERIEL DE BUREAU 27 974,97 21 295,83 6 739,14
A3: MOBILIER DE BUREAU 68 488,44 31 760,03 36 228,41
A4: INTERETS COURUS SUR CREANCES 10 223,46 15 223,46

voici ce que j'aimerais obtenir :
A1: AGENCEMENTS INSTALLATION
A2: MATERIEL DE BUREAU
A3: MOBILIER DE BUREAU
A4: INTERETS COURUS SUR CREANCES

Sans formule idéale, j'ai converti mes cellules en colonne en séparant par les espaces (data-> text to columns), puis en supprimant A LA MAIN toutes les cellules contenant des chiffres, et enfin en concaténant les cellules restantes (celles contenant les lettres).
J'ai fait 3 fichiers de 700 lignes comme ça, mais pour le quatrième j'aimerais vraiment apprendre quelque chose.

J'ai pensé à concevoir une formule de concatenation uniquement si les cellules contiennent des lettres, pour éviter d'avoir à supprimer toute les cellules contenant des chiffres, mais je n'ai pas trouvé.

J'ai également essayé une formule trouvée sur un forum, en français, que j'ai traduite en anglais (oui, mon Excel est en anglais), mais ça n'a pas marché (erreur : "#NAME?")
formule=LEFT(A1,MAX((MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)>="0")*(STXT(A1,ROW(INDIRECT("1:" & LEN(A1))),1)<="9")*ROW(INDIRECT("1:" & LEN(A1)))))

Avez-vous une solution à ce problème ?

Merci

 

[Jocelyn]

Re : Séparer les lettres et les chiffres d'une cellule

Bonjour le Forum,
Bonjour Siiann,

une essai en fichier joint si j'ai bien compris, formule matricielle à valider en appuyant simultanément sur CTRL, Maj (au dessus de CTRL) et entrée

Bonne année

Cordialement

 

[Siiann]

Re : Séparer les lettres et les chiffres d'une cellule

Merci Jocelyn,

Je ne pensais pas avoir une réponse si rapide, je l'ai donc fait à la main dans l'heure qui a suivi mon message, mais je le recommencera avec ta solution pour apprendre quelque chose.


Merci beaucoup