Excel bietet standardmäßig eine Vielzahl an Möglichkeiten, um Zahlen in der gewünschten Weise zu formatieren, wie z.B. als Währung, Datum, Prozent und so weiter.
Manchmal reichen die Standardformate aber nicht aus, weil man z.B. eigene Einheiten an seine Zahlen anhängen möchte. Oder weil man besonders lange Zahlen zwecks besserer Lesbarkeit optisch trennen möchte.
Für diese Fälle gibt es in Excel eine einfache Lösung: Benutzerdefinierte Zahlenformate. Damit sind der Fantasie (fast) keine Grenzen gesetzt, sein Datenmaterial im gewünschten Format darzustellen.
Und so geht’s:
Mit dem Standard geht’s los
Die verschiedenen Zahlenformate erreicht man entweder über die entsprechende Auswahlliste im Start-Register oder über einen Rechtsklick auf eine Zelle und den Menüpunkt „Zelle formatieren…“
Zahlenformate auswählen
Damit gelangt man zum Fenster mit den verschiedenen Standard-Kategorieren für Zahlenformate:
Zahlenformate nach Kategorie
Um nun ein eigenes Zahlenformat zu definieren empfiehlt es sich, zunächst aus den vorhandenen Kategorien ein möglichst ähnliches Format auszuwählen. Denn in vielen Fällen kann ein bestehendes Zahlenformat als Ausgangsbasis für die Anpassungen dienen. Im Bild oben habe ich als Beispiel ein Währungsformat ausgewählt, das ich im nächsten Schritt anpassen möchte.
Zahlenformat anpassen
Das Euro-Zeichen wird standardmäßig hinter die Zahl angehängt, soll in meinem Beispiel aber vorangestellt werden. Dazu wähle ich nun die Kategorie „Benutzerdefiniert“ aus:
Anpassung des Währungsformats
Wie man sieht, werden im Feld „Typ“ die Formatierungszeichen angezeigt, die ich nun ändern kann. Dazu klicke ich einfach in diese Zeile und stelle das Eurozeichen an die erste Stelle:
Geändertes Währungsformat
Und schon ist das gewünschte Ergebnis erreicht, wie man in Zelle A2 sieht:
Vorangestelltes Währungszeichen
Genauso gut hätte ich auch eine völlig neue Währung definieren können. In diesem Fall hänge ich die Währungsangabe einfach in doppelte Anführungszeichen hinten an die Formatierungszeichen an:
Eigene Währung definieren
Und schon haben wir eine Alternative für den Euro geschaffen!
Jetzt kommt die Extrawurst
Damit ist aber noch lange nicht das Ende erreicht, denn die benutzerdefinierten Formate bieten noch viel mehr Möglichkeiten. So kann ich z.B. für positive und negative Zahlen unterschiedliche Farben festlegen und einen Hinweis ausgeben, wenn es sich bei der Eingabe um keine Zahl handelt:
Verwendung von Farben
Dieses Format wollen wir uns zum besseren Verständnis etwas näher ansehen:
Aufbau des Zahlenformats
Die Formatierungszeile ist in 4 Bereiche unterteilt, die ich zur besseren Unterscheidung farbig markiert habe. Dabei werden die Bereiche in genau dieser Reihenfolge definiert: positive Werte, negative Werte, Null und Texte. Die einzelnen Bereiche werden durch ein Semikolon voneinander getrennt.
Die Farbbezeichnungen müssen dabei jeweils in eckigen Klammern vorangestellt werden. Folgende Farbangaben können verwendet werden: Schwarz, Grün, Weiß, Blau, Magenta, Gelb, Zyan und Rot.
Die folgende Beispieltabelle zeigt, was noch alles mit benutzerdefinierten Formaten möglich ist:
Weitere benutzerdefinierten Zahlenformate
Auf einige Besonderheiten möchte ich noch kurz eingehen:
Texte sind immer in doppelte Anführungszeichen zu setzen, wie wir schon in unserem Währungsbeispiel gesehen haben.
Das Rautensymbol # steht immer für eine Zahl, wobei nicht signifikatente Nullen weggelassen werden. Wenn man die Nullen auf jeden Fall anzeigen möchte, gibt man das Zeichen „0“ an.
Man kann auch mit Bedingungen arbeiten, die dann in eckige Klammern gesetzt werden. Dies ist bei der Unterscheidung von Einzahl und Mehrzahl ganz hilfreich: [=1] wird somit für den Wert 1 (also die Einzahl) angewendet [<>1] für alle Werte, die ungleich 1 sind, also die Mehrzahl.
Über das Stern-Symbol wird das nachfolgende Zeichen solange wiederholt, bis die komplette Zelle gefüllt ist.
Das könnte dich auch interessieren:
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Super erklärt! Und wie kann ich nun den in Anführungszeichen angegebenen Text am Ende der Formel als das erscheinen lassen, als das er normalerweise erscheinen würde (in diesem Fall ein SVERWEIS)?
Meine Formel lautet: ##.#0;-##.#0;[weiß]##.#0; und jetzt?
Hallo Irmtraud,
Du musst nur dieses benutzerdefinierte Format auf die Zelle mit dem SVERWEIS anwenden, dann sollte es schon greifen. Also Zelle markieren, Rechtsklick, Zellen formatieren, Format auswählen…
Schöne Grüße,
Martin
Hallo Martin
Danke für diese Erklärungen. Ich habe eine Frage, die zu diesem Thema passt: ich möchte in Excel in einer bestimmten Zelle jeweils eine Zahl von 1 bis 12 eingeben und die Formatierung dieser Zelle so machen, dass es mir dann den jeweiligen Monatsnahmen hinschreibt. Also wenn ich in Zelle A1 die Zahl 5 eintippe und ich dann ENTER drücke, soll in der Zelle A1 Mai stehen.
Ich möchte das nicht über eine Formel machen, die sich auf die Zelle A1 bezieht (also so im Sinn von in der Zelle A2 die Formel =DATUM(2016;A1;1) hinschreiben und dann die Zelle A2 benutzerdefiniert mit MMMM formatieren. Das Ziel wäre dass das alles in A1 passiert. Geht so eine Formatierung? Danke im Voraus für die Hilfe.
Hallo Thomas,
das lässt sich mit einer reinen benutzerdefinierten Formatierung nicht lösen. Ich fürchte, Du wirst um die Formellösung nicht herumkommen.
Schöne Grüße,
Martin
Formel wäre hier aber ungünstig, weil sie nicht im Feld des Eintrags zum tragen kommt.
In diesem Fall hülfe ein Makro, was bei der Eingabe der Zahl in die Zelle anspringt.
So geht’s:
– Mit Alt + F11 den VBA-Editor öffnen
– in der Baumstruktur links das gewünschte Tabellenblatt anklicken
– rechts ins (weiße) Editorfeld folgenden Code eingeben:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case Target.Value
Case 1: Target.Value = „Januar“
Case 2: Target.Value = „Februar“
Case 3: Target.Value = „März“
Case 4: Target.Value = „April“
Case 5: Target.Value = „Mai“
Case 6: Target.Value = „Juni“
Case 7: Target.Value = „Juli“
Case 8: Target.Value = „August“
Case 9: Target.Value = „September“
Case 10: Target.Value = „Oktober“
Case 11: Target.Value = „November“
Case 12: Target.Value = „Dezember“
Case Else: Exit Sub
End Select
End If
End Sub
Sobald in irgendeine Zelle der Spalte A (Target.Column = 1) eine Zahl zwischen 1 und 12 eingegeben wird, wird sie durch den Monatsnamen ersetzt.
Alle anderen Eingaben in Spalte A (Case Else) bleiben unverändert.
Hallo Klexy,
richtig, mit VBA gibt’s natürlich auch elegante Lösungen. Danke dafür!
Schöne Grüße,
Martin
Hallo Thomas,
wenn es nur um die Formatierung (also geänderte Anzeige) des Monatsnamens geht, kannst Du das über eine bedingte Formatierung mit 12 Regeln realisieren.
Beispiel für Zelle A1:
-> bedingte Formatierung
-> neue Regel
-> Formel zur Ermittlung der zu formatierenden Zellen verwenden
Formel: =A1=1
Formatierung: Zahlen: Benutzerdefiniert: Typ: „Januar“
-> neue Regel
Formel: =A1=2
Formatierung: Zahlen: Benutzerdefiniert: Typ: „Februar“
.
.
-> neue Regel
Formel: =A1=12
Formatierung: Zahlen: Benutzerdefiniert: Typ: „Dezember“
Wenn nach dem Drücken von Enter wirklich „Januar“ anstatt „1“ als Wert in der Zelle stehen soll, bleibt nur die VBA-Lösung von Klexy.
Gruß
Stefan
hallo martin,
beim importieren einer csv-datei zerschießt mir excel immer eine spalte, in der nummern einer liste drin sind, like:
0.1
0.2
1.0
1.1.0
1.1.1.0
gibt es dafür auch eine sinnvolle lösung?
danke und mfg
Hallo Rene.
Die Dateiendung von csv in txt ändern (lässt sich dann natürlich nicht mehr einfach durch Doppelklick öffnen).
Die txt-Datei in Excel öffnen und beim Import im Textkonvertierungs-Assistenten:
Schritt 1 von 3: „Getrennt“ wählen
Schritt 2 von 3: „Semikolon“ wählen
Schritt 3 von 3: Für die fragliche(n) Spalte(n) das Datenformat „Text“ wählen.
Nicht besonders elegant, sollte aber funktonieren.
Gruß
Stefan
Hallo,
ich möchte in einer Tabelle negative Werte FETT anzeigen lassen. Gibt es da eine Möglichkeit?
Wie ich die ´Schriftfarbe ändere bei negativen zahlen weiß ich aber zusätzlich in Fett wäre auch super 🙂
Danke schonmal für die Hilfe
Hallo Ramona.
Das lässt sich über die „normale“ Wertformatierung nicht mehr realisieren. Die „Extrawurst“ funktioniert nur bei der Schriftfarbe. Um sonstige Formatierungen (Schriftart, Hintergrundfarbe, Zellrahmen etc.) abhängig von Werten (oder anderen Bedingungen) zu ändern, muss man Regeln mittels der bedingten Formatierung für die Zelle(n) anlegen.
Gruß
Stefan
Hallo, ich hätte folgende Frage:
Ich habe ein Feld in dem nur 4 Eingaben möglich sind: m, w, männlich, weiblich (das habe ich mit einer Gültigkeitsprüfung gelöst).
Nun muss ich die Zelle aber so formatieren, dass wenn der Benutzer m eingibt männlich angezeigt wird, bzw. wenn der Benutzer w eingibt weiblich angezeigt wird.
Leider komme ich nicht auf die richtige Lösung
Hallo Lisa,
der einzige Weg, der mir spontan einfällt, wäre die Autokorrektur. In den Excel-Option unter „Dokumentenprüfung“ lässt sich das einstellen, dass ein „m“ eben durch „männlich“ ersetzt wird.
Vielleicht hilft das ja weiter.
Schöne Grüße,
Martin
habe eine dringende Bitte. es gibt kaum Erklärungen zum Platzhalter @ in benutzerdefinierten zahlenformaten bzw. wenn man es nach einer der spärlichen anleitungen macht, funktioniert es nicht. wofür brauche ich den genau? es gibt auch anscheinend platzhalter wie _ , die nirgendwo erklärt werden. bin für jede hilfe dankbar!
und gleich noch eine Frage: wenn man auf Einfügen /Pivot Table geht, erscheint am Tabellenblatt ein Rechteck mit dem Text „PivotTable1 Wählen Sie zum Erstellen eines Berichts die Felder aus der PivotTable-Feldliste aus“ mit eine Grafik darin. Kann man dieses Rechteck samt Inhalt löschen, wenn man es doch nicht sehen will am Blatt, wann ja, wie??? vielen Dank
Hallo Laura,
das Feld für die Pivot-Tabelle bekommt man am leichtesten so weg:
Das war’s.
Schöne Grüße,
Martin
Hallo Laura,
das @-Zeichen ist der Platzhalter für Text. Wenn er im benutzerdefinierten Format angegeben wird, bleibt der Text in der Eingabezeile auch stehen. Wird das @-Zeichen weggelassen und gibt man nur einen fixen Text an, bleibt nur dieser fixe Text in der Eingabezelle stehen. Eigene Eingaben werden nicht angezeigt.
Beispiel:
Detaillierte Infos zu den Platzhaltern gibt es auch auf der Support-Seite von Microsoft.
Schöne Grüße,
Martin
danke suuper das funktioniert und ist erst mal logisch. aber ich kann ja auch ohne das @ Text in die Zelle eintragen. Welchen Sinn hat also überhaupt folgende @-Setzung? 0;-0;0;@ nur mit 0;-0;0 funktioniert es ja genauso.
sorry wenn ich jetzt noch mal nerve, aber es muss sein, diese benutzerdefinierten Zahlenformate treiben mich in den Wahnsinn und ich habe außer der einen Antwort von Ihnen noch immer keine ordentliche Anleitung mit sinnvollen und nützlichen Beispielen gefunden, das von microsoft ist unverständlich und ohne gute Beispiele.
Habe jetzt folgendes Format erstellt. : 0;-0;“keine“;@ „Erdäpfel“
da sollte es doch statt Null immer „keine“ schreiben, tut es aber nicht, es schreibt trotzdem 0. Was habe ich falsch gemacht ???!!!??? bin für jeden Hinweis dankbar.
Hallo Laura,
ich habe es gerade getestet und bei mir bringt dieses Format anstelle der Null auch „keine“. Steht in Ihrer Zelle tatsächlich eine glatte Null oder ist es das Ergebnis einer Berechnung, bei der es vielleicht noch einige Nachkommastellen nach der Null gibt? Ansonsten weiß ich es auch nicht…
Schöne Grüße,
Martin
danke für die Mühe. habe es nochmal getestet. wenn ich nur 0 eintippe, schreibt es „keine“. Wenn ich auch noch Text dazu schreibe, springt es aber wieder auf 0 um. Glatte Null, kein Ergebnis einer Berechnung.
Hallo Martin,
ich hoffe Du kannst mir helfen.
Mein Problem:
in einer Form habe ich eine Textbox in der ich einen Kilometerstand eingebe. Dieser soll in einer Zelle (Excel 2007) übernommen werden, die schon benutzerdefiniert #.##0″ KM“ ist. In der betreffenden Zell erfolgt jedoch nur linksbündig eine Zahl z.B. 45000 ohne weitere Formatierung. Auch eine nachträgliche Formatierung bringt keinen Erfolg. Trotz wochenlangem googeln habe ich keine brauchbare Lösung gefunden.
Kennst Du vielleicht eine Möglichkeit?
MfG
Dieter Smets
Hallo Dieter,
ich vermute, Du musst bei Deiner Suche in Deiner Form ansetzen oder ggf. im VBA-Code, der dahinterliegt. Dort ist vermutlich irgendwo festgelegt, dass Text geschrieben wird.
Schöne Grüße,
Martin
Hallo, vielen Dank für den Beitrag und für die Mühe das zu erstellen.
Jedoch habe ich eine Frage:
Ich möchte die Zellen so formatieren, dass folgende Anordnung besteht:
0-170420-AA
Am Anfang darf nur eine Zahl eingetragen werden ( optimal wärs wenn man nur eine 0 oder eine 1 eintragen könnte)
der Bindestrich sollte immer bestehend angezeigt werden
danach folgen 6 weitere Zahlen ( bestenfall sollen zur zahlen eingetragen werden)
der zweite Bindestrich sollte ebenfalls bestehen
zuletzt sollen zwei Buchstaben eingetragen werden können ( bestenfalls nur buchstaben und nur zwei)
ist das irgendwie umsetzbar ohne ein Makro?
LG
Andreas
Hallo Andreas,
ein benutzerdefiniertes Format funktioniert hier nicht. Wenn ich es richtig verstehe, möchtest Du eigentlich eine Eingabeprüfung.
Richte also für die betroffenen Eingabezellen folgende Datenüberprüfung ein (Menü Daten | Datenüberprüfung):
Zulassen: Benutzerdefiniert
Formel: =UND(ODER(LINKS(A1;1)=“0″;LINKS(A1;1)=“1″);TEIL(A1;2;1)=“-„;TEIL(A1;9;1)=“-„;LÄNGE(A1)=11;NICHT(ISTFEHLER(WERT(TEIL(A1;3;6))));NICHT(ISTZAHL(WERT(RECHTS(A1;2)))))
Statt A1 gibst Du die jeweilige Eingabezelle an. Die Formel akzeptiert dann nur noch Eingaben, die folgendem Muster entsprechen:
– erstes Zeichen 0 oder 1
– Bindestrich an der 3. und 9. Stelle
– Gesamtlänge muss 11 sein
– die 6 mittleren Stellen müssen eine Zahl sein
– die beiden letzten Stellen dürfen keine Zahl sein
Schöne Grüße,
Martin
Ich bin kein Mensch vieler Worte aber ihre waren wirklich die hilfreichsten die ich im Netz gefunden habe, und dafür bedanke ich mich aufrichtig. Leider gibt es zuviele Köche im Internet die nur Sinnlosen Brei von sich geben, umso mehr weiss ich die Professionalität ihrer Arbeitsweise zu schätzen. Alles Gute machen sie bitte weiter so.
mit Freundlichen Grüßen
Robert Müller
Hallo Herr Müller,
vielen Dank für dieses schöne Feedback. Genau so etwas motiviert ungemein dazu, auch zukünftig hier mein Bestes zu geben.
Schöne Grüße,
Martin
Hallo Herr Weiß,
ich schließe mich an – sehr gut erklärt, vielen Dank!
Meine spezielles Format-Problem: ich muss sehr große und sehr kleine Zahlen in einer Tabelle darstellen und möchte die Lesbarkeit (und Vergleichbarkeit) erhöhen. Es sollen alle (=beliebig viele) Nachkommastellen in Dreiergruppen (Bsp. 0,000 000 007) dargestellt werden, große Zahlen mit Trennpunkt (Bsp. 123.000.000.000).
Gibt es dafür eine Format-Lösung?
Danke vorab & viele Grüße, pickUP
Hallo pickUp,
solch ein Zahlenformat könnte so aussehen:
#.##0,### ### ### ### ###
Damit werden vor dem Komma wenn nötig Tausendertrennzeichen gesetzt. Nach dem Komma werden die Zahlen in maximal 5 Dreiergruppen dargestellt. Das ist meines Wissens auch die maximale Genauigkeit von Excel (15 Stellen), mehr dürfte also ohnehin nicht gehen.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für Deine Antwort – fast perfekt! Die großen Zahlen werden jetzt allerdings alle mit Komma dargestellt (16.000, ).
Mir fehlt quasi das Zeichen für „optionales Komma“. Hast Du so etwas ist der Trickkiste? 😉
Viele Grüße,
pickUP
PS: Ein weiterer Effekt, der mir auch vorher schon mal aufgefallen ist (auch wenn man in der Menüleiste die „000“ für Tausendertrennzeichen anwählt): die Zelleinträge bekommen eine Abstand zur rechten Zellenbegrenzung. Aber das ist im Grunde nur ein „Schönheitsfehler“, oder eben Geschmackssache.
Hallo pickUp,
ich vermute, für ein optionales Komma gibt es keine Lösung. Du könntest höchstens noch immer eine Stelle nach dem Komma anzeigen lassen, dann steht das Komma nicht so verloren da:
#.##0,0## ### ### ### ###
Und der rechte Abstand kommt durch die 4 Leerzeichen im Formatcode zustande, auch dafür kenne ich leider keine Lösung. Aber vielleicht hat ein anderer Leser ja noch einen Tipp.
Schöne Grüße,
Martin
Super erklärt- hat mir direkt geholfen ohne große weitere Suche. Lieben Dank
Hallo Chris,
gern geschehen.
Schöne Grüße,
Martin
Hallo Herr Weiß,
da Sie das hier so super erklären, stelle ich Ihnen auch mal eine Frage:
wenn ich Spalten mit Zahlen von Excel nach InDesign kopiere, erscheinen sie in InDesign so wie sie in Excel formatiert waren, z.B. Hochkomma als Tausendertrennzeichen, Punkt als Dezimaltrennzeichen.
Jetzt möchte ich aber über die InDesign-Funktion „Datei – Platzieren…“ eine Datei einfügen mit solcherart formatierten Zahlen, weil ich da auch die Tabellenrahmen mit einstellen kann, bei Kopieren geht das nicht. Dummerweise wird das Format in InDesign geändert in Punkte als Tausendertrennzeichen, Komma als Dezimaltrennzeichen. Ich finde nirgendwo in InDesign, wo ich das einstellen kann. Aber sei’s drum, hier geht es ja um Excel und da ist meine Frage:
wie kann ich benutzerdefinierte Zellenformatierungen so einstellen, dass Hochkommata als Tausendertrennzeichen kommen und Punkte als Dezimaltrennzeichen. #’##0.00 funktioniert nicht. So wird es zwar angezeigt, wenn ich über „Datei – Optionen – Erweitert“ den Haken rausmache bei „Trennzeichen vom Betriebssystem übernehmen“ und bei Dezimaltrennzeichen „.“ und bei Tausendertrennzeichen „’“ eingebe. Selbst dann wird es in InDesign anders formatiert. Würde ich über „Region und Sprache – Formate“ „Deutsch (Schweiz)“ eingeben würde es eventuell auf meinem Rechner funktionieren (ich habe es nicht ausprobiert), aber das InDesign-Dokument soll später auf einem anderen Rechner geöffnet werden. Das mit dem Hochkomma bekomme ich noch hin, aber wie lautet der Befehl „mach für ein Dezimaltrennzeichen einen Punkt!“ im benutzerdefinierten Format. Die Einstellung Punkt und Komma soll bleiben.
Vielen Dank für Ihre Bemühungen, aber da ich selbst nach ziemlich langer Suche nichts gefunden habe, gehe ich davon aus, dass Sie das auch nicht rausfinden werden :‘-( Schönen Tag noch!
Viele Grüße
-martin fydrich-
Hallo Herr Weiß,
manchmal hilft es, sein Problem einfach nochmal deutlich zu beschreiben. Wenn ich die Region für mein ganzes Windows auf „Deutsch (Schweiz)“ einstelle und die Datei in InDesign platziere, kommt sie da natürlich richtig. Aber wenn ich es ganz normal abspeichere, verändert es sich nicht mehr. Auch auf anderen Rechnern nicht. Juhu!
(Trotzdem wäre es schön zu wissen, wie ich einen benutzerdefinierten Punkt als Dezimaltrennzeichen hinbekomme.)
Mit freundlichen Grüßen
-martin fydrich-
Hallo Herr Fydrich,
freut mich, dass Sie das Problem noch lösen konnten.
Was ein benutzerdefiniertes Dezimaltrennzeichen angeht, muss ich allerdings passen. Mir ist zumindest nicht bekannt, dass so etwas möglich ist.
Schöne Grüße,
Martin
Hallo Hr. Weiß!
In MS Excel 2016 kann man in den Excel-Optionen und dem Menüpunkt „Erweitert“ das Dezimaltrennzeichen definieren. Standardmäßig ist hier der Punkt „Trennzeichen vom Betriebsystem übernehmen“ angehakt.
LG
Klaus
Hallo Herr Zagorz,
vielen Dank für den Tipp, das war mir bisher völlig entgangen.
Schöne Grüße,
Martin
Hallo,
ich habe ein Diagramm, bei dem die Beschriftung der Y-Achse aus Zahlenwerten mit zwei Nachkommastellen besteht.
Dies stelle ich in meinem Excel 2016 ein, indem ich die Y-Achse mit der rechten Maustaste auswähle und dann
– Achse formatieren auswähle und dann im Menü rechts „Zahl“ das entsprechende Zahlenformat auswähle.
Ich möchte jedoch hinter den Zahlenwerten ein VARIABLES Währungssymbol setzen.
Das Währungssymbol steht als Text in der Zelle K392 auf einem anderen Tebellenblatt namens „progress“.
Ich hatte gehofft, dass ich den Formatcode nun ganz einfach wie oben beschrieben mit dem nachfolgenden Zusatz ändern kann :
#.##0,00 & progress!K392
Das wäre aber offensichtlich zu einfach.
Egal, was ich probiere und mit „Hinzufügen“ bestätige – das Währungszeichen taucht nicht im Diagramm auf.
Gibt es dafür eine Lösung?
Vielen Dank für alle, die sich meinen Kopf zerbrechen
Danke – hat sich gerade erledigt.
Ich habe das Problem per VBA gelöst.
Hallo Roy,
mit normalen Mitteln (also ohne VBA) wäre das vermutlich auch nicht zu lösen gewesen.
Schöne Grüße,
Martin
Schöne Beispiele, gut erklärt!
Ich benötige folgende Extrawurst, die ich benutzerdefiniert nicht hinbekomme.
Die Einheit für Volumnia ist m³ (m + ALT0179) und läßt sich so auch als benutzerdefiniertes Zellenformat eingeben.
Die Einheit für Trägheitsmomente ist m4. Wie kann ich die 4 als benutzerdefiniertes Zellenformat schreiben dass bei Eingabe eines
Wertes von 3,75 in der Zelle 3,75 m4 mit hochgestellter 4 angezeigt wird.
Im voraus vielen Dank.
Hallo Rudi,
eine hoch gestellte 4 lässt sich meines Wissens in Excel nicht als benutzerdefiniertes Zahlenformat darstellen.
Das geht nur über eine manuelle Formatierung direkt in der Zelle: Die „4“ mit der Maus markieren – Rechtsklick – Zellen formatieren – Schrifteffekt: Hochgestgellt
Schöne Grüße,
Martin
Guten Tag Herr Weiss. Hier in der Schweiz haben wir die Tausender-Trennzeichen, also Format 1’234.56.
Wenn ich z.B. aus Deutschland eine Tabelle erhalte, sind die Zahlen 1,234.56 formatiert. Diese Zahlen kann ich dann nur mit der Autosumme addieren, wenn ich bei jeder Tausenderzahl das Komma von Hand lösche.
Gibt es eine einfachere Methode, die betreffenden Zellen alle zusammen anzupassen?
Schöne Grüsse aus der Schweiz
Nicolo Paganini
Hallo Herr Paganini,
mich wundert es ein wenig, dass Excel das Zahlenformat nicht selbst korrekt interpretiert. Allerdings irritiert es mich auch, dass Sie aus Deutschland das Zahlenformat 1,234.56 erhalten, denn üblich wäre eigentlich 1.234,56
Im Zweifelsfall könnten Sie aber immer die Suchen&Ersetzen-Funktion nutzen:
Suchen Sie nach dem Komma und lassen Sie das Feld „Ersetzen durch“ einfach leer. Dann sollte das lokal auf Ihrem Rechner eingestellte Zahlenformat greifen.
Schöne Grüße,
Martin
Hallo,
ich würde gerne eine bestimmte Teilenummer in ein passendes Format bringen, schaffe das aber bis jetzt nicht.
Beispiel einer Teilenummer wäre 4K0131962Q und ich will, dass sie folgendermaßen formatieren: 4K0 131 962 Q.
Da auch immer Buchstaben in der Teilenummer vorhanden sind, funktioniert das benutzerdefinierte Zahlenformat nicht.
Kann man dies irgendwie hinbekommen?
Grüße
Oliver
Hallo Oliver,
ich fürchte, das wird sich mit benutzerdefinierten Formaten nicht lösen lassen. Da bleibt wohl nur die Option einer Hilfsspalte, in der du mit den Text-Funktionen die Teilenummer in das gewünschte Format bringst. Also etwa so:
=LINKS(A1;3)&“ „&TEIL(A1;4;3)&“ „&TEIL(A1;7;3)&“ „&RECHTS(A1;1)
(es handelt sich oben immer um doppelte Anführungszeichen; die werden hier leider nicht korrekt dargestellt)
Schöne Grüße,
Martin
Hallo Herr Weiß,
vielen Dank für die tolle und interessante Extrawurst-Anleitung!
Sie hatten in einer Antwort schon geschrieben, dass dies nur mit den Farben geht, aber ich wollte dennoch einmal nachfragen, ob Sie eine Möglichkeit über die Wert-Formatierung sehen, die Zahlen vor dem Komma in einer größeren Schriftgröße (z.B. Schriftgröße 12) und die Zahlen nach dem Komma in einer kleineren Schriftgröße (z.B. 10 oder 8) darzustellen?
Gibt es hierfür eventuelle andere Mittel? Vielen Dank!
Hallo Alex,
unterschiedliche Schriftgrößen für Vor- und Nachkommastellen lassen sich weder über die Zahlenformate noch über eine bedingte Formatierung darstellen. Wenn überhaupt, müsste hier mit VBA-Programmierung gearbeitet werden. Mit normalen Excel-Bordmitteln ist das jedoch nicht möglich.
Schöne Grüße,
Martin
Hallo Herr Weiss,
Sie haben bestimmt sofort eine brauchbare Antwort für mich? Ich finde einfach kein Format (ich bin kein Excel Freak) für die Zahl 0012.3456.
Was ich auch ausprobiere, die Nullen vorne sind immer weg…
Ich brauche das für einen SVerweis mir einer Vielzahl von Zahlen, die jetzt als Text vorliegen. So geht SVerweis natürlich nicht..
Vielen Dank
Hallo Herr Blumentritt,
wenn die Zahlen, die sie vergleichen wollen, als Text vorliegen, dann müssen Sie sie auch als Text formatieren. Dann bleiben auch die führenden Nullen erhalten.
Schöne Grüße,
Martin
Sehr hilfreiche Erläuterungen. Vielen Dank hierfür. Mich drückt aber der Schuh an anderer Stelle:
Mir schwebt vor, eine lange Kette von Ziffern (keine Zahl) zu erfassen und nach dem Return die Darstellung der Zeichenkette nach jeweils vier Ziffern von einem Leerzeichen unterbrechen zu lassen (z. B. „0123 4567 8901 2345 6789“). Zur Erläuterung: Der String besteht ausschließlich aus Ziffern, damit bei der Erfassung der Ziffernblock nicht verlassen werden muss. Die optische Einfügung der Leerzeichen die Zeichenkette „lesefreundlicher“ gestalten.
Dafür habe ich das benutzerdefinierte Format ####“ „####“ „####“ „####“ „#### gewählt. Dies hat jedoch zur Folge, dass der Text als Zahl interpretiert wird und somit die letzten vier Ziffern jeweils mit einer „0“ dargestellt werden.
Wie sieht eine Lösung aus?
Vielen Dank im voraus!
Hallo Joachim,
das beschriebene Problem lässt sich mit einem benutzerdefinierten Format leider nicht lösen. Eine Alternative könnte sein, die Dateneingabe als Text in einer Zelle vorzunehmen und dann diese Eingabe in der Spalte daneben per Textformeln in das gewünschte Anzeigeformat bringen.
Also beispielsweise so:
=LINKS(A1;4)&“ „&TEIL(A1;5;4)&“ „&TEIL(A1;9;4)&“ „&TEIL(A1;13;4)&“ „&TEIL(A1;17;4)
Nicht optimal, aber eine bessere Lösung fällt mir nicht ein.
Schöne Grüße,
Martin
Hallo,
ich habe ein anderes Problem – und noch keine Lösung gefunden.
Das Format kommt so an: 6,72905E+12. Ich habe es schon benutzerdefiniert umformatiert in: 6729053715704.
Aber: die letzte Ziffer muss noch weg (das ist eine Prüfziffer, die mit ankommt – in meinem System aber nicht hinterlegt ist – daher kann das System diese Zelle nicht zuordnen).
Wie bekomme ich diese eine letzte Ziffer „wegformatiert“ ?
Vielen Dank.
Hallo Silke,
mit wegformatieren geht hier leider nichts. Du musst die letzte Stelle zum Beispiel per Formel abschneiden. Angenommen, die Zahl steht in A1:
=WERT(LINKS(A1;LÄNGE(A1)-1))
Damit bist du hinsichtlich der Länge der Zahl flexibel und es wird immer nur die letzte Stelle abgeschnitten. Wenn die Zahl jedoch beispielsweise immer 13stellig ist, geht es ein wenig kürzer:
=WERT(LINKS(A1;12))
Hinweis: Die umschließende WERT-Funktion ist notwendig, damit das Ergebnis auch wieder als Zahl erkannt wird. Ansonsten wird durch die LINKS-Funktion ein Text, der nur wie eine Zahl aussieht.
Schöne Grüße,
Martin
Moin,
Alternativ wäre auch Teilen und Abrunden möglich.
=ABRUNDEN(A1/10;0)
Dann bleibt es bei einer Zahl.
Gruß M.T-S (Marcel)
Hallo Marcel,
richtig, ABRUNDEN wäre natürlich auch eine gute Alternative 🙂
Viele Grüße,
Martin
Hallo Martin,
leider komme ich mit meinen Kenntnissen und dem, was im WWW (nicht) beschrieben wird, am Ende und bitte um Hilfe.
Unter Bezugnahme auf eine Zelle, in der als Berechnungsergebnis z.B. 100 Stunden im Format [hh]:mm korrekt angezeigt werden, möchte ich an anderer Stelle den Gegenwert der 100 Stunden in Minuten anzeigen lassen. Das Format [mm] greift auch soweit, aber ich hätte die Anzeige gerne mit Tausendertrennzeichen.
Viele Grüße
Hans-Jürgen
Hallo Martin,
ich habe eigene Formate wie m, m2, m3, etc. und hätte diese gerne im Menuband um einfacher darauf zugreifen zukönnen.
Eine Idee wie man die in das Menüband in das Register Zahl bekommt 🙂
Gruß
Peter
Hallo Peter,
da sieht es schlecht aus. Eigene benutzerdefinierte Formate bekommt man nicht ins Menüband oder in die Schnellzugriffsleiste – zumindest ist mir kein Weg bekannt.
Der schnellste Weg wäre vermutlich noch über die Tastenkombination Strg+1. Damit wird zumindest schon mal das Auswahlfenster mit den Zahlenformaten aufgerufen.
Schöne Grüße,
Martin
Hallo,
ich würde gerne auf das Format [ZEICHEN/ZEICHEN/LEERZEICHEN/ZAHL/ZAHL/PUNKT/ZAHL] prüfen.
Also Beispiel KX 30.1
Wobei KX, das Leerzeichen und der Punkt fest steht. Die „30“ und die „1“ im Beispiel sind variabel/ändern sich.
Wie lautet die Syntax für die Datenprüfung/Zellenformatierung? @;@;@;0;0@;0 ?
Grüsse
Hallo Olli,
mit einem benutzerdefinierten Zahlenformat geht das nicht. Aber in einer Datenprüfung könntest du folgende Formel anwenden (im Beispiel für Zelle A1):
=UND(LINKS(A1;3)=“KX „;ISTZAHL(WERT(TEIL(A1;4;2)));TEIL(A1;6;1)=“.“;ISTZAHL(WERT(RECHTS(A1;1)));LÄNGE(A1)=7)
Schöne Grüße,
Martin
Hey Martin,
vielen dank für die Erklärung, aber nun zu meiner Frage.
Kann ich benutzerdefinierte Formate auch als Symbol auf die Menüleiste setzten, wenn ja wie geht das?
Vielen lieben Dank schon einmal im Voraus.
LG
Sandra
Hallo Sandra,
die Frage hatte ich weiter oben schon mal beantwortet: Nein, benutzerdefinierte Formate lassen sich leider nicht als Symbol in die Menüleiste übernehmen.
Schöne Grüße,
Martin
Hallo zusammen,
Wenn ich eine WENN/DANN Formel habe, kann ich durch Benutzerdefinierte Formate irgendwie kenntlich machen, mit welcher Wenn/Dann Bedingung das Zelle Ergbnis Zustande gekommen ist?
Also mit anderen Worte, kann ich das irgendwie so formatieren, das z.B. ein „A“ an das Ende des Wertes angefügt wird, wenn das Ergebnisse das Produkt aus der WENN Bedingung ist und ein „B“, wenn das Ergebnis ein Produkt aus der DANN Bedingung ist?
Somit könnte ich über die bedingte Formatierung die Tabelle nach A & B durchsuchen und unterschiedlich einfärben zu lassen. Somit hätte man sofort einen Überblick, welches Ergebnis aufgrund welcher Bedingung zu Stande gekommen ist.
geht so etwas? Vielen Dank für die Antwort.
Hallo JB,
ich bin mir nicht sicher, ob ich die Frage richtig verstanden habe. Wenn es schon eine WENN-Formel in der Zelle gibt, warum hängst du dann dort nicht auch gleich das „A“ oder „B“ an den Wert an? Mit einem benutzerdefinierten Zahlenformat lässt sich das leider nicht abbilden.
Schöne Grüße,
Martin
Hallo Martin,
Ich möchte folgendes Format verwenden: ####-####-####-####
Eingabe: 1224-5678-1234-5678
aber dann ist die
Ausgabe: 1234-5678-1234-5670
Egal welche Zahlen genommen werden, die letzte Zahl wird immer auf Null (0) gesetzt.
Wie kann man dies beheben? Die IBAN-Nummer kann auch nicht dargestellt werden oder doch?
Vielleicht gibt es ja einen Trick? oder Excel 2016 einen Buck? oder ?
Der-vor dem-Computer-sitzt
Horst
Hallo Horst,
das Problem liegt darin, dass es sich um ein Zahlenformat handelt und Excel keine Zahlen darstellen kann, die länger als 15 Stellen sind. Einfach mal ohne dieses Format eintippen und oben in die Bearbeitungszeile schauen: Alles was nach der 15. Stelle kommt, wird durch eine Null ersetzt.
Das ist eine „natürliche“ Grenze in Excel und da kenne ich auch keinen Trick, wie man das umgehen könnte (und somit lassen sich auch keine IBAN-Nummern entsprechend formatieren.
Schöne Grüße,
Martin
Hallo Martin,
interessanter Beitrag.
Ich tüfftel gerade um, wie ich wohl in eine mit Prozent formatierte Zelle noch einen Text bekomme.
Das sollte so aussehen:
0,00 % TEXT
Kannst du mir helfen?
Danke
Mel
Hallo Mel,
wenn es ein statischer Text sein soll, dann erstellst du folgendes benutzerdefiniertes Zahlenformat:
0% „TEXT“
(oder was auch immer als Text da stehen soll)
Alternativ kannst du einfach die TEXT-Funktion nutzen. Angenommen, der Prozentwert steht in A1, dann gib ein eine andere Zelle ein
=TEXT(A1;“0%“)&“ TEXT“
Und da sind deiner Fantasie keine Grenzen gesetzt.
Schöne Grüße,
Martin