Séparation chiffre et lettre d'une adresse dans la même cellule

[vgrenet]

Bonjour à tous,

Je rencontre un petit soucis d'équivalence de colonne d'adresse.

Comme toute base adresse je me retrouve avec des chiffres associés à des numéros de rue de type : "8Bis rue Vincent Auriol", ou "13A du 56ième régiment..."

Je souhaiterais simplement séparer le numéro de la lettre en insérant un espace. C'est à dire que le 8Bis devient le 8 Bis ou le 13A devient le 13 A.
Bien entendu il arrive le plus souvent qu'il n'y ait pas de lettre derrière le numéro ou même qu'il n'y ait pas de numéro de type : "Chem du cornout"

Petit exemple en pièce jointe...

Vous voyez une solution ?

Cordialement

 

[phlaurent55]

Re : Séparation chiffre et lettre d'une adresse dans la même cellule

Bonjour,

avec ce code:

Sub separer()
Application.ScreenUpdating = False
Dim cellule As Range
For Each cellule In Range("A2:A" & Range("A65535").End(xlUp).Row)
For i = 1 To Len(cellule)
If Mid(cellule, i, 1) < "0" Or Mid(cellule, i, 1) > "9" Then
Cells(cellule.Row, 3) = Left(cellule, i - 1) & " " & Right(cellule, Len(cellule) - i + 1)
If Left(Cells(cellule.Row, 3), 1) = " " Then
Cells(cellule.Row, 3) = Right(Cells(cellule.Row, 3), Len(Cells(cellule.Row, 3)) - 1)
End If
Exit For
End If
Next i
Next cellule
Application.ScreenUpdating = True
End Sub

à+
Philippe

 

[VDAVID]

Re : Séparation chiffre et lettre d'une adresse dans la même cellule

Bonjour vgrenet,phlaurent55,

Une autre proposition par macro:

Sub retraiter()

Dim i, h, k, z As Integer
Dim Num As String
Dim Flag1 As Boolean

Num = "0123456789"

z = 0
    For i = 2 To Range("A65536").End(xlUp).Row

        For h = 1 To Len(Range("A" & i).Value)
            
            For k = 1 To 10
                
                If InStr(h, Mid(Range("A" & i).Value, h, 1), Mid(Num, k, 1)) <> 0 Then
                    
                    Flag1 = True
                    z = z + 1
                    GoTo Borne:
                    Else
                    Flag1 = False
                    
                End If
                
                If Flag1 = False And z > 0 Then
                Range("A" & i).Value = Replace(Mid(Range("A" & i).Value, 1, h), " ", "") & " " & LTrim(Mid(Range("A" & i).Value, h + 1, Len(Range("A" & i).Value)))
                z = 0
                End If
            
            Next k
Borne:
        Next h
        
    Next i
    
End Sub

Bonne journée

 

[david84]

Re : Séparation chiffre et lettre d'une adresse dans la même cellule

Bonjour,
une fonction personnalisée à tester :

Function Séparer(Chaine As String) As String
Dim oRegExp As Object, Matches As Object
Set oRegExp = CreateObject("vbscript.regexp")
With oRegExp
    .ignorecase = True
    .Pattern = "^(\d+)([A-Z]\s)(.+)"
    If .test(Chaine) = True Then
        Séparer = .Replace(Chaine, "$1 $2$3")
    Else
        Séparer = Chaine
    End If
End With
End Function

=Séparer(A2)

et tirer la fonction vers le bas.
A+

 

[vgrenet]

Re : Séparation chiffre et lettre d'une adresse dans la même cellule

Bonjour,

Merci pour vos réponses rapide.

J'ai essayé les deux solutions.
Philippe lorsque j’exécute votre macro il ne se passe rien... j'ai regarder mais je ne vois pas le problème pourquoi elle ne s'enclenche pas.

VDAVID la votre fonctionne parfaitement pour la colonne A

Comment puis-je faire pour qu'elle fonctionne sur une sélection de cellule qui ne sont pas forcément sur la colonne A ?
Je remplace tous les Range ("A") par Cells.select ???

Vous avez compris je suis un peu novice en macro. Je reprends dans l'aide en ligne vos fonctions pour y voir un peu plus claire.

Merci

 

[vgrenet]

Re : Séparation chiffre et lettre d'une adresse dans la même cellule

oups "j'ai regardé" et non "regarder"
oubli de relecture

 

[VDAVID]

Re : Séparation chiffre et lettre d'une adresse dans la même cellule

Re à tous,

Il est vrai qu'il serait plus simple de procéder avec une boucle For Each.

Le code modifié:

Sub retraiter()

Dim h, k, z As Integer
Dim Num As String
Dim Flag1 As Boolean
Dim Cel, myRange As Range

Num = "0123456789"

