Frage von Badhole, 50

Wer kann bei Excel Formel helfen?

Ich habe ein Problem mit Folgender Formel: =WENN(UND(F16>0;F17=0);(C16&" "&D16&" "&E16&" "&F16&" - "&G16&"");AB17)

Wenn ich zum Beispiel folgende Zahlen eingebe: 11 22 3333 004 - 5 Bekomme ich als ergebnis: 11 22 3333 4 - 5 Ich bräuchte aber unbedingt die 0 am Anfang mit. Die Zeilen habe ich schon wie folgt Formatiert 00 00 0000 000 - 0 und ## ## #### ### - # hat leider alles nichts geholfen. Ich danke schon mal für eure Hilfe.

Hilfreichste Antwort - ausgezeichnet vom Fragesteller
von Iamiam, Community-Experte für Excel, 19

in dem Moment, wo Du mit & verknüpfst oder ein Leerzeichen als Text dazwischen schiebst (eines dazwischen reicht!), hast Du einen Text. Ich verstehe zwar nicht, was das soll (muss ich auch nicht), aber wenn Du in einem Text die Zahl 004 auch so darstellen willst, musst Du

=Text(4;"000") verwenden. und wenn das führende Leerzeichen mit rein soll, dann vor die Nullen noch ein Leerzeichen.

Das lässt sich analog auch auf die anderen Zahlen übertragen, zB

=WENN(UND(F16>0;F17=0);TEXT(11;"00")&TEXT(22;" 00")&TEXT(3333;" 0000")&TEXT(4;" 000 ")&TEXT(-5;" 0");AB17)

oder mit Bezügen:

WENN(UND(F16>0;F17=0);TEXT(C16;"00")&TEXT(D16;" 00")&TEXT(E16;" 0000")&TEXT(F16;" 000 ")&TEXT(G16;" 0");AB17)


Das - schiebt sich vor das " 0", wusste ich auch nicht.
Reicht Dir das so oder gibts nicht berücksichtigte Sonderfälle?
Ohne die - 5 wäre es auch mit Faktoren (10^12, 10^10, 4*10^3 etc, hab das jetzt nicht geprüft) als Summe (Zahl)  darstellbar, und dann in genau dem Leerzeichen-gespickten Format, das Du auch eingestellt hast! (aber eben nur, wenn es eine Zahl ist. Das - 5 verhindert das)

Kommentar von Iamiam ,

sehe gerade, dass PWolff ein fast gleiches Konzept schon vor mir eingestellt hatte!

Kommentar von Iamiam ,

noch der Vollständigkeit halber die Zahlenformel,

erst dann zu Text, notwendig  wegen des Anhängens von -5:

=WENN(UND(F16>0;F17=0);TEXT(C16*10^9+D16*10^7+E16*10^3+F16;"00 00 0000 000 ")&TEXT(G16;" 0");AB16)

Für die Formel ohne die - 5 kann die Zahl im erwähnten Bendef-Format rechenbar dargestellt werden, es wird im DANN-Fall aber auch AB16 in diesem Format dargestellt, sofern da eine Zahl steht.

Antwort
von MagicalMonday, 41

Und wenn du die Zellen einfach als Text formatierst? Rechnen solltest du damit trotzdem noch können, und die führenden Nullen bleiben dran.

Antwort
von PWolff, 33

Nimm die Formate auch in die Formel:

(TEXT(C16;"00")&" "&TEXT(D16;"00")&" "&TEXT(E16;"0000")&" "&TEXT(F16;"000")&" - "&TEXT(G16;"0")&"")

Kommentar von iMPerFekTioN ,

Brauch man doch gar nicht, es reicht doch an der einen Stelle das Format mit zu übergeben:

=WENN(UND(F16>0;F17=0);(C16&" "&D16&" "&E16&" "&TEXT(F16;"000")&" - "&G16&"");AB17)
Kommentar von PWolff ,

Der Mehraufwand ist gering (Copy & Paste), und nach meiner langjährigen Erfahrung mit solchen Problemen ist es sehr wahrscheinlich, dass irgendwann mal auch in einer der anderen Zahlen eine führende Null auftritt.

Die einzigen Fälle, wo ich derartige Änderungen nicht vorgenommen habe, sind die, wo mein Chef es mir verboten hat. (Üblicherweise sehr geizige Kunden, die bei allen möglichen Gelegenheiten noch ein paar Prozent Preisnachlass raushandeln. Die kriegen wirklich nur, was sie bestellt haben. Für zukünftige Änderungen sollen sie dann auch zahlen. - Die vollständige Codeänderung erzeugen wir auch gleich bei der Gelegenheit mit - es ist einfacher, wenn man sowieso im Thema ist -, besonders unangenehme Kunden kriegen dann die Zeit berechnet, die es ohne diese Vorbereitung gedauert hätte.)

Kommentar von iMPerFekTioN ,

Versteht ich nicht, alles in Text zu formatieren macht (an dieser Stelle) keinen Sinn!

Wenn man das wie du macht, kann man gleich die Zellen in Text formatieren.

Das wäre das gleiche Ergebnis und man würde sich die ganze Formel sparen.

Kommentar von gfntom ,

PWolff hat sch0n recht.

Versteht ich nicht, alles in Text zu formatieren macht (an dieser Stelle) keinen Sinn!

Das ergibt durchaus Sinn, das ist ja genau das, was gewollt ist und was du auch machst: der &-Operator verknüpft Einzeltexte zu einem Gesamttext.

Über die TEXT-Funktion gleichzeitig das Ausgabeformat mitanzugeben ist schon sinnvoll. Die einzelnen Quellzellen als Text zu formatieren ist in den meisten Fällen keine Alternative, da du ja dabei auch Auswirkungen auf andere Zugriffe hast.

Der Fragesteller selbst hat diesen Fall ja als "Beispiel" benannt, d.h. davon auszuhehen, dass der Fall nur bei dem einen Wert auftreten kann, ist gewagt.

Aber natürlich: man kann es in diesem einen Fall so machen wie du es vorschlägst und auf den ersten Blick ist es die effizientere Lösung. Die konsistentere und wartbarere ist aber jedenfalls die von PWolff. Und jeder, der schon mal "gewachsene" Excel-Sheets nach Fehlern durchsuchen oder einfach nur ausmisten musste, weiß, wovon ich rede.

Antwort
von iMPerFekTioN, 33

Meinst du ungefähr so?

Grüße,

iMPerFekTioN

Antwort
von LeroyJenkins87, 39

Versuch es mal indem du vor die 004 ein Hochkomma schreibst. Also:

'004

Kommentar von iMPerFekTioN ,

Dann wäre es ja als Text formatiert =)

Kommentar von LeroyJenkins87 ,

Ich weiss. Es funktioniert aber. Ausser man möchte damit weiterrechnen.

Kommentar von iMPerFekTioN ,

Es ist aber Schade, wenn du die ganze Zelle dazu in Text Formatierst, das kannst du auch per Formel während der Berechnung machen!

So behält die Zelle die Formatierung bei und in der Anzeige hat man das gewünschte Ergebnis:

=WENN(UND(F16>0;F17=0);(C16&" "&D16&" "&E16&" "&TEXT(F16;"000")&" - "&G16&"");AB17)

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten