XL 2016 honte au "0" ( débutant un nombre )

[tuti]

bonjour,
excel n'aime pas les 0 devant un nombre
hors, mon numéro entier prends 10 chiffre

dans une cellule A1, je note
1234

mais à la base, j'aurais aimer
0000001234



attention, mon nombre "1234" contient 4 numéro donc il faut mettre 6 "0" pour avoir "0000001234"
mais mon nombre suivant peux être "147852" donc il faut 4 "0" pour avoir "0000147852"

en B1,
quel formule pour ajouter les 0 pour obtenir une série de 10 chiffre ?


merci

 

[fanfan38]

Bonjour
Un fichier est TOUJOURS le bienvenu
selectionne A1
Clic droit
format de cellule
Nombre
Personnalisée
En dessous de Type à la place de standard tu mets 10 0 soit 0000000000

A+ François

 

[Phil69970]

Bonjour à tous

@tuti
Et si tu fais comme à dit François tu auras ceci sur la colonne A

 

[crocrocro]

Bonjour le fil,
@tuti, si votre nombre est un faux-nombre, c'est à dire que toute opération numérique sur ce nombre serait absurde et que vous ne ferez aucune opération dessus (somme, addition, moyenne ...), par exemple le n° d'Insee, préférez alors mettre un format de cellule "Texte".
[EDIT] je complète ici : si on veut contrôler avec le format de cellule Texte qu'on saisit bien un nombre de 10 chiffres, Données -> Validation de Données -> Personnalisé et dans la formule (ici pour la cellule D50)

=ET(CNUM(D50);NBCAR(D50)=10)

 

[tuti]

merci pour vos réponses

il s'agit bien d'un faux-nombre
il s'agit d'un numéro de dossier dont la série pleine fait 10 chiffres ( ils prévoient sur du long terme )

par facilité, les gens ne saisissent que les nombres "complet"
jamais, ils ne prendront la peine d'écrire les 0 manquant ( donc je fais en sorte que la feuille pensent pour eux )

 

[crocrocro]

@tuti ,
si c'est un faux nombre il faudra donc selon moi :
- soit procéder comme j'ai écrit dans le post 4
- soit passer par une macro qui complète la saisie sans 0 par les 0 pour atteindre les 10 chiffres avec toujours la cellule au format Texte.
Le Format numérique personnalisée n'est que de l'affichage, du visuel. Par exemple en A1
saisie de 12345, avec un format personnalisé = "0000000000", affichage 0000012345.
Si vous écrivez en B1 =(A1="0000012345"), la valeur retournée sera FAUX
Si vous écrivez en B1 =(A1="12345"), la valeur retournée sera FAUX
Si vous écrivez en B1 =(A1=12345), la valeur retournée sera VRAI
en procédant comme je l'ai indiqué
Si vous écrivez en B1 =(A1="0000012345"), la valeur retournée sera VRAI

 

[tuti]

pour la macro, j'évite au maximum ( ne les maitrisant pas )

aussi, j'ai appliquer le visuel sur mes 2 colonnes
en concatenant les 2 colonnes, je me suis aperçu 'du visuel' ( j'ai perdu mes 0 )

[EDIT] je complète ici : si on veut contrôler avec le format de cellule Texte qu'on saisit bien un nombre de 10 chiffres, Données -> Validation de Données -> Personnalisé et dans la formule (ici pour la cellule D50)

dans ce cas, si je comprends bien
je force/pénalise l'user à ajouter les 0 manquant ?

 

[AtTheOne]

Bonsoir à toutes & à tous
Si c'est pour transformer le contenu de la cellule modifiée (j'ai saisi 12345 et je veux "0000012345") il faudra passer par une macro (événement Worksheet_Change).
Si c'est pour obtenir le code dans une autre cellule (mettons je saisis 12345 en A1 et je veux "0000012345" en B1.
Mettre en B1 la formule =TEXTE(A1;"0000000000").
Pour la macro je vais retourner sur mon PC tout à l'heure, mais la base sera Target.value=Format(Target.value,"0000000000")
Avec en préalable un verouillage des événements par EnabledEvents = false sans oublier de les rétablir avant la fin de la macro.
À bientôt

 

