Séparateur tous les 3 chiffres à partir du 1er chiffre

gringoh

XLDnaute Junior
Bonjour le forum,

J’ai une petite question qui me pose quelques problèmes.

Disons que j’ai un nombre à 8 chiffres 12345678 dans un tableau que souhaite présenter de la façon suivante :

123 456 78

J’ai essayé plein de formats de nombres personnalisés mais comme la séparation commence à la décimale je n’arrive à obtenir le résultat souhaité.

A chaque fois le résultat obtenu est :

12 345 678

J’essaye de trouver une astuce qui permette d’effectuer une séparation tous les 3 chiffres en commençant par le 1er chiffre et nom par la décimale. Ce qui fait que si mon nombre n’avait que 4 chiffres, il apparaitrait ainsi :

123 4

J’ai vraiment essayé plein de choses et je ne trouve rien.

Toute aide serait la bienvenue.
Par avance merci,
G
 

Jacky67

XLDnaute Barbatruc
Bonjour le forum,

J’ai une petite question qui me pose quelques problèmes.

Disons que j’ai un nombre à 8 chiffres 12345678 dans un tableau que souhaite présenter de la façon suivante :

123 456 78

J’ai essayé plein de formats de nombres personnalisés mais comme la séparation commence à la décimale je n’arrive à obtenir le résultat souhaité.

A chaque fois le résultat obtenu est :

12 345 678

J’essaye de trouver une astuce qui permette d’effectuer une séparation tous les 3 chiffres en commençant par le 1er chiffre et nom par la décimale. Ce qui fait que si mon nombre n’avait que 4 chiffres, il apparaitrait ainsi :

123 4

J’ai vraiment essayé plein de choses et je ne trouve rien.


Toute aide serait la bienvenue.
Par avance merci,
G
Bonjour,
Peut-être
Format personnalisé==> ###\ ###\ ###\ ##
 

job75

XLDnaute Barbatruc
Bonjour gringoh, Jacky67,

Le nombre étant en A1 entrez en B1 la formule :
Code:
=TEXTE(A1;REPT("###\ ";ENT(NBCAR(A1)/3))&REPT("#";NBCAR(A1)-3*ENT(NBCAR(A1)/3)))
B1 est bien entendu un texte, pour le reconvertir en nombre utiliser :
Code:
=--SUBSTITUE(B1;" ";)
A+
 

gringoh

XLDnaute Junior
Merci pour vos réponses,

@Jacky67
Je viens d’essayer et ton astuce fonctionne quand il y a 8 chiffres, mais s’il y en a 6 par exemple l’affichage devient 1 234 56

@job75
Merci pour ton retour, mais dans un premier temps, je souhaite essayer de réaliser le truc uniquement avec un affichage personnalisé

Pas si simple cette histoire…
 

job75

XLDnaute Barbatruc
Voyez le fichier joint et cette macro :
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Intersect(Target, [A:A], UsedRange)
If r Is Nothing Then Exit Sub
For Each r In r 'si entrées multiples
    If IsNumeric(CStr(r)) Then
        If Int(r) = r Then
            r.NumberFormat = Application.Rept("000\ ", Int(Len(r) / 3)) & String(Len(r) - 3 * Int(Len(r) / 3), "0")
        Else
            r.NumberFormat = "General"
        End If
    End If
Next
End Sub
 

Pièces jointes

  • Formatage(1).xlsm
    20.2 KB · Affichages: 12

job75

XLDnaute Barbatruc
Bonjour le forum,

La macro précédente ne va pas si l'on entre des valeurs simultanément dans un grand nombre de cellules.

Pour y remédier utiliser :
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, t, n%, nn&
Set r = Intersect(Target, [A:A], UsedRange)
If r Is Nothing Then Exit Sub
If r.Count > 9999 Then t = Timer 'mesure de la durée à partir de 10000 cellules
Application.ScreenUpdating = False
r.NumberFormat = "General" 'RAZ
With Range(Replace(r.Address, ",", ":"))
    .Columns(2).Resize(, 2).Insert xlToRight  '2 colonnes auxiliaires
    With .Columns(2)
        .Cells(1, 2) = 1: .Columns(2).DataSeries 'repérage du classement
        .FormulaR1C1 = "=IF(ISNUMBER(RC[-1]),IF(NOT(MOD(RC[-1],1)),LEN(RC[-1])))"
        .Value = .Value 'supprime les formules
        .EntireRow.Sort .Columns(1) 'tri pour regrouper et accélérer
        For n = 4 To Application.Max(.Value)
            nn = Application.CountIf(.Cells, n)
            If nn Then
                Set r = .Cells(Application.Match(n, .Cells, 0), 0).Resize(nn)
                r.NumberFormat = Application.Rept("000\ ", Int(n / 3)) & String(n - 3 * Int(n / 3), "0")
            End If
        Next
        .EntireRow.Sort .Columns(2), xlAscending, Header:=xlNo 'ordre initial
        .Resize(, 2).Delete xlToLeft
    End With
End With
If t Then Application.ScreenUpdating = True: MsgBox "Durée " & Format(Timer - t, "0.00 \s")
End Sub
Avec une entrée simultanée dans 100 000 cellules cette macro s'exécute chez moi en 1,2 seconde.

Fichier (2).

A+
 

Pièces jointes

  • Formatage(2).xlsm
    24.3 KB · Affichages: 7

gringoh

XLDnaute Junior
Merci beaucoup pour ton aide, je garde cette macro sous le coude.

Malheureusement ce n’est pas ce que je recherche, je cherchais vraiment à passer par le menu format de cellules pour des raisons pratiques.

Au final cela me parait difficile alors qu’à la base il s’agit d’un petit rien du tout :)
 

Membres actuellement en ligne

Statistiques des forums

Discussions
312 803
Messages
2 092 250
Membres
105 316
dernier inscrit
cyrille225