Formatierung / Anzeige von Zahlen

[Marco]

Es ist nichts großartiges; es verwundert mich nur.
Ich verwende meine alte EXCEL 2002 Version gerne als 'Datenbank'.

Und dabei fiel mir etwas auf, das ich nicht verstehe.
Wenn ich eine 18stellige Paket-Sendungsnummer in eine Zelle eingebe, die als Zahl, ohne Dezimalstellen aber mit 1000er Trennung formatiert ist, ersetzt EXCEL die letzten drei Ziffern durch Nullen.
So wird z. B. aus
340434177146631128 die Anzeige 340.434.177.146.631.000, statt 340.434.177.146.631.128, wie es von mir gedacht ist.

Auch wenn ich 340.434.177.146.631.128 als Text in eine Zelle schreibe und in einer zweiten Zelle mit dem benutzerdefinierten Format ###.###.###.###. ###.##0 mit WERT(Zelle) eine Zahl mache, ist die
Anzeige wiederum 340.434.177.146.631.000
 

Kennt einer der EX(cel)perten den Grund?

Danke von der MarCo

[shift-del]

Hallo Marco

Excel rechnet nur mit 15 Stellen Genauigkeit. Stellen die darüber hinaus gehen werden genullt.

[Marco]

Sorry, wie kommst du auf Stellen?
Mir geht es um eine Ganzzahl mit 18 Ziffern, nicht um eine Dezimalzahl mit 18 Stellen rechts vom Komma.

[shift-del]

Es gibt auch Stellen links vom Dezimalkomma.

[Gast 123]

Hallo Marco

ich will mich in das Wissen von Fennek nicht einmischen, der kennt sich bestimmt besser aus wie ich.  Aber schau mal hier

 18stellige Paket-Sendungsnummer

als altem Praktiker ist es mir völlig wurscht wie Excel rechnet, denn du rechnest bestimmt keine Paket Nummer zusammen, oder ???
Wenn Excel nicht so klappt wie ich will trickse ich Excel einfach aus!!  Paket Nummern würde ich die ganze Spalte alks Text formatieren.  
Dann interessiert Excel das umstellen von Zahlen nicht mehr!  Text ist einfach nur Text - Basta !!  

Als String kann die Zahl beliebig lang sein.  Ich bin halt ein praktisch denkender Mensch. 

mfg  Gast 123

[GMG-CC]

Dein Beitrag ist eine Punktlandung!
... Aber eher eine auf dem Bauch (=Bauchlandung), denn dem "alten Praktiker" vor Ort ist es nicht egal, wie das Ergebnis aussieht. "Punkt" ist hier nämlich das Stichwort.
Und es soll (doch offensichtlich) eine 18-stellige Ziffernfolge eingegeben und dann mit den trennenden Punkten dargestellt werden.
Das lässt sich direkt bei der Eingabe mit VBA lösen, in einer fertigen Liste u.U. auch mit Hilfsspalten / Power Query.

[Gast 123]

Hallo Marco

@Günther    danke für die Kritik mit der Bauchlandung, und dem Hinweis auf Vba.  Frage:  kann der Frager das Makro selbst entwickeln??

Anzei eine Beispieldatei mit Spalte D wo der Rang als Text formatiert ist.  Bei der Zahleneingabe gibt es dann kein Problem mehr.  Dann kommen die Vba "Heinzelmaennchen" und formatieren die Zahl wie gewünscht.  Die stammen übrigens aus Köln.
Stimmt die Laenge der Eingabe von 18 Stellen nicht kommt Fehlermeldung, und die Eingabe nicht angenommen.

Auch wenn ich 340.434.177.146.631.128 als Text in eine Zelle schreibe 

Das Excel dann macht was Excel will ist klar, denn Marco, das ist -keine gültige- Text Eingabe für Excel!!
Für Zahlen als Text must du am Anfang immer ein Apostroph davorseten  '340.434.177.146.631.128 

mfg  Gast 123

[GMG-CC]

@ Gast 123:
Aus Gründen der Daten-Hygiene öffne ich keine Makro-behafteten Dateien, darum kenne ich diene Lösung nicht. Sie wird aber ähnlich sein. 

@All:
Zum Vergleich poste ich hier einfach mal meinen Code:

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Lg As Variant, c As Variant, Block As Byte, Rc As String, Df As Integer
 
  'Nur in Spalte_A
  If Target.Column <> 1 Then Exit Sub
  'Fehlerbehandlung muss sein
  On Error GoTo ErrorHandler
  c = CStr(Trim(Target))
  Lg = Len(c)
  Application.EnableEvents = False 'Sonst Dauerschleife
  'Muss 18 Zeichen lang sein
  If Lg <> 18 Then
     Df = (18 - Lg) * -1
     MsgBox "Die Länge der Eingabe ist fehlerhaft." & vbCrLf _
     & "Differenz: " & IIf(Df > 0, "+", "") & Df & " Zeichen."
     GoTo ErrorHandler
  End If
  'Und nun punkten  ;-)
  For Block = 0 To 5
     Rc = Rc & Mid(c, Block * 3 + 1, 3) & "."
  Next Block
  'Letzten Punkt entfernen
  Target = Left(Rc, 23)

ErrorHandler:
  If Err.Number <> 0 Then MsgBox "Fehler-Nr.: " & Err.Number & vbCrLf & Err.Description
  Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  'Bei Klick in Spalte_A Zahlenformat erst einmal auf Text setzen
  If Target.Column = 1 Then Target.NumberFormat = "@"
End Sub

Hier ist Spalte_A für die Eingabe der Werte vorgesehen. Es braucht kein führendes Apostroph verwendet werden.
------------------------------
Es gibt aber auch eine reine Formel-Lösung, die hinhaut. Vorbedingung: Die Zelle/Spalte, wo die zu formatierenden Zahlen eingegeben werden, ist vorher als Text formatiert worden. (Dann kann auch das führende Auslassungszeichen entfallen). In F2 wird die Zahl eingegeben: 

Code:

=LINKS(F2;3)&"."&TEIL(F2;4;3)&"."&TEIL(F2;7;3)&"."&TEIL(F2;10;3)&"."&TEIL(F2;13;3)&"."&TEIL(F2;16;3)

Wie schon weiter oben erwähnt gibt es weitere Möglichkeiten ...

[Fennek]

Hallo,

auch wenn die Lösung von Günther perfekt ist, hier eine kürzere Alternative, die hoffentlich auch in schwierigen Fällen klappt:

Code:

Function Fen(ByVal rng As Range) As String
If Len(rng.Value) = 18 Then
   Fen = Format(--(Left(rng.Value, 9)), "0##,###,##0") & "." & _
       Format(--(Right(rng.Value, 9)), "0##,###,##0")
Else
   MsgBox "falsche Eingabe"
End If
End Function

Wenn der Text in A1 steht: in B1:=Fen(A1)
wie eine normle xl-Formel.

mfg

[Fennek]

Hallo,

wie wäre es so: (nur für A1, eine Schleife über die ganze Spalte müßte ergänzt werden)

Code:

Sub Fen()
for i = 16 to 3 step -3
   cells(1,1).Characters(i, 0).Insert(".")
next i
end sub

mfg