[tuti]

en B1.
Mettre en B1 la formule =TEXTE(A1;"0000000000").

si je fais cela dans mes 2 colonnes,
est ce que je peux concatener 2 colonnes dans la foulée ?
( ou alors , faire un tir grouper dans ma cellule concatener )

 

[crocrocro]

Bonsoir @tuti, @AtTheOne ,
avec la formule proposée par AtTheOne en C2 et la concaténation de D2 avec C2 en E2
avec la concaténation directe en E4

Mais, n'oubliez pas de mettre dans les données de validation un contrôle pour B2 qui doit être un nombre entier à priori compris entre 1 et 9999999999.

 

[AtTheOne]

Re

si je fais cela dans mes 2 colonnes,
est ce que je peux concatener 2 colonnes dans la foulée ?

@crocrocro viens de te répondre, je n'ai plus rien à ajouter...
A+

 

[job75]

Bonsoir à tous,

Voyez le fichier joint et cette macro qui contrôle les entrées en colonne A :

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Intersect(Target, Columns(1), UsedRange)
If Target Is Nothing Then Exit Sub
Dim x$, y$, n%, i%, z$
Application.EnableEvents = False 'désactive les évènements
Columns(1).NumberFormat = "@" 'format Texte
For Each Target In Target 'si entrées multiples
    x = Target: y = "": n = 0
    For i = 1 To Len(x)
        z = Mid(x, i, 1)
        If IsNumeric(z) Then
            n = n + 1
            y = y & z
            If n = 10 Then Exit For 'limite
        End If
    Next i
    If y <> "" Then If Len(y) < 10 Then y = String(10 - Len(x), "0") & y
    Target = y
Next Target
Application.EnableEvents = True 'réactive les évènements
End Sub

A+

 

[AtTheOne]

Bonne nuit à toutes & à tous
@tuti , je vois le code de @job75 , que je salue, et je me rappelle que j'ai dis

Pour la macro je vais retourner sur mon PC tout à l'heure,

Donc voilà une autre version moins blindée que la sienne (je n'épure pas la saisie des caractère non numériques, et en cas de saisie d'un séparateur décimal les décimales sont tronquées) mais bon, tu choisiras, et peut-être préféreras-tu la solution par formule.

J'ai fais un tableau structuré pour les saisies ("tbSaisies") il y a deux colonnes à toi de le mettre à ta sauce.

Les nouvelles saisies se font en tapant des chiffres juste sous le tableau qui va s'étendre automatiquement.

Voilà le texte de la macro :

Private Sub Worksheet_Change(ByVal Target As Range)
    
     Dim C As Range
    
     'S'assurer qu'au moins une cellule modifiée se situe dans la colonne «N°»
     If Intersect(Target, [tbSaisies[N°]]) Is Nothing Then Exit Sub
    
     Application.EnableEvents = False
    
     'En cas de saisie multiple
     For Each C In Intersect(Target, [tbSaisies[N°]]).Cells
          
          'Tester si cette cellule peut être interprétée comme un nombre
          If Not Intersect(C, [tbSaisies[N°]]) Is Nothing And IsNumeric(C) Then
              'Si le contenu est dans les limites des numéros convertir en chaîne de 10 chiffres
              If CDbl("0" & C) > 0 And CDbl("0" & C) <= 9999999999# Then C.Value = "" & Format(C, "0000000000") Else C.ClearContents
          Else
               C.ClearContents
          End If
     Next
    
     Application.EnableEvents = True
    
End Sub

Voir le fichier en pièce jointe
À bientôt

 

[tuti]

merci pour les réponses

j'ai essayer la formule de @crocrocro

en colonne 3,

=CONCATENER(TEXTE([colonne1];00000)&"-"&TEXTE([@[colonne2]];0000000000))

mais il m'a supprimer mes 0 en trop pour donner

=CONCATENER(TEXTE([colonne1];0)&"-"&TEXTE([@[colonne2]];0))

 

[TooFatBoy]

Hopla !

mais il m'a supprimer mes 0 en trop pour donner

Et avec les guillemets ???