Eine Liebeserklärung an MS Excel
Erstaunlich – man kann eine Zahl mit mehreren Dezimalstellen mit einem Tausendertrennzeichen im Zahlenformat trennen, beispielsweise (für Deutschland und Österreich)
#.##0oder (für die USA)
#,##0oder für die Schweiz
#'##0Allerdings versagt das Leerzeichen:
# ##0Es funktioniert erst dann, wenn man in den Optionen / Erweitert das Leerzeichen als Tausendertrennzeichen definiert und dann die Zellen als Zahl mit Tausendertrennzeichen festlegt.
Warum machen die das?
Ich entdecke die Seite
https://de.wikipedia.org/wiki/Liste_der_britischen_Premierminister
Ich greife mit Powerquery auf die Seite zu und lasse mir die Namen und die Datumsangaben (absteigend sortiert) zurückgeben:
Mit der Funktion TEXTTEILEN will ich die Datumsangaben trennen:
=TEXTTEILEN(Die_Liste_der_Premierminister__seit_1721__Bearbeiten___Quelltext_bearbeiten[@Amtsantritt];" ")und bin über das Ergebnis verblüfft:
Tag und Monat werden nicht getrennt. Ich ahne es. Ich ermittle den Code des dritten Zeichens der Zelle C2:
=CODE(TEIL(C2;3;1))Das Ergebnis lautet 160. Anders dagegen das zweite Leerzeichen, also Zeichen Nummer 8:
=CODE(TEIL(C2;8;1))Hier lautet das Ergebnis 32, also Leerzeichen, während es sich beim ersten Blank um ein geschütztes Leerzeichen handelt. Warum das? Soll beim Verschieben der Seite Tag und Monat nicht getrennt werden? Was vielleicht auf der Internetseite „schön“ aussieht, erweist sich als Ärgernis bei der Weiterverarbeitung der Daten. Oder will wikipedia nicht, dass man seine Daten weiter benutzt?
Übrigens: Wählt man in PowerQuery den Befehl „Spalte teilen“, so schlägt PowerQuery #(00A0) als Trennzeichen vor …
Hallo Herr Martin,
warum findet Excel Costa Rica nicht? Es gibt Costa Rica!
Genauer: ich habe mit einer Suchformel
=INDEX($F$2:$F$114;VERGLEICH(A9;$G$2:$G$114;0))einen Wert gesucht. Alle Werte werden gefunden – nur nicht Costa Rica:
Zuerst vermutete ich ein Leerzeichen hinter einer der beiden Wörter. Die Schreibweise ist korrekt. Dann vermute ich das Leerzeichen als Übeltäter. Und so ist es auch: die Funktion
=CODE(TEIL(G23;6;1))liefert den Wert 160 (geschütztes Leerzeichen) und nicht 32 (Leerzeichen), wie erwartet:
Auf mein Nachfragen erfahre ich: „… ich habe die Liste doch aus wikipedia kopiert. Und ich habe sie explizit als Wert eingefügt – OHNE Formatierung!“ Das genügt leider nicht … wikipedia verwendet an einigen Stellen geschützte Leerzeichen und bedingte Trennstriche. Das macht das Weiterverrbeiten solcher Listen in Excel manchmal mühsam. Böses Excel …
Excel-VBA-Schulung. Wir programmieren eine eigene Funktion. Als Übung gebe ich auf eine Funktion zu schreiben, welche die Hypotenuse in einem rechtwinkligen Dreieck berechnet.
Also:
Ich tippe die Lösung, die ich mit einem „Quick & Dirty“ kommentiere:
Ich verlasse die Codezeile:
Ein Fehler ist die Folge. Darf man nicht die Funktion Sqr (Wurzel) aufrufen und im Aufruf rechnen?
Okay – dann „sauberer“ in zwei Zeilen:
c = a^2 + b^2in der Funktion
Function Hypotenuse(a As Double, b As Double) As Double
Dim c As Double
c = a^2 + b^2
Hypotenuse = Sqr(c)
End Functionfalsch. Es dämmert mir. Richtig: das Caret-Zeichen (^) – der Zirkumflex – darf nicht direkt hinter die Variable geschrieben werden, da a^ zum Erstellen von Long Long-Datentypen in einer 64-Bit-Umgebung verwendet wird. Man muss ein Leerzeichen zwischen Variable und dem „Dach“ ein Leerzeichen schreiben:
Also:
Function Hypotenuse(a As Double, b As Double) As Double
Dim c As Double
c = a ^ 2 + b ^ 2
Hypotenuse = Sqr(c)
End Functionoder in der Kurzform:
Function Hypotenuse(a As Double, b As Double) As Double
Hypotenuse = Sqr(a ^ 2 + b ^ 2)
End FunctionDas funktioniert!
VBA-Schulung. Eine Teilnehmerin sieht ihren Fehler nicht und bittet mich zu helfen. Ich schaue mir den Code an:
Böse Leerzeichen! Der Fehler ist schnell gefunden: Am Ende des Tabellenblattnamens hatte sie aus Versehen ein Leerzeichen geschrieben, also statt Set xlTabelle = xlDatei.Sheets(„Almodovar“)
Set xlTabelle = xlDatei.Sheets("Almodovar ")Erstaunlich. Das ist mir noch nie aufgefallen. War das schon immer?
Erstellt man in Excel eine Datenüberprüfung mit einer Liste und trägt die einzelnen Werte in das Textfeld als Quelle ein
kann man die Texte gefolgt von einem Leerzeichen eingeben:
Verwendet man dagegen als Liste die Werte einiger Zellen, die ausgewählt werden:
kann man die Texte NICHT gefolgt von einem Leerzeichen eintragen. SO sollte es doch eigentlich sein, oder?
Fast wäre ich reingefallen. Folge Mail erreichte mich:
Hallo René
Du hast mir kürzlich mit dem SVerweis geholfen.
Nun habe ich wiederum ein kurze Frage:
Im Anhang die Angaben von Personen. Ich möchte diese aufteilen in Anrede (Mr.), Vorname und Nachname.
Wie geht das?
Herzliche Grüsse
Andreas
Das kann doch kein Problem sein, dachte ich klickte auf den Assistenten „Daten / Text in Spalten“. Doch der belehrte mich eines besseren:
Ich schreib zurück:
Hallo Andreas,
die Funktion
=CODE(TEIL(A2;4;1))
liefert 160. Das ist der ASCII-Code des „Leerzeichens“ zwischen „Mr.“ und „Rüdiger“. Das Leerzeichen hat Code 32.
Woher hast du die Daten? Aus dem Internet? Aus Word? Von einem fremden System? DAS hat kein Mensch getippt!
Antwort: Wir brauchen Leerzeichen!
Markiere das Teilchen zwischen „Mr.“ und „Rüdiger“. Kopiere es.
Dann rufe den Ersetzen-Dialog auf (Strg + H).
Ersetze (Strg + V) durch Leerzeichen.
Das Ergebnis sieht aus wie vorher.
Dann kannst du die Spalte A markieren und mit dem Assistenten Daten / Text in Spalten trennen. Voilà.
Klappt das?
Liebe Grüße
René
Hoi René
Perfekt! Hat super geklappt!
LG Andreas
Hallo Rene,
sei gegrüßt in den Abendstunden.
Ich hoffe, dass es dir soweit gut geht und dein EXCEL-Kurs für Einsteiger bald abgehalten werden kann.
Mit dieser Mail kommt nur eine fachliche Frage zu EXCEL:
Ich realisiere auf einer Zelle ein Dropdown mit einer Datenauswahl bzw. Überprüfung.
Als Datengrundlage nutze ich ein x und ein Leerzeichen und möchte auf die Verwndung eines Namens verzichten.
D.h.: Im WWW finde ich eine Lösung (http://www.office-loesung.de/ftopic332490_0_0_asc.php) über die Schritte:
Einstellungen / Listung / Quelle
den Vorschlag:
‚; x
Gibt es noch andere Lösungen?
Dankeschön & Gruß Jürgen
Hallo Jürgen
willst du wirklich als Auswahl ein Leerzeichen und ein x haben?
Ich habe so etwas schon gemacht – nur mit x. Wenn der Anwender es nicht haben will, kann man es löschen (Taste [entf]).
Wie auf office-loesung beschrieben – elegant ist das ‚ nicht gerade.
Alternative: Namen – das willst du nicht.
Alternative: Wähle zwei Zellen aus. Im unteren steht ein „x“, die obere Zelle ist leer. Oder mit einem Leerzeichen gefüllt.
Liebe Grüße
Rene
Irritierend, verwirrend und nicht konsequent!
In Excelschulungen erzähle ich immer, wenn ich den Assistenten „Daten / Text in Spalten“ vorstelle, dass es in Excel keine Tabulatoren gibt – man kann die Grundeinstellung (die beim Import von Textdateien verwendet wird) ruhig stehenlassen:
Ich werde eines Besseren belehrt: Wenn Sie ein Worddokument oder einen Mailtext aus Outlook nach Excel kopieren, werden die Tabstopps durch Spalten getrennt:
Wenn Sie dagegen Texte IN Zellen kopieren (das heißt: die Zelle mit [F2] editieren) und dann den kopierten Text einfügen, stehen Tabstopps in Zellen. Merkwürdigerweise sieht man sie nicht, wenn der Cursor AUF der Zelle steht – lediglich IN der Zelle werden sie angezeigt:
Der Assistent „Daten / Text in Spalten“ interpretiert die Tabstopps als Leerzeichen!?!
Die Funktion CODE liefert den Wert 32 – den gleichen wie Leerzeichen!?!
Wenn man in den Ersetzen-Dialog einen Tabstopp hineinkopiert und durch ein Leerzeichen ersetzen lässt, funktioniert dies hervorragend.
Fazit: Finger weg von Tabstopps in Exceltabellen! Excel behandelt diese Zeichen nicht konsequent als eigene Zeichen – eine Unterscheidung durch Leerzeichen ist nicht an allen Stellen durchgeführt. Und wenn ein Anwender Texte in die Zellen eingefügt hat – dann bleibt nur noch der Ersetzen-Assistent oder die WECHSELN-Funktion.
Danke an die Einladung zum Excelstammtisch in Hessen. Auch in Frankfurt – wie bei uns in München – eine geballte Ladung an Wissen, Freude über Excel, Staunen und Spaß.
Klasse war es.
Danke auch an Andreas Thehos, der sich mit dem gleichen Problem rumgeschlagen hat wie ich gestern. Nur: die Aufgabenstellung war eine andere.
Gegeben sei eine Namensliste. Sie ist Basis für eine Pivottabelle. Es erstaunt, dass ein Name zwei Mal auftaucht. Und nein: es sind keine Leerzeichen hinter dem Namen.
Die Ursache: auch hier hat sich ein geschütztes Leerzeichen (ein non-breaking space) mit dem CODE 160 zwischen den Vornamen und Nachnamen geschummelt.
Wie passiert so etwas? Entweder wurden die Daten aus einer Internetseite entnommen – oder von einem Word-Dokument. DORT kann man mit der Tastenkombination [Umschalt] + [Strg] + [Leertaste].
Erstaunlich. Ich kopiere eine Liste von einer Internetseite. Und möchte die Informationen einer Spalte trennen. Ich verwende den Assistenten Daten / Text in Spalten und bin verblüfft, dass die Texte nicht (am Leerzeichen) getrennt werden:
Eine Analyse mit der Funktion
=CODE(TEIL(A2;LÄNGE(„Argentinien“)+1;1))
liefert das Ergebnis, dass es sich nicht um ein Leerzeichen (32), sondern um ein geschütztes Leerzeichen (160) handelt. Es entspricht dem (non-breaking space). Zum Glück kann man es aus dem Text kopieren und daran trennen:
Schon doof: ich füge in Zellen eine Datenüberprüfung ein – zugelassen sind nur die beiden Werte intern;extern.
Leider ist es möglich „intern “ einzugeben – das heißt: hinter den Text Leerzeichen einzufügen. Das ist doch wohl schlecht, oder?
Schon perfide: Ich verbinde zwei Zellen miteinander („verbinden und zentrieren“). Ich speichere die Datei unter dem Namen „RenesTest.xlsx“. Ich verweise von einer anderen Datei auf die verbunden Zelle(n) und erhalte als Bezug:
=[RenesTest.xlsx]Tabelle1!$B$5
Ich speichere die Datei unter dem Namen „Test Rene.xlsx“. Erneut ein Bezug auf die verbundenen Zellen. Nun erhalte ich:
='[Test Rene.xlsx]Tabelle1′!$B$5:$C$5
was nach Bestätigung mit dem Fehler #WERT! quittiert wird. Erstaunlich, dass bei einem Leerzeichen im Dateiname der Bezug auf die verbunden Zellen aufgelöst wird, während bei einem Dateinamen ohne Leerzeichen der Bezug auf eine Zelle erfolgt!
Fazit: Besser nicht „verbinden und zentrieren!“ Das bringt nur Ärger!
Heute in der Excelschulung. Ich zeige den Teilnehmer das „Leerzeichenproblem“: Manchmal geben Anwender und Anwenderinnen am Ende eines Textes Leerzeichen ein, das man nicht sichtbar machen kann. Das ist fatal beim Sortieren, bei Pivottabellen, die Funktionen WENN, ZÄHLENWENN, SUMMEWENN rechnen vermeintlich falsch … Man kann solche Leerzeichen mit der Funktion RECHTS ausfindig machen, man kann sie mit GLÄTTEN entfernen, … All das zeige ich. Weise darauf hin, dass man (zugegeben: sehr schlecht!) die Leerzeichen sichtbar machen kann, wenn man die Texte rechtsbündig formatiert:
Eine Teilnehmerin meldet sich und fragt, warum bei ihr die Texte am Ende KEINE Lücke aufweisen, obwohl sie ein Leerzeichen eingefügt hat:
Die Antwort: Wenn am Ende eines Textes sich ein Leerzeichen befindet, wird es unterdrückt, wenn ein Textumbruch eingeschaltet ist !?!
Heute in der Excel-Schulung. Ein Teilnehmer rief mich und fragte mich, warum die Zeichen „+“ und „-“ nicht untereinander stehen:
Die Antwort können Sie sich sicherlich denken: Hinter den Pluszeichen (die nach unten kopiert wurden) befand sich ein Leerzeichen …
Letzte Woche in der libreOffice-Calc-Schulung.
Wir üben die Technik des Ziehens und erstellen einen Kalender.
Ein Teilnehmer sagt, dass es bei ihm nicht funktioniere. Ich ahne, was er falsch gemacht hat.
Richtig: er hat nicht „Montag“ in die Zelle eingetragen, sondern „Montag “ – also mit einem Leerzeichen hinter dem Wochentag.
Schnell nachgeschaut: Excel übergeht freundlicherweise diesen Eingabefehler. Und schreibt die weiteren Wochentage ohne Leerzeichen in die Tabelle. Ist hat doch besser als Calc.
Noch ein hübscher Fehler aus der heutigen VBA-Schulung.
Warum liefert folgende Codezeile einen Fehler:
Set xlBlatt = xlDatei.Worksheets(„Filme“)
Nun – die Teilnehmerin hat das Blatt nicht „Filme“ genannt, sondern “ Filme“ – also vor den Buchstaben „F“ ein Leerzeichen eingegeben …
… und ich finde die Ursache …
Heute in der VBA-Schulung. Ein Teilnehmer fragt mich, wo der Fehler in der folgenden Codezeile ist:
ActiveWorkbook.Worksheets(„Filme“).Activate
warum?
Nein – ActiveWorkbook ist richtig – es war keine andere Datei offen. Jedoch hatte der Teilnehmer das Tabellenblatt „Filme “ benannt – mit einem Leerzeichen Ende …
Böses Leerzeichen!
Können Sie mir helfen? Ich dachte, ich hätte es gefunden. In den Optionen in Excel gibt es den Befehl „Steuerzeichen anzeigen“. Ich dachte, dass man damit die Leerzeichen (und Zeilenschaltungen) anzeigen lassen kann. Aber Pustekuchen – geht wohl doch nicht. Oder mache ich etwas falsch?
Steuerzeichen ?!?
Nicht ganz. Normalerweise ist diese Option in Excel ausgeblendet.
Normalerweise so.
Sie sehen sie nur, wenn Sie mit Schriften arbeiten, die von rechts nach links laufen: Arabisch, Hebräisch, Persisch und andere. Dann wird der Cursor angezeigt – mit einem Haken, der verdeutlicht, dass nun die Textlaufrichtung von rechts nach links eingestellt wurde. Die Leerzeichen kann man auch in Excel 2016 leider noch immer nicht sichtbar machen.
Steuerzeichen !
Hallo. Ich habe schon eine ganze Weile gesucht, bis ich herausgefunden habe, warum er die gallischen Tiere falsch zählt. Eigentlich müsste die Formel ANZAHL2 die Zahl 2 ergeben und nicht 4:
ANZAHL2 zählt falsch
Die Antwort habe ich nach langem Suchen gefunden: In einigen Zellen habe ich aus Versehen ein Leerzeichen eingegeben. Klar – das sehe ich nicht; das wird als Text mitgezählt.
Die Wurzel des Übels
Aber nun meine Frage: Wenn ich die Liste filtere – warum zeigt der Autofilter nicht an, dass einige Zellen Leerzeichen enthalten?
Die Antwort: Ja – Sie haben recht – der Autofilter übergeht zum Glück (oder leider?) die Leerzeichen. Der Vorteil: „Asterix“ und „Asterix “ (mit einem Leerzeichen am Ende) werden vom Filter als gleicher Text behandelt. Der Nachteil: Der Filter hilft nicht diese Leerzeichen, die an anderen Stellen Probleme verursachen, aufzufinden.
Der Autofilter übergeht Leerzeichen am Ende des Textes.
Man kann die Texte mit Suchen ([Strg]+[F]) auffinden. Oder mit Funktionen:
=LÄNGE(C2)
=WENN(LINKS(C2;1)=“ „;“x“;““)
Oder mit [Strg]+[↓] können Sie den Cursor nach unten versetzen; er springt nun zur ersten Zelle, in der etwas steht; stoppt also auch bei den Zellen, die mit einem Leerzeichen gefüllt sind.
Warum zählt die Funktion ZÄHLENWENN (und auch SUMMEWENN) manchmal falsch?
Zählt ZÄHLENWENN falsch?
Die Antwort auf diese Frage findet man, wenn man die Spalte rechtsbündig formatiert. Dann sieht man, dass „China“ beim zweiten Mal mit einem Leerzeichen eingegeben wurde.
Ein Leerzeichen am Ende bewirkt einen anderen Text.
Sehr geehrter Herrn Martin,
In der Excel Tabelle die im Anhang beigefügt ist, bekommen wir Daten von unsere EDV (sehe Sheet 1 vor Verarbeitung). Ich gehe dann in „DATA“ und „Text to Columns“ und spalte diese Tabelle so dass sie nach diese Schritte wie im 2. Sheet (Daten nach Verarbeitung) aussieht.
Das Problem liegt indem einige Zahlen immer noch so erscheinen „1 150,000“ und keine weitere Formatierung möglich ist. Da ich auch eine Summe daraus ziehen möchte.
Wie kann man diese Problem Lösen ?
Originaldaten
Daten nach dem Trennen
Die Antwort: Dummerweise liefert Ihr System die Spalte E so, dass nach dem Tausenderwert als Tausendertrennzeichen ein Leerzeichen verwendet wird. Diese müssen Sie löschen. Ich würde die Spalte (hier E) markieren und dann mit Home / Find & Replace (ganz rechts) das Leerzeichen (einfach ein Blank eintippen) durch nichts ersetzen.
Kann ich mit der Summe nicht mehrere getrennte Bereiche addieren?
Doch. Aber die Bereiche werden nicht mit einem Leerzeichen, sondern mit einem Semikolon (;) getrennt.
Also – nicht so:
=SUMME(G5:G18 G30:G42 G56:G71 G80:G93)
sondern so:
=SUMME(G5:G18;G30:G42;G56:G71;G80:G93)
Übrigens: das Leerzeichen hat auch eine Funktion – es bedeutet: Schnittmenge. Man könnte berechnen:
=SUMME(C5:H21 G5:G26)
Damit würde die Summe über die Zelle G1:G21 gezogen werden.
Meine Empfehlung – tun Sie das nicht – das versteht kein Mensch, was Sie da tun! Und: wenn die Schnittmenge leer ist, quittiert Excel dies mit der Fehlermeldung #NULL!
Summe rechnet nicht.
Richtige Schreibweise: ; statt Leerzeichen!
Leerzeichen bedeutet Schnittmenge!
Das würde funktionieren; ist aber unverständlich!
Ich habe mich einmal gewundert, weshalb bei einer Dame eine kleine Tabelle immer sehr stark verkleinert wurde. Oder umgekehrt – warum bei ihr immer so viele Leerseiten gedruckt wurden. Bis ich dahinter kam, dass sie Daten nicht mit der Taste [Entf] gelöscht hat, sondern, indem sie die Leertaste gedrückt hat. Somit stand in einer Zelle – ziemlich weit rechts außen – immer ein Leerzeichen, das man (fast) nicht mehr findet. Auf alle Fälle nicht mehr sieht!
Fazit: Tun Sie so etwas bitte nicht!
Die Originaltabelle
Seitenansicht I
Anpassen auf eine Seite
Seitenansicht II
Keine Anpassung – 11 Seiten werden ausgedruckt!