Wie kann ich in Excel ein benutzerdefiniertes Format verwenden, um Punkte (als Trennzeichen) in einen 18-stelligen Eintrag einzufügen?

Question

Es handelt sich um 18-stellige Abrechnungscodes, die mehrere Punkte als Trennzeichen enthalten. Ich möchte nur die Ziffern eingeben, ohne die Punkte einzutippen. Beispiel:

112233333444555556

würde automatisch formatieren als

11.22.33333.444.55555.6

Ich habe diese Formel verwendet:

??"."??"."?????"."???"."?????"."?

das funktioniert, außer dass die letzte Ziffer immer 0 ist

Wie muss dieser Code geändert werden, damit im letzten Feld jede beliebige Ziffer eingegeben werden kann?

Answer 1

Hier ist ein VBA-Code, der Spalte A überwacht und die Punkte hinzufügt, wenn der Texteintrag 18 Zeichen hat. Wählen Sie die gesamte Spalte aus und formatieren Sie sie als Text. Anschließend können Sie den 18-stelligen Code eingeben oder einfügen.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    If Len(Target) = 18 Then
        s = Left(Target, 2) & "." & _
        Mid(Target, 3, 2) & "." & _
        Mid(Target, 5, 5) & "." & _
        Mid(Target, 10, 3) & "." & _
        Mid(Target, 13, 5) & "." & _
        Right(Target, 1)
        Target.Value = s
    End If
Application.EnableEvents = True
End If

End Sub

Answer 2

Wie von @ erwähntAbonnierenExcel folgt der IEEE 754-Spezifikation für Gleitkommazahlen und kann nur 15 signifikante Ziffern speichern. Die einfachste Problemumgehung ohne Makros besteht darin, die Formel in zwei Teile aufzuteilen (die 15 Ziffern ganz links und die verbleibenden Ziffern ganz rechts).

=TEXT(
   LEFT([input], 15), format_left
 ) & 
 TEXT(
   RIGHT([input], LEN([input]) - 15), format_right
 )

dh

= TEXT(LEFT(A2,15),"00-00-00000-000-000") & TEXT(RIGHT(A2,3),"00-0")

| Account number     | Account number (formatted) |
| ------------------ | -------------------------- |
| 112233333444555556 | 11-22-33333-444-55555-6    |