Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Zahlenformat

Betrifft: Zahlenformat von: Francesco Giardino
Geschrieben am: 25.09.2014 14:24:05

Hallo Miteinander,
ich habe ein Mittelgroßes Problem :)

Ich habe in Excel eine Liste erstellt die wir bei Bestandskorrekturen auf Arbeit verwenden.

Die Materialnummern (Zelle A1) haben folgendes Format 0000-000-0000-*(Anstelle von* kommt in Wirklichkeit ein ein Buchstabe von a-z) .

meine Frage ist es kann ich die Zelle so Formatieren dass ich die Zahl Quasi so 00000000000* eingebe und die Zahl in der Zelle aber 0000-000-0000-* so angezeigt wird ???

Über das Zellen formatieren geht es nicht, weil ich ja nicht immer den gleichen Buchstaben habe.

Hat mir jemand ein Tipp evtl. Über Vba ???
Vielen Dank im Voraus

  

Betrifft: mit benutzerdefinierten Zahlenformat ... von: neopa C (paneo)
Geschrieben am: 25.09.2014 14:36:54

Hallo Francesco ,


... so:

"0000-000-0000-"0;;
Gruß Werner
.. , - ...


  

Betrifft: das gilt natütülich nur für Zahlen ... aber von: neopa C (paneo)
Geschrieben am: 25.09.2014 14:41:28

Hallo nochmal,

... sollen für * auch Textwerte stehen, dann so:

"0000-000-0000-"0;;;"0000-000-0000-"@
Gruß Werner
.. , - ...


  

Betrifft: AW: das gilt natütülich nur für Zahlen ... aber von: SF
Geschrieben am: 25.09.2014 14:44:14

Hola,

hier wird auch darüber diskutiert:

http://ms-office-forum.net/forum/showthread.php?t=314241

Gruß,
steve1da


  

Betrifft: Aber da hat nur Hajo geantwortet, quasi die ... von: Luc:-?
Geschrieben am: 25.09.2014 15:11:19

…„geballte Kompetenz“, Steve,
und dann auch noch mit Standard-(Falsch-)Aussage… ;-]
Allerdings mögen wir hier kein CrossPosting, Chris,
zumindest dann nicht, wenn es nicht genannt und mit Link darauf verwiesen wird! :-|
Gruß, Luc :-?


  

Betrifft: Heute bissken 'tütülich', Werner? ;-] Gruß owT von: Luc:-?
Geschrieben am: 25.09.2014 15:12:35

:-?


  

Betrifft: meine Brille war vernebelt ... von: neopa C (paneo)
Geschrieben am: 25.09.2014 15:38:28

Hallo Luc,

... deshalb hatte ich zu erst nicht gelesen, dass er ja nur Textwerte eingeben will :-(
Und bei meiner Korrektur hätte ich natürlich den Teil für die Zahlenformatierung löschen können.
Aber ich verhindere so eine ungewollte Anzeige für (fehlerhafte) Eingaben von 0 und Minuswerten ;-)

Also alles noch im Toleranzbereich :-)

Gruß Werner
.. , - ...


  

Betrifft: Meine norddT SM sagte immer 'tüdelig' ... ;-) owT von: Luc:-?
Geschrieben am: 25.09.2014 15:41:15

:-?


  

Betrifft: Format-Tipp nicht nur über VBA von: Luc:-?
Geschrieben am: 25.09.2014 15:04:51

Hallo, Francesco;
MatNrn, die aus Ziffern und einem Buchstaben bestehen, sind Text, den man nur nur direkt (mit VBA) formatieren kann, d.h., die komplette Nr müsste fertig formatiert als Text ins Format eingetragen wdn:
0;;;"fertigformatierteNr"
Das kann man mit VBA zwar erledigen, wird aber nicht wirksam, wenn nur die Ziffern (als echte Zahl) in die Zelle eingetragen wdn.
Es geht ja wahrscheinlich nur um das Einsetzen der Striche im Format. Das fktioniert mit Xl-Format nur mit echten Zahlen. Deshalb mein Vorschlag: Ersetze jeden Buchstaben durch einen ggf 2stelligen ZahlenCode (bei Verwendung des ASCII-Code des Buchstabens müsste der bei KleinBst sogar 3stellig sein) und schließe den als Dezimalen an die Zahl an, dann kannst du wahrscheinlich sogar auf VBA verzichten, wenn du das spezielle, jetzt dezimalen­abhängige Format per BedingtFormatierung setzt. Dazu müssten nur die Dezimalen vgln wdn und das benutzer­definierte BedingtFormat entsprd eingestellt wdn. Dazu wird eine Regel pro möglichem Buchstaben benötigt, also wohl 26: 0000-000-0000-\§ (für § steht dann der jeweilge Bst aus [a,z])
Eine Regel (unter 6.MenüPkt angelegt) könnte so aussehen: =--RECHTS(A1;3)=0,01 → 0000-000-0000-\a
Das ginge dann bis =--RECHTS(A1;3)=0,26 → 0000-000-0000-\z
Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: AW: Zahlenformat von: Francesco Giardino
Geschrieben am: 25.09.2014 17:05:02