z = 0
    Set myRange = Range("A2:A" & Range("A65536").End(xlUp).Row) 'Modifier la plage selon le souhait (Cf: http://www.excelabo.net/pasapas/tables_vba) Merci Misange :)
    For Each Cel In myRange

        For h = 1 To Len(Cel.Value)
            
            For k = 1 To 10
                
                If InStr(h, Mid(Cel.Value, h, 1), Mid(Num, k, 1)) <> 0 Then
                    
                    Flag1 = True
                    z = z + 1
                    GoTo Borne:
                    Else
                    Flag1 = False
                    
                End If
                
                If Flag1 = False And z > 0 Then
                Cel.Value = Replace(Mid(Cel.Value, 1, h), " ", "") & " " & LTrim(Mid(Cel.Value, h + 1, Len(Cel.Value)))
                z = 0
                End If
            
            Next k
Borne:
        Next h
        
    Next Cel
    
End Sub

Du coup tu as juste

Set myRange = Range("A2:A" & Range("A65536").End(xlUp).Row)

cette ligne à adapter
N'hésites pas à consulter le lien que j'ai mis en commentaire dans le code pour la plage de données.

Bonne journée

 

[vgrenet]

Re : Séparation chiffre et lettre d'une adresse dans la même cellule

Re Bonjour,

Oui en effet ça fonctionne très bien comme ça. Je consulte le lien du commentaire.

Merci encore pour votre aide

Bonne journée

 

[vgrenet]

Re : Séparation chiffre et lettre d'une adresse dans la même cellule

Erratum

Après application sur ma base complète je me rends compte que la macro ne fonctionne pas si le nombre n'est composé que d'un chiffre.
13A = OK
8A = KO

Cordialement

 

[david84]

Re : Séparation chiffre et lettre d'une adresse dans la même cellule

Re
As-tu testé la solution que je t'avais proposée au message #4 ?
A+

 

[ralph45]

Re : Séparation chiffre et lettre d'une adresse dans la même cellule

Bonjour à tous,

Perso, j'ai testé la fonction personnalisée de david84.
Elle fonctionne en faisant la séparation du 18A en 18 A, mais pas du 13BIS en 13 BIS...

Je propose cette version à peine modifiée... Ai fait le test sur 2 adresses au pif, cela semble fonctionner.

Function Séparer(Chaine As String) As String
Dim oRegExp As Object, Matches As Object
Set oRegExp = CreateObject("vbscript.regexp")
With oRegExp
    .ignorecase = True
    .Pattern = "^(\d+)([A-Z])(.+)"
    If .test(Chaine) = True Then
        Séparer = .Replace(Chaine, "$1 $2$3")
    Else
        Séparer = Chaine
    End If
End With
End Function

A+

 

[vgrenet]

Re : Séparation chiffre et lettre d'une adresse dans la même cellule

Re
As-tu testé la solution que je t'avais proposée au message #4 ?
A+

Re

Non excuses-moi mais je ne comprend pas le système, tu peux m'expliquer le fonctionnement ?
Je colle le code dans un nouveau module de la feuille VBA ? Et après je tape "=Séparer (A2)" dans la case A2 ???

Merci pour ton éclairage

 

[ralph45]

Re : Séparation chiffre et lettre d'une adresse dans la même cellule

Re

Non excuses-moi mais je ne comprend pas le système, tu peux m'expliquer le fonctionnement ?
Je colle le code dans un nouveau module de la feuille VBA ? Et après je tape "=Séparer (A2)" dans la case A2 ???

Merci pour ton éclairage

Re, si je puis me le permettre...
A 99% yes, sauf que tu colles =Séparer(A2)... dans une autre cellule, exemple B2.

A+

EDIT : autres tests avec des TER, QUATER, B, etc. "Jusqu'à présent, tout va bien"

 

[vgrenet]

Re : Séparation chiffre et lettre d'une adresse dans la même cellule

Re,

Oui c'était tout bête pour le fonctionnement.
Ça marche nickel merci à tous.

Bonne journée

 

[david84]

Re : Séparation chiffre et lettre d'une adresse dans la même cellule

Re
@Ralph45 : sympa que tu t'essaie aux expressions rationnelles ! Le motif initial répondait expressément aux exemples fournis dans le fichier. Maintenant, si tu veux prendre en compte les BIS, TER,... cela donnerait plutôt :

Function Séparer(Chaine As String) As String
Dim oRegExp As Object
Set oRegExp = CreateObject("vbscript.regexp")
With oRegExp
    .ignorecase = True
    .Pattern = "^(\d+)([A-Z]+\s)(.+)"
    If .test(Chaine) = True Then
        Séparer = .Replace(Chaine, "$1 $2$3")
    Else
        Séparer = Chaine
    End If
End With
End Function

Si tu veux des explications, n'hésite pas à poser des questions.
A+