Chris hat ein Arbeitsblatt mit Kundennamen in den Spalten A bis F. In Spalte G möchte er eine Formel einfügen, die alle Namen aus den sechs Namensspalten in eine lange Zeichenfolge mit den Zeichen // zwischen den einzelnen Namen verkettet . Es ist möglich, dass nicht in allen sechs Spalten Namen vorhanden sind und keine externen führenden oder nachfolgenden // Trennzeichen vorhanden sein sollten.

Das Verketten von Text in Excel ist einfach. Wenn Sie beispielsweise etwas in Zelle A2 haben und es mit dem in Zelle B2 verketten möchten, können Sie dies mit der folgenden Formel tun:

=A2 & B2

Sie können die // Trennzeichen zwischen den beiden Werten einfügen, indem Sie sie einfach an der richtigen Stelle hinzufügen:

=A2 & "//" & B2

Das ist ziemlich einfach. Mit diesem Ansatz können Sie alle sechs Namen mit der folgenden Formel verketten:

=A2 & "//" & B2 & "//" & C2 & "//" & D2 & "//" & E2 & "//" & F2

Schwierig wird es, wenn Sie erkennen, dass einige dieser Zellen möglicherweise nichts enthalten. Somit würde die Formel entweder zu nachgestellten oder endenden // Trennzeichen oder zu doppelten Trennzeichen (////)

führen irgendwo in der Mitte des Ergebnisses.

Die naheliegende Lösung besteht darin, IF-Anweisungen zu verwenden, um den Inhalt der Namenszellen zu überprüfen, bevor sie verkettet werden. Dies kann jedoch zu erstaunlich langen Formeln führen. Die folgende Formel führt beispielsweise die Überprüfung und Verkettung korrekt durch:

=IF(RIGHT(CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)),2)="//",LEFT(CONCATENATE(IF(A3="","", CONCATENATE(A3,"//")),IF(B3="","",CONCATENATE(B3,"//")), IF(C3="","",CONCATENATE(C3,"//")),IF(D3="","",CONCATENATE(D3,"//")), IF(E3="","",CONCATENATE(E3,"//")),IF(F3="","",F3)), LEN(CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)))-2),CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)))

Ja, dies ist eine einzeilige Formel. (Puh!) Diese Formel verwendet den Ansatz, IF-Anweisungen zu verschachteln, um das gewünschte Ergebnis zu erzielen. Dies mag in diesem speziellen Fall funktionieren, aber die Formel liegt sehr nahe an der Grenze von Excel, nach der nur IF-Anweisungen bis zu sieben Ebenen tief verschachtelt werden dürfen.

Die Lösung für das Problem der potenziellen verschachtelten Ebenen besteht darin, die IF-Anweisungen einfach nicht zu verschachteln. Stattdessen können Sie jede Zelle einzeln auswerten und alles verketten, was zurückgegeben wird.

=MID(IF(ISTEXT(A3),"//"&A3,"") & IF(ISTEXT(B3),"//"&B3,"")

& IF(ISTEXT(C3),"//"&C3,"") & IF(ISTEXT(D3),"//"&D3,"") & IF(ISTEXT(E3),"//"&E3,"") & IF(ISTEXT(F3),"//"&F3,""),3,2000)

Beachten Sie, dass diese Formel viel kürzer ist. Sie können besser sehen, was es tut, wenn Sie sich die Formel „ausgebrochen“ in mehreren Zeilen ansehen:

=MID( IF(ISTEXT(A3),"//"&A3,"") & IF(ISTEXT(B3),"//"&B3,"") & IF(ISTEXT(C3),"//"&C3,"") & IF(ISTEXT(D3),"//"&D3,"") & IF(ISTEXT(E3),"//"&E3,"") & IF(ISTEXT(F3),"//"&F3,""),3,2000)

Jede einzelne IF-Anweisung in der Formel wertet eine Namenszelle aus und gibt entweder nichts („“) zurück, wenn die Zelle keinen Text enthält, oder sie gibt das Trennzeichen (//) gefolgt vom Namen zurück. Die gesamte Formel wird dann in die MID-Anweisung eingeschlossen, die das erste // Trennzeichen in der Zeichenfolge effektiv abschneidet.

Diese Formel kann noch weiter verkürzt werden, wenn Sie anstelle der ISTEXT-Funktion zum Auswerten der Zellen einfach einen Booleschen Vergleich durchführen, um herauszufinden, ob sich Text in der Zelle befindet, wie folgt:

=MID(IF(A3>"","//"&A3,"") & IF(B3>"","//"&B3,"") & IF(C3>"","//"& C3,"") & IF(D3>"","//"&D3,"") & IF(E3>"","//"&E3,"") & IF(F3>"","//"&F3,""),3,2000)

Dies ist genau die gleiche Technik, nur etwas kürzer. (Und viel kürzer als die ursprüngliche Formel.)

Diese Formel funktioniert hervorragend, vorausgesetzt, die Werte in den Namenszellen sind Text. Wenn Ihre Namensspalten aus irgendeinem Grund numerische Werte enthalten, können Sie die Formel einfach so ändern, dass ISBLANK anstelle von ISTEXT verwendet wird, wie hier gezeigt:

=MID(IF(ISBLANK(A3),"","//"&A3) & IF(ISBLANK(B3),"","//"&B3)

& IF(ISBLANK(C3),"","//"& C3) & IF(ISBLANK(D3),"","//"&D3)

& IF(ISBLANK(E3),"","//"&E3) & IF(ISBLANK(F3),"","//"&F3),3,2000)

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (2173) gilt für Microsoft Excel 97, 2000, 2002 und 2003.