Hallo,
wenn ich die Zellen so Formatiere wie du geschrieben hast

Dann bekomme ich folgendes Ergebnis 0000-000-0000-414825635612a
der schreibt die ganze zahl im Buchstaben bereich auf.


verflixt, ich kann machen was ich will ich bekomme es nicht hin :(


  

Betrifft: Wen bzw welche Lösung meinst du? owT von: Luc:-?
Geschrieben am: 25.09.2014 17:12:59

:-?


  

Betrifft: Meine fktionierende wohl eher nicht! owT von: Luc:-?
Geschrieben am: 25.09.2014 17:14:14

:-?


  

Betrifft: oh, Mann, wie hattest Du doch Recht ... von: neopa C (paneo)
Geschrieben am: 25.09.2014 17:27:35

Hallo Luc,

... er meinte offensichtlich meinen Vorschlag. Und so sehe ich erst jetzt, was Francesco wirklich meinte :-(

Hallo Francesco,

... das was Du willst, ist mE nicht wirklich sinnvoll mit Zellenformat realisierbar. Ich würde mit einer Hilfsspalte arbeiten. In der wird eingegeben in der anderen das von Dir gewünschte mit einer Formel zum Anzeigen gebracht. Die Frage ist, ob Du das so willst.

Gruß Werner
.. , - ...


  

Betrifft: AW: Zahlenformat von: Francesco Giardino
Geschrieben am: 25.09.2014 17:19:27

@Luc
deine Lösung habe ich nicht probieren können, da ich leider nicht weiß wie ich dein Vorschlag umsetzen soll,
Der Andere Vorschlag war ja die Zellen formatierung, von neopa !
vielleicht kannst du mir dein Vorschlag den du hattest ein bisschen verständlich erklären, bin noch nicht lange an excel oder Vba dran, deshalb sind nur die Basics drin !

Vielen Dank für eure Super Tipps


  

Betrifft: in Deinem Beispiel hast Du 12 Ziffern ... von: neopa C (paneo)
Geschrieben am: 25.09.2014 17:33:54

Hallo Francesco,

... 17:05 hast Du "4148 256 3561 2a" geschrieben. Dein spez. Format sieht aber nur 11 Ziffern vor. Was gilt?

Gruß Werner
.. , - ...


  

Betrifft: AW: in Deinem Beispiel hast Du 12 Ziffern ... von: Francesco Giardino
Geschrieben am: 25.09.2014 17:37:04

Hallo Werner
ja ich habe mich vertippt, sorry das format sieht so aus 0000-000-0000-a
danke


  

Betrifft: und ... von: neopa C (paneo)
Geschrieben am: 25.09.2014 17:41:27

Hallo Francesco,

... reicht Dir eine Hilfsspaltenlösung? Und dann auch noch die Frage sind es wirklich immer 11 Ziffern oder können es weniger sein und die fehlenden sind vorn durch 0en zu ersetzen? Oder?


Gruß Werner
.. , - ...


  

Betrifft: AW: und ... von: Francesco Giardino
Geschrieben am: 25.09.2014 17:44:46

es sind immer 11 ziffern
ja die zahl sollte ich in A1 eingeben können und in A1 soll auch dann Quasi die Formatierte zahl erscheinen.
wenn das mit der Hilsfsaplte möglich ist, warum nicht, würde ich nehmen.

Danke


  

Betrifft: mit Hilfsspalte und Eingabeprüfung von: neopa C (paneo)
Geschrieben am: 25.09.2014 17:51:28

Hallo Francesco,

... Als Eingabehilfsspalte hab ich Spalte H gewählt und dort eine Eingabeprüfung vorgesehen.

Tabelle2

 ABCDEFGH
14148-256-3562-a      41482563562a

Formeln der Tabelle
ZelleFormel
A1=LINKS(H1;4)&"-"&TEIL(H1;5;3)&"-"&TEIL(H1;8;4)&"-"&RECHTS(H1;1)

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
H1Benutzerdefinidert =(ANZAHL(--LINKS(H1;SPALTE(A1:L1)))=11)*(LÄNGE(H1)=12)*ISTTEXT(RECHTS(H1;1)) 


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4



Gruß Werner
.. , - ...


  

Betrifft: Meine Lösung sieht so aus, ... von: Luc:-?
Geschrieben am: 25.09.2014 19:40:26

…Francesco
(hat etwas gedauert, weil ich dir alle 26 Regeln angelegt habe):

 ABC
1OriginalUmgewandeltFormatiert
201370471200a1370471200,010137-047-1200-a
368222298879b68222298879,026822-229-8879-b
468421626737c68421626737,036842-162-6737-c
544775548402d44775548402,044477-554-8402-d
671781499068e71781499068,057178-149-9068-e
742311372120f42311372120,064231-137-2120-f
860377361896g60377361896,076037-736-1896-g
981475387043h81475387043,088147-538-7043-h
1007504520667i7504520667,090750-452-0667-i
1164907942072j64907942072,106490-794-2072-j
1285317818770k85317818770,118531-781-8770-k
1327486233415l27486233415,122748-623-3415-l
1431159739538m31159739538,133115-973-9538-l
1574615062309n74615062309,147461-506-2309-n
1609910179527o9910179527,150991-017-9527-o
1761964762607p61964762607,166196-476-2607-p
1883523455572q83523455572,178352-345-5572-q
1907409839999r7409839999,180740-983-9999-r
2096974498148s96974498148,199697-449-8148-s
2147773486216t47773486216,204777-348-6216-t
2277338836991u77338836991,217733-883-6991-u
2300220329645v220329645,220022-032-9645-v
2425197983390w25197983390,232519-798-3390-w
2508334165809x8334165809,240833-416-5809-x
2694901223410y94901223410,259490-122-3410-y
2750046505839z50046505839,265004-650-5839-z

Dazu musst du aus deinen OriginalNrn mit EndBst erst mal echte Zahlen mit lfd BstNr als 2stellige Dezimale machen: =--(WECHSELN(A2;RECHTS(A2);"")&TEXT(CODE(RECHTS(A2))-96;"\,00")) im Bsp in Sp B
Das Ergebnis kannst du dann als Werte auf die OriginalDaten-Spalte (im Bsp Sp A) kopieren.
Deren ganzen Bereich (im Bsp Sp C) markierst du dann, klickst auf Bedingte Formatierung - Neue Regel, klickst auf den 6.(letzten) MenüPkt (Formel … verwenden) und legst dann die 26 Regeln an:
  1.Regel - Formel: =--RECHTS(TEXT(A2;"0,00");3)=0,01 bis …
26.Regel - Formel: =--RECHTS(TEXT(A2;"0,00");3)=0,26
Unter jeder Regel klickst du auf den Formatieren…-Button, wählst den Reiter Zahlen → letzten Pkt (Benutzerdefiniert) und trägst hier das Zahlenformat mit dem zugehörigen Endbuchstaben ein: 0000-000-0000-a bis 0000-000-0000-z
(Ab der 2.Regel kannst du das zuvor eingetragene Format aus dem Menü wählen und musst so nur noch den EndBst ändern.)
Zur Sicherheit habe ich dir auch noch die BspMappe hochgeladen.
Gruß, Luc :-?


  

Betrifft: AW: Meine Lösung sieht so aus, ... von: Ewald
Geschrieben am: 26.09.2014 15:09:03

Hallo,

mit Format geht da garnichts weil die Bezeichnung dann falsch ist. Der Wert bliebe immer noch

11111111111a,

ohne Hilfspalte und für alle Zellen in Spalte A (könnte angepaßt,aber auch nur für A1 gelten)

folgenden Code in das Tabellenmodul einfügen.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim a
Dim b
If Target.Column = 1 Then
    If Len(Target.Value) = 12 Then
        a = Format(Left(Target.Value, 11), "0000-000-0000")
        b = "-" & Right(Target.Value, 1)
    End If
    Application.EnableEvents = False
    Target.Value = a & b
    Application.EnableEvents = True
End If
End Sub
Dann steht die Bezeichnung auch richtig als Wert in der Zelle und du kannst damit weiterarbeiten.

Gruß Ewald


  

Betrifft: AW: Meine Lösung sieht so aus, ... von: Francesco Giardino
Geschrieben am: 26.09.2014 22:26:59

Hallo,
vielen Dank für die hilfe,
@ewald
deine Lösung ist genau dass was ich gesucht habe.
Vielen dank für den Script.
@Luc
deine Lösung ist aufwendig aber kommt letzt endlich nicht zu dem ergebnis den ich brauche.
den wenn ich alle formate von a-z habe müsste ich jedesmal wenn ich eine Nummer eingebe das zellenformat aussuchen,

Vielen dank für eure Mühen
grüße


  

Betrifft: Na, dann hast du nicht wirklich verstanden, ... von: Luc:-?
Geschrieben am: 28.09.2014 21:20:53

…wie die fktioniert, Francesco!
Du wolltest formatieren, indem für die 12. (Buchstaben-)Stelle ein * gesetzt wird. Das geht natürlich so nicht, es sei denn, du ersetzt den * durch eine lfdNr für den Buchstaben. Damit ergäbe sich eine Zahl, bei mir eine Dezimalzahl. Das kannst du in einer Hilfsspalte per Fml erledigen (habe ich alles angegeben), dann dort belassen oder deine OriginalNr mit dem Ergebnis überschreiben und dann wie von mir gezeigt formatieren.
Dass sich durch eine Formatierung der OriginalWert normalerweise nicht ändert, sollte ja wohl allen an diesem Thread Beteiligten klar sein. Die Frage ist nur, warum dann ursprünglich eine Formatierung verlangt wurde?! Ich ging davon aus, dass das nur einer besseren Lesbarkeit dienen soll, denn sonst könnte die MatNr ja gleich richtig angegeben wdn! Soll das Ganze aber nur der EingabeVereinfachung dienen, kämen noch andere Lösungen in betracht, aber das wurde ja nicht ausdrücklich erwähnt.
Kommt die MatNr aber in der gezeigten Form aus einer DBank, dann steht sie da auch so und man kann mit irgendeiner festen Umformung derselben dann nicht wieder in der DB nach ihr suchen. Ob es dann als einfacher betrachtet wird, nur die Striche wegzulassen oder die Dezimalen der Zahl (lt meiner Methode) in einen Buchstaben umzuwandeln, ist dann doch fast nur Geschmackssache.
Ich habe jedenfalls gezeigt, unter welchen Voraussetzungen und wie es hierbei möglich ist, die gewünschte Form per Formatierung zu erreichen, und damit die Frage so beantwortet wie sie ursprünglich gestellt wurde. (Nebenbei, die BedingtFormatierungen kann man ebenfalls mit einem VBA-Pgm erreichen!)
Ciao, Luc :-?


  

Betrifft: da müsste aber mE noch nachjustiert werden ... von: neopa C (paneo)
Geschrieben am: 28.09.2014 11:13:27

Hallo Ewald,

... in Deinem VBA-Vorschlag. Denn fehlerhafte Eingaben werden gnadenlos gelöscht und eine Eingabe wie z.B. 923456789x1a führt zu 923456789x1-a und wenn man dann das x nachträglich löscht, entsteht -falsch:"-0923-456-7891-a" und so weiter.

Da würde ich doch momentan noch meine einfache Hilfsspaltenlösung vorziehen.
Wobei ich die Formel in A1 nun so: =WENN(H1="";"";LINKS(H1;4)&"-"&TEIL(H1;5;3)&"-"&TEIL(H1;8;4)&"-"&RECHTS(H1;1)) schreiben würde und meine Eingabeprüfformel für H1 (bis H##) noch vereinfache auf:

=(ISTZAHL(--LINKS(H1;11))*(LÄNGE(H1)=12)*ISTTEXT(RECHTS(H1;1)))

Wenn man bei der Eingabe in H sich hier verschreibt, wird man darauf hingewiesen und korrigiert eben einfach, ohne dass man wieder von vorn beginnen muss.

Kann man natürlich mit VBA abfangen, aber warum sich unnötig Gedanken machen, wenn es auch ohne geht;-)

Gruß Werner
.. , - ...


  

Betrifft: AW: da müsste aber mE noch nachjustiert werden ... von: Ewald
Geschrieben am: 28.09.2014 14:54:26

Hallo Werner,

so ganz verstehe ich deinen Einwand nicht, es sollen Materialnummern eingegeben werden und keine beliebige Zeichenfolge. Dies besteht immer aus 11 Ziffern und einem Buchstaben.

wird in der Ziffernfolge ein oder mehrere Buchstaben eingegeben sorgt Format dafür das nicht formatiert wird und der ganze Block wird wiedergegeben.

333333333x44-g

du siehst direkt das was falsch ist.

ersetzt du jetzt nur das x durch eine Ziffer und entfernst nicht das - ist die Eingabe total falsch, da die Länge überschritten ist.

Da die Länge überschritten wurde, ist a (1.Block) und b = "" und damit wird auch nichts zurückgegeben.

ersetzt du das x durch eine Ziffer und entfernst das - ist die Länge gegeben und es wird richtig wiedergegeben. (Vorgabe ist ja Eingabe 11 Ziffern und ein Buchstabe)

Es ist also alles im Code enthalten.

Natürlich kann man auch für jede Falscheingabe eine Meldung ausgeben, nur dann muß gesagt werden was dann passieren soll. Darüber gibt es aber keine Info und der Fragesteller scheint damit ja auch zufrieden zu sein.

Gruß Ewald


  

Betrifft: es ging/geht mir um Tippfehler, ... von: neopa C (paneo)
Geschrieben am: 28.09.2014 15:33:36

Hallo Ewald,

... die mit dem aktuellen Makro etwas aufwendig korrigierbar sind.
Sicherlich war das"x" in meinem Beispiel nicht besonders glücklich gewählt. Besser wäre womöglich anstellen dessen ein "O" gewesen. Denn ein "O" hat schon mancher für eine 0 eingetippt.

In dem Fall muss dann, wie Du richtig selbst aussagst, nicht nur das "O" durch eine 0 ersetzt werden, sondern zuvor auch das "-" vor dem letzten Zeichen löschen. Noch aufwendiger wird es, wenn (aus Versehen) mehr als 12 Zeichen oder genau 12 Ziffern eingeben wurden oder aber es muss (aus welchen Grund auch immer) nachträglich nur ein Zeichen korrigiert werden (dann müssen zuvor auch alle "-" gelöscht werden).

Wenn dass alles dem Fragesteller nicht stört, nun gut. Ich schrieb ja auch nur: "Da würde ich doch momentan noch meine einfache Hilfsspaltenlösung vorziehen".

Gruß Werner
.. , - ...


  

Betrifft: AW: es ging/geht mir um Tippfehler, ... von: Ewald
Geschrieben am: 28.09.2014 18:07:26

Hallo Werner,

bin ja garnicht so, damit auch Dus­se­lig­keit unterstützt wird.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim a
Dim b
If Target.Column = 1 Then
    If Target.Count > 1 Then Exit Sub
    If Len(Target.Value) > 12 Then
        MsgBox "Falsche Eingabe" & Chr(13) & "Siehaben " & Len(Target.Value) & " Zeichen  _
eigegeben, es sind nur 12 erlaubt"
        Exit Sub
    End If
    If Not IsNumeric(Mid(Target.Value, 1, 11)) Then
       MsgBox "Falsche Eingabe" & Chr(13) & "Die Stellen 1 bis 11 dürfen nur Ziffern enthalten"
       Exit Sub
    End If
    If Asc(Right(Target.Value, 1)) < 97 Or Asc(Right(Target.Value, 1)) > 122 Then
       MsgBox "Falsche Eingabe" & Chr(13) & "Die 12. Stelle muß ein Kleinbuchstabe sein, sie  _
haben  " & Right(Target.Value, 1) & "  eingegeben"
       Exit Sub
    End If
    If Len(Target.Value) = 12 Then
        a = Format(Left(Target.Value, 11), "0000-000-0000")
        b = "-" & Right(Target.Value, 1)
    End If
    Application.EnableEvents = False
    Target.Value = a & b
    Application.EnableEvents = True
End If
End Sub
Gruß Ewald


  

Betrifft: AW: es ging/geht mir um Tippfehler, ... von: Francesco Giardino
Geschrieben am: 03.10.2014 13:55:18

Hallo Ewald,
Danke für deine Lösungen funktionieren beide sehr gut, die erste finde ich sogar besser :)
Ich hätte noch eine kurze frage , wenn wir das dokument abspeichern und beispielsweise zelle e5 ist aktiviert , wenn wir die Tabelle wieder öffnen ist ja die Zelle e5 logisch aktiv.
Ist es möglich das man sagt, nach speichern bitte Zelle auf Spalte A ohne wert aktivieren?
So dass wenn man die Tabelle öffnet nur die materialnummer eingeben muss und nicht erst die leere zeile snklicken muss??

Ist jetzt doch ne längere Frage geworden :)
Danke im Voraus


 

Beiträge aus den Excel-Beispielen zum Thema "Zahlenformat"