Excel: Text in Bereich suchen, Wert dieser Zelle ausgeben
Hallo liebe Community,
ich suche z.Z. eine Formel, mit der ich einen Bereich nach einem bestimmten Text in einer Formel absuchen kann und dann einen TEIL davon in der aktuellen Zelle auszugeben.
Hier ein Beispiel:
In Bereich A1:A100 steht irgendwo "Besucher, 1234" (die Zahl ist jedes mal unterschiedlich). Mein Wunsch ist es nur diese Zahl in einer neuen Zelle auszugeben, sagen wir es mal so:
=WENN(A1:A100="Besucher"; dann alle Werte ab dem Komma (1234) hier eintragen;"nicht gefunden")
Klingt ziemlich simpel eigentlich und ich denke ich stehe nur auf dem Schlauch, aber ich kriege es nicht gebacken.
Für jede Hilfe bin ich dankbar.
Grüße, Peter
5 Antworten
etwas komplex, funktioniert aber:
=WENNFEHLER(RECHTS(SVERWEIS("Besucher*";A1:A100;1;0);
LÄNGE(SVERWEIS("Besucher*";A1:A100;1;0))-
SUCHEN(", ";SVERWEIS("Besucher*";A1:A100;1;0))-1);
"nicht gefunden")
Erklärung: Mit den SVERWEIS-Blöcken wird immer die richtige Zelle gesucht. Dann wird mit RECHTS und LÄNGE die Zahl extrahiert.
Ich habe es jetzt probiert und bei mir funktioniert es soweit. Vielen vielen Dank!
@BoskoBiati: Kannst du das etwas genauer erklären, wann es nicht funktionieren soll? Habe mehrere Varianten getestet und bisher alles prima. Zur Info: Das Format ist immer "Text, Zahl"
Grüße, Peter
Natürlich findet die Formel nur Zellen, die mit "Besucher" beginnen. Das war aber auch genau so gefragt.
Es wird auch immer nur die erste Zelle gefunden, falls mehrere Zellen mit "Besucher" beginnen.
Ich weiß immer noch nicht wie die Daten aussehen. Daher kann ich es nicht ganz nachvollziehen.
Ich habe die Formel nur mit meinen Beispieldaten getestet und dabei festegestellt, dass die Abfrage auch durchgeführt wird, wenn Besucher nicht in der Zelle steht. In Bezug auf das obige Beispiel, wird mir in Zeile 6 (Kunde, 1239), 1240 als Ergebnis ausgegeben, anstelle von nicht gefunden.
Ich weiß immer noch nicht wie die Daten aussehen.
Der Fragesteller hat unten in einem Kommentar das genauer erklärt:
Besucher, 1234
Informationen, 11111
Blätter, 22
+weitere Werte, jeden Monat anders angeordnet, also kein fester Zellenbezug möglich (Anordnung liegt am Programm, nichts zu ändern)
Demzufolge gibt es im Bereich A1:A100 genau einmal die Zelle "Besucher, [Zahl]". Und nur diese eine Zelle soll gefunden werden und diese eine Zahl soll extrahiert werden. Deshalb weiß ich nicht, was du mit "Diese Variante funktioniert aber nur so lange, bis sich der Zeilenanfang ändert" meinst. Genau das ist nämlich gewünscht.
Du darfst die Formel nicht in Spalte B nach unten kopieren. Warum auch? Die gesuchte Zahl wird in die gewünschte Zelle geschrieben. Eine Hilfsspalte ist unnötig.
Ich hatte den unteren Teil nicht gesehen. Jetzt wird mir Einiges klar. Danke für den Hinweis.
Habe es jetzt in meine Tabelle komplett eingefügt. Funktioniert sehr gut, danke noch mal!
Auch an alle anderen Danke die mir hier ihre Möglichkeiten präsentiert haben.
Grüße, Peter
wenn die Anzahl der Zahlen immer gleich ist, zB 4 Stellen, kannst du mit der Formel RECHTS einfach die letzten 4 Stellen auslesen
Wenn nicht nur Besucher stehen sollte, sondern auch z. B. Zuschauer und danach eine beliebige Zahl, so musst du eingeben:
=RECHTS(A1;LÄNGE(A1)-SUCHEN(",";A1))
Leider geht aus deiner Beschreibung nicht genau hervor, wie die Daten aufgebaut sind. Ich vermute mal, dass in der Liste mehrere Bezeichnungen wie Kunde, Lieferant oder Ähnliches auftreten können.
Vielleicht hilft das folgende Beispiel.
Formel B1:
=WENN(ISTZAHL(SUCHEN("besucher";A1));RECHTS(A1;LÄNGE(A1)-SUCHEN(",";A1)-1);"keine Treffer")
│ A │ B │
───┼────────────────┼───────────────┤
1 │Besucher, 1234 │ 1234│
───┼────────────────┼───────────────┤
2 │Besucher, 1235 │ 1235│
───┼────────────────┼───────────────┤
3 │Besucher, 1236 │ 1236│
───┼────────────────┼───────────────┤
4 │Besucher, 1237 │ 1237│
───┼────────────────┼───────────────┤
5 │Besucher, 1238 │ 1238│
───┼────────────────┼───────────────┤
6 │Kunde, 1239 │keine Treffer │
───┼────────────────┼───────────────┤
7 │Besucher, 1240 │ 1240│
───┼────────────────┼───────────────┤
8 │Kunde, 1241 │keine Treffer │
───┼────────────────┼───────────────┤
9 │Besucher, 1242 │ 1242│
───┼────────────────┼───────────────┤
10 │Besucher, 1243 │ 1243│
───┴────────────────┴───────────────┘
Tabelle mit dem Tabellen-Zeichner-Addin generiert.
Version: 2.0
Download: <a href="http://www.gutefrage.net/tipp/excel-tabellen-darstellen">http://www.gutefrage.net/tipp/excel-tabellen-darstellen</a>
© REDas ist leider nicht genau was ich gesucht habe, ich glaube ich habe die Lösung jetzt bereits in einem anderen Kommentar. Danke trotzdem für deine Mühe!
Ist die Zahl hinter Besucher immer vierstellig? dann hilft vielleicht diese Formel:
=WENN(SUCHEN("Besucher";A1);RECHTS(A1;4);"nicht Gefunden")
Gruß
Die Zahl kann auch mal 3 oder 5stellig sein. Außerdem ist das Problem ja, dass ich nicht weiß in welcher Zelle "Besucher" steht. Der Ort kann jeden Monat wechseln, deswegen wollte ich den Bereich A1:A100 absuchen.
Die Formel musst du neben der Spalte mit dem Inhalt eintragen. In JEDE Zeile. Mit dem kleinen schwarzen Viereck rechtsunten (bei markierter Zelle) kannst du die Formel in alle zeilen kopieren.
Mit variabler Länge der Zahl wird komplizierter:
=WENN(SUCHEN("Besucher";A1);RECHTS(A1;LÄNGE(A1) - FINDEN(",";A1));"nicht gefunden")
Nun das Problem wäre dann, dass ich ca. 50Werte wie den oben genannten habe, also "Beschreibung, Wert", die jedes mal in einer anderen Reihenfolge aus unserem Programm ausgegeben werden und die ich dann sozusagen Automatisiert sortieren möchte. Grundsätzlich würde es mir auch mit Zwischenschritt reichen, also z.B.:
Tabelle1: (Basistabelle)
- A1:
- Besucher, 1234
- Informationen, 11111
- Blätter, 22
- +weitere Werte, jeden Monat anders angeordnet, also kein fester Zellenbezug möglich (Anordnung liegt am Programm, nichts zu ändern)
Tabelle2: (Werte so ordnen, wie ich es möchte)
- A1
- Blätter, 22
- Besucher, 1234
- Informationen, 11111
Tabelle3: (nurnoch die Zahlen aus Tabelle 2 in bestimmte Felder (das krieg ich hin) zuordnen)
Jetzt etwas klarer wo mein Problem liegt?
Danke trotzdem für deine bisherigen Antworten!
Erstmal Werte trennen (über Daten->Werte in Spalten->Komma getrennt)
Dann hast du:
Spalte A Blätter Besicher Informationen
Spalte B 22 1234 11111
In Tabelle3 zum Beispiel für die Anzahl der Blätter: =SVERWEIS("Blätter";Tabelle1!A1:B50;2;FALSCH)
Scheint ein großes Tabellenmonster zu werden ;-)
Diese Variante funktioniert aber nur so lange, bis sich der Zeilenanfang ändert.