Format de cellule personnalisé

Titesoeur · 16 Juin 2020

Bonjour,

Petit soucis de format personnalisé sous excel 2016.
Je souhaiterais pouvoir mettre en forme une série de chiffres (23) en intégrant des espaces tous les 4 caractères avec seulement 3 caractères sur la dernière partie.

Explication:
Je tape : 12345678901236547896541
et je voudrais : 1234 5678 9012 3654 7896 541

J'ai tout essayé mais rien ne fonctionne. Quand la "mise en forme" fonctionne, les derniers caractères se transforment en 0 (Normal puisque Excel ne prend pas plus d'un certain nombre de chiffres 15 je crois). Du coup en texte ça fonctionne mais il faut taper les espaces pas cool...

Je suis preneuse de toute aide venue et je vous remercie tous d'avance.

sylvanu · 16 Juin 2020

Bonsoir,
Une solution en rajoutant une colonne avec :

VB:

=STXT(D6;1;4)&" " &STXT(D6;5;4)&" " &STXT(D6;9;4)&" " &STXT(D6;13;4)&" " &STXT(D6;17;4)&" " &STXT(D6;21;3)

et en masquant la colonne ayant les nombres.
Attention faire précéder les "nombres" par une apostrophe pour être considérés comme un texte sinon XL tronquera ce nombre.

jmfmarques · 16 Juin 2020

Bonjour
l'utilisation de vba me parait plus judicieuse dans un tel cas. Elle permet de traiter directement la valeur saisie et d'éviter en outre de formater à nouveau indûment.
Exemple :

VB:

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Column = 1 Then

    If Target.Value Like String(23, "#") Then

      Target.Value = Format(Target.Value, "@@@@ @@@@ @@@@ @@@@ @@@@ @@@")

    End If

  End If

End Sub

Simply · 16 Juin 2020

Bonjour

=TEXTE(GAUCHE(A1;12);"0000 0000 0000 ")&TEXTE(DROITE(A1;11);"0000 0000 000")

Titesoeur · 16 Juin 2020

Merci pour vos réponses très utiles.
j’avoue que j’ai une préférence pour la VBA.
Je vais tester. Merci beaucoup

Titesoeur · 16 Juin 2020

jmfmarques à dit:
Bonjour
l'utilisation de vba me parait plus judicieuse dans un tel cas. Elle permet de traiter directement la valeur saisie et d'éviter en outre de formater à nouveau indûment.
Exemple :

VB:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then If Target.Value Like String(23, "#") Then Target.Value = Format(Target.Value, "@@@@ @@@@ @@@@ @@@@ @@@@ @@@") End If End If End Sub

Sniff, ça ne fonctionne pas.

Après, j'ai déjà plusieurs macro sur le fichier, donc je pense que ça doit coincer quelque part.

Je joins le fichier pour plus de compréhension.

Le format doit se faire dans la colonne AG soit la n°33.

J'en profite pour un autre soucis.
La macro d'insertion fonctionne super bien à l'ouverture du fichier, mais par la suite ça bug (mais uniquement au bureau, pas chez moi). Une idée???

jmfmarques · 16 Juin 2020

Je n'ouvre aucun classeur tiers.
fonctionne sans faille chez moi, pour autant que (relire ce qu'en dit sylvanu) :

les "nombres" ont bien précédés par une apostrophe pour être considérés comme un texte sinon XL tronquera ce nombre.

Titesoeur · 16 Juin 2020

jmfmarques à dit:
Je n'ouvre aucun classeur tiers.
fonctionne sans faille chez moi, pour autant que (relire ce qu'en dit sylvanu) :

Du coup, voici ta macro avec celle déjà en place sur la même feuille.

Si tu veux bien y jeter un coup d'œil et simplement m'indiquer ce que je dois modifier, tu m'enlèverais une sacrée épine du pied. Je me doute que c'est dans le "vocabulaire" (zz et Target" mais je bute.

Je t'en remercie par avance.

Private Sub Worksheet_Change(ByVal zz As Range)
On Error Resume Next
If Intersect(zz, [Nom,Organisme,Ville,BIC,Pays]) Is Nothing Then Exit Sub
Application.EnableEvents = False
zz = UCase(zz)
Application.EnableEvents = True

If Target.Column = 33 Then
If Target.Value Like String(23, "#") Then
Target.Value = Format(Target.Value, "@@@@ @@@@ @@@@ @@@@ @@@@ @@@")
End If
End If

End Sub

GALOUGALOU · 16 Juin 2020

bonsoir titesoeur, bonsoir le fil, bonsoir le forum
je vous propose une solution par formulaire, qui s'ouvre par un double clic dans une cellule de la colonne ag.
le nombre saisi se met en forme au fur et à mesure de la frappe.

VB:

Private Sub CommandButton1_Click()
ActiveCell = TextBox1.Text
Unload Me
End Sub

Private Sub TextBox1_Change()

     If TextBox1.TextLength = 4 Or TextBox1.TextLength = 9 Or TextBox1.TextLength = 14 Or TextBox1.TextLength = 19 Or TextBox1.TextLength = 24 Then TextBox1.Text = TextBox1 + " "

End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim VT As Integer
    TextBox1.MaxLength = 28
    Select Case KeyAscii
        Case 48 To 57
        Case Else
            KeyAscii = 0
            MsgBox "CARACTERE NON AUTORISE"
    End Select
End Sub

précision : le code de jmfmarques marche très bien, c'est le début de votre code qui plombe la macro qu'il vous a proposé.
cordialement
galougalou

Titesoeur · 16 Juin 2020

GALOUGALOU à dit:
bonsoir titesoeur, bonsoir le fil, bonsoir le forum
je vous propose une solution par formulaire, qui s'ouvre par un double clic dans une cellule de la colonne ag.
le nombre saisi se met en forme au fur et à mesure de la frappe.

VB:

Private Sub CommandButton1_Click() ActiveCell = TextBox1.Text Unload Me End Sub Private Sub TextBox1_Change() If TextBox1.TextLength = 4 Or TextBox1.TextLength = 9 Or TextBox1.TextLength = 14 Or TextBox1.TextLength = 19 Or TextBox1.TextLength = 24 Then TextBox1.Text = TextBox1 + " " End Sub Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Dim VT As Integer TextBox1.MaxLength = 28 Select Case KeyAscii Case 48 To 57 Case Else KeyAscii = 0 MsgBox "CARACTERE NON AUTORISE" End Select End Sub

précision : le code de jmfmarques marche très bien, c'est le début de votre code qui plombe la macro qu'il vous a proposé.
cordialement
galougalou

C'est nickel, sauf qu'il peut arriver qu'il y ait une lettre également. Et là ça ne fonctionne plus "CARACTERE NON AUTORISE"...
Merci beaucoup.

GALOUGALOU · 16 Juin 2020

re
mais si mais si, cela va fonctionner, il vous suffit de modifier le code
Private Sub TextBox1_KeyPress par le code ci-dessous

VB:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim VT As Integer
    TextBox1.MaxLength = 28
End Sub

cordialement
galougalou

Titesoeur · 16 Juin 2020

GALOUGALOU à dit:
re
mais si mais si, cela va fonctionner, il vous suffit de modifier le code
Private Sub TextBox1_KeyPress par le code ci-dessous

VB:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Dim VT As Integer TextBox1.MaxLength = 28 End Sub

cordialement
galougalou

Super ça fonctionne exactement comme je voulais.
En revanche, un petit problème se pose, c'est que mes formules de la colonne A ne restent pas...
Elles sont toutes simple en A2 j'ai 0 puis A3 j'ai la formule A2+1 et ainsi de suite...
Sauf que lorsque je tape la formule en A3, elle se calcule mais se "convertie" en valeur... La formule envolée...

Je sais j'abuse un peu de la bonté de tous Merciiiii

Titesoeur · 16 Juin 2020

Titesoeur à dit:
Super ça fonctionne exactement comme je voulais.
En revanche, un petit problème se pose, c'est que mes formules de la colonne A ne restent pas...
Elles sont toutes simple en A2 j'ai 0 puis A3 j'ai la formule A2+1 et ainsi de suite...
Sauf que lorsque je tape la formule en A3, elle se calcule mais se "convertie" en valeur... La formule envolée...

Je sais j'abuse un peu de la bonté de tous Merciiiii

Encore un petit abus... dsl
Toutes les colonnes se mettent en MAJUSCULES alors que j'ai une macro pour que certaines colonnes uniquement soit en majuscules et pas les autres. En regardant, j'ai vu que vous aviez "bloqué" la partie où je demandais certaines colonnes. Mais quand je la "débloque", il n'y a plus du tout de majuscule.
Je ne sais pas non plus comment intégrer dans cette même macro le fait que la colonne E soit formater en Nom Propre.

Dix millions de merci à tous pour votre aide.
Mon fichier sera pour ainsi dire parfait grâce à vous tous.

Format de cellule personnalisé

Titesoeur

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

jmfmarques

XLDnaute Accro

Simply

XLDnaute Occasionnel

Titesoeur

XLDnaute Nouveau

Titesoeur

XLDnaute Nouveau

Pièces jointes

jmfmarques

XLDnaute Accro

Titesoeur

XLDnaute Nouveau

GALOUGALOU

XLDnaute Accro

Pièces jointes

Titesoeur

XLDnaute Nouveau

GALOUGALOU

XLDnaute Accro

Titesoeur

XLDnaute Nouveau

Titesoeur

XLDnaute Nouveau

Discussions similaires