Durch Kommas getrennte Werte in einer Zelle zählen

In diesem Tutorial erfahren Sie, wie Sie mithilfe einer Kombination der Funktionen LEN, TRIM und SUBSTITUTE durch Kommas getrennte Werte in einer Excel-Zelle zählen.

Wie zählt man die durch Kommas getrennten Werte in einer Excel-Zelle?

Wie im folgenden Screenshot gezeigt, enthält Spalte B eine durch Kommas getrennte Liste von Zeichenfolgen – und Sie möchten die Anzahl der durch Kommas getrennten Werte in jeder Zelle ermitteln. Gehen Sie dazu wie folgt vor:

Allgemeine Formel

=IF(ISBLANK(B3),„",LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",„"))+1)

Argumente

Wie wendet man diese Formel an?

1. Wählen Sie eine leere Zelle aus, um das Ergebnis anzuzeigen – in diesem Fall Zelle D3.

2. Geben Sie die folgende Formel ein und drücken Sie die Eingabetaste. Ziehen Sie anschließend den Ausfüllknauf nach unten, um die Formel auf weitere Zellen anzuwenden.

=IF(ISBLANK(B3),„",LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",„"))+1)

Hinweis: In dieser Formel steht B3 für die Zelle, die die durch Kommas getrennte Zeichenfolge enthält, deren Werte Sie zählen möchten. Sie können die Zellenreferenz bei Bedarf anpassen.

Wie funktioniert diese Formel?

=IF(ISBLANK(B3),„",LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",„"))+1)

1. LEN(TRIM(B3)): Die TRIM-Funktion entfernt alle überflüssigen Leerzeichen aus der durch Kommas getrennten Zeichenfolge „AA, BB, CC, DD“ und behält jeweils nur ein einzelnes Leerzeichen zwischen den Wörtern. Anschließend berechnet die LEN-Funktion die Gesamtlänge von „AA, BB, CC, DD“ und gibt das Ergebnis 14 zurück;

2. LEN(SUBSTITUTE(TRIM(B3),",",„"))

• SUBSTITUTE(TRIM(B3),",",„")Wie in der obigen Erläuterung gezeigt, entfernt die TRIM-Funktion hier alle überflüssigen Leerzeichen aus „AA, BB, CC, DD“ und gibt das Ergebnis als „AA, BB, CC, DD“ zurück. Anschließend ersetzt die SUBSTITUTE-Funktion alle Kommas in „AA, BB, CC, DD“ durch nichts und liefert „AA BB CC DD“.
• LEN(„AA BB CC DD")Die LEN-Funktion gibt die Anzahl der Zeichen in der Textzeichenfolge „AA BB CC DD“ zurück – das Ergebnis ist 11.

3. 14-11+1: Hier wird die Gesamtlänge von „AA, BB, CC, DD“ von der Gesamtlänge von „AA BB CC DD“ subtrahiert, um die Gesamtanzahl der Kommas in der Zeichenfolge zu ermitteln (14 − 11 = 3). Da nach dem letzten Komma stets ein Wort steht, addieren Sie 1 zu dieser Zahl, um die Gesamtanzahl der durch Kommas getrennten Wörter zu erhalten – in diesem Fall also 4.

4. WENN(ISTLEER(B3);„";14): Die WENN-Funktion besagt hier: Wenn Zelle B3 leer ist, wird nichts zurückgegeben; andernfalls liefert sie die Zahl 14. Da B3 einen Wert enthält, gibt die Funktion 14 zurück.

Verwandte Funktionen

Verwandte Formeln