Zellinhalte mit doppelten Werten trennen unterschiedliche Längen in Exel 2010

Computer: Software & Programmierung Office Software
#1

Ich habe diese Formel für eine Problem bekommen
=RECHTS(C7;LÄNGE(C7)-FINDEN(RECHTS(C7;8);C7)-7)
mit der Formel wird aus „Japan Fussball Gamba OsakaGamba Osaka“ in Zelle B nun in Zelle A nur Gamba Osaka

aber leider habe ich dabei nicht bedacht, das auch folgendes passieren kann:
a.) der Name der Mannschaft hat nur 5 Buchstaben -> Japan Fussball FC GifuFC Gifu
b.) der LIganame wird nicht mit importiert -> V-Varen Nagasaki = Ergebnis = leere Zelle
c.) Als Ergebnis wird #Wert geliefert -> NK Krka = Ergebnis = #Wert

Kann man mit einer Wenn-Formel das abfangen? Ich habe schon versucht die Formel zu ändern, also die 8 auf 5 und die 7 auf 4 gesetzt aus der Formel, aber es gibt keine richtiges Ergebnis.

Wie müsste also die Formel aussehen?

Hier mal die Werte aus SlowenienI die ich so in Excel stehen habe
Slowenien Fussball NK MariborNK Maribor
Slowenien Fussball ND HiT GoricaND HiT Gorica
Slowenien Fussball FC KoperFC Koper
NK Zavrč
Slowenien Fussball NK Rudar VelenjeNK Rudar Velenje
Slowenien Fussball NK DomžaleNK Domžale
NK Krka
Slowenien Fussball Olimpija LjubljanaOlimpija Ljubljana
Slowenien Fussball TriglavTriglav
Slowenien Fussball MIK CM CeljeMIK CM Celje

Hat einer eine Lösung?

Vielen Dank, falls mir jemand helfen könnte :smile:

#2

Hallo Hans Holger
Vorausgesetzt, dass die Werte immer auf die gleiche Art zusammengestellt sind, könnte dir folgendes helfen:
Im Beispiel stehen die Werte in Spalte A.
In A1 schreibst du den Namen der Liga, direkt darunder kommt deine Tabelle. In B2 kommt dann die Formel

=WENN(LINKS(A2;LÄNGE($A$1))=$A$1;RECHTS(A2;(LÄNGE(A2)-(LÄNGE($A$1)+1))/2);A2)

Diese dann runterziehen.
Probier das mal aus. Wenn es passt, erklär ich dir gerne die Formel

#3

Hallo Hans Holger,

ehrlich gesagt weiss ich nicht, wo geanu das Problem liegt. Auch verstehe ich den Aufbau nicht. Warum hast du den Namen 2x hintereinander stehen?

#4

leider keine Lösung

#5

Hallo Hans Holger,

hier die Lösung:

=RECHTS(A1;(LÄNGE(A1)-(SUCHEN(TEIL(A1;FINDEN(" „;A1;FINDEN(“ „;A1;1)+1)+1;5);A1;FINDEN(“ „;A1;FINDEN(“ ";A1;1)+1)+5)-1)))

Die Zeichenfolge in einer Spalte rechts von den Vereinsbezeichnungen eingeben.

Also im Beispiel steht das Kauderwelsch in Zelle A1 und die Lösung in der Zelle rechts davon, in die du die Formel eingibst, dann einfach nach unten kopieren.

Hinweis: Die 5 in der Formel gibt die Mindestbuchstabenzahl des Vereinsnamens an. Diese Zahl kann auch reduziert werden, dann aber an beiden Stellen! Ich denke jedoch, dass 5 eine sinnvolle Zahl ist, um eine größtmögliche Übereinstimmung der doppelten Werte zu erreichen.

Kleiner Tipp:
Nach markieren der Zelle den Mauszeiger auf die untere rechte Ecke der Zelle positionieren bis sich der Zeiger in ein kleines schwarzes Kreuz verwandelt, dann einfach einen Doppelklick ausführen und die Formel kopiert sich bis an das Ende der Liste (soweit die Werte zusammenhängend sind!)

Gib doch bitte eine Rückmeldung, ob das funktioniert hat.

Danke und freundliche Grüße aus Gronau

Excel-Fan

#6

Hallo Markus,
zur Info möchte ich dazu sagen, das ich die Werte aus dem Netz importieren „Web Daten abrufen“. Die Seite die ich verwende hat vor jeder Mannschaft das VereinsWappen und daher erscheinen die Namen doppelt. Ist kein Vereinswappen vorhanden, dann fehlt der Liganame und der Vereinsname ist nicht doppelt.

Aber es gibt schon eine Lösung, daher denke ich das ich keine Antwort mehr benötige und bedanke mich aber dennoch für die Rückmeldung.

Ich hatte es mit der Wenn-Formel versucht, habe aber das Problem mit „nur 5 Buchstaben“ nicht lösen können.

Danke Gruß Hans Holger

#7

Hallo Excel-Fan,
hat fast funktioniert :smile:

Punkt a.) und b.) wurden gelöst, aber nicht Punkt c.)

dafür erst Mal vielen Dank, aber kann man die Formel auch erweitern so das Punkt c. gelöst wird?

c.) Als Ergebnis wird #Wert geliefert -> NK Krka = Ergebnis = #Wert

In der Zelle A7 erscheint nicht der Mannschaftsname sondern der Fehler #Wert, weil beim Importieren aus dem Internet der Liganame fehlt. Das ist eine Web-Abfrage in Excel.

Daher dachte ich auch man könne es mit der Wenn-Funktion lösen.

Das Beispiel vom Rudi O funktioniert nicht, weil die Formel in Zelle A steht und die Tabellenwerte in der Zelle C. Habe versucht die Formel abzuändern, aber wegen dem Absoluten Bezügen auf $A$1 funktioniert die Formel nicht.

Haben Sie noch einen Vorschlag?

Ich schreibe in meiner Frage noch mal das Beispiel wie es bei mir in Excel aussieht.

Vielen Dank
Gruß Hans Holger

#8

Sorry aber ich kann meine Frage nicht nachträglich bearbeiten.

In Zelle C stehen dieses Werte aus dem Netz:
(C32)Brasilien Fussball SE PalmeirasSE Palmeiras
(C33)Chapecoense

Mit der Formel die Sie geschrieben haben erhalte ich dieses Werte dazu:
(A32)SE Palmeiras
(A33)#WERT!

Danke

#9

Vielen Dank Herr Rudi,

aber die Formel funktioniert nicht wegen dem Absoluten Bezug $A$1

Ich nehme nochmal meine 3 Bespiele a-c.

a.) der Name der Mannschaft hat nur 5 Buchstaben -> Japan Fussball FC GifuFC Gifu = leere Zelle
b.) der LIganame wird nicht mit importiert -> V-Varen Nagasaki = Ergebnis = leere Zelle
c.) Als Ergebnis wird #Wert geliefert -> NK Krka = Ergebnis = #Wert

Die Werte stehen in der Zelle C7 und die Formel in Zelle A7, also auf gleicher Höhe und bei a+b ist dann die Zelle A leer und bei b erhalte ich #Wert.

Die Werte werden mit einer Web-Abfrage in Excel automatisch aus dem Internet gezogen.

Da aber nicht immer der Liganame vom Anbieter mitgegeben wird, tauchen vereinzelt dann Mannschaftsnamen in Zelle C auf ohne Liganamen und das verursacht dann den Fehler #Wert.

Da ich in einer anderen Tabelle aber mit SVerweis und Index auf die Mannschaftsnamen angewiesen bin (exakte Schreibweise), erhalte ich dann dort den Fehler #NV was logisch ist, weil keine Übereinstimmung mehr gefunden wird.

Manuell könnte ich dann das korrigieren, aber bei bis zu 400 Spielen und 100 Fehlern würde das einfach zuviel Zeit kosten.

Haben Sie eine Lösung für das letzte Problem?

Vielen Dank
Mfg
Hans Holger

#10

Wer lesen kann ist klar im Vorteil,

Formel funktioniert :smile: weil ich zum Glück in den Tabellenblatt Zeilen freigelassen habe zwischen den Ligen in denen keine automatische Abfrage getätigt wird.

Ich muss nun nur 120mal die Liga-Namen über die Tabelle schreiben und schon funktioniert alles wunderbar.

Damit ist die Lösung perfekt

Vielen Dank

PS: Formel muss man mir nicht erklären habe die jetzt in Wenn/DANN/SONST zerlegt und dann verstanden was die als Ergebnis liefert.

Im Klartext: Es wird eigentlich nur geprüft, ob ein Liganame in einer bestimmten Länge vorhanden ist, dieser Übereinstimmt und dann vom Rest der übrig bleibt abgezogen bzw. getrennt. Der Rest, also der doppelte Mannschaftsname wird durch 2 geteilt, weil die Länge des Restes in jeder einzelnen Zelle ja immer gleich ist. O.k. kann man besser ausdrücken aber ich habe es wenigstens verstanden hoffe ich.

Also vielen vielen Dank
Gruß Hans Holger

#11

Achso, habe ich nicht ganz richtig verstanden.

Also wenn Sie nur die Zellen auslesen wollen, in denen ein Wert steht, gehen Sie wie folgt vor.

=Wenn(C33"";C33;"")

wenn Sie diese Zeichenfolge an meine Formel anhängen möchten, also das gesamte Ergebnis in einer Zelle, gehen Sie wie folgt vor:

An meine Formel ein Leerzeichen anhängen, dann ein „kfm. und“ also „&“, dann wieder ein Leerzeichen und dann die hier oben angegebene Formel (ohne Gleichheitszeichen!)

Dann sollten die Werte verkettet sein und bei einer leeren Zeichenfolge erscheint anstatt #Wert! eigentlich nichts.

Danke für eine Rückmeldung, ob die Lösung passt.

Gruß Excel-Fan

1 Like
#12

Nochmals vielen Dank Excel-Fan,

habe es so gemacht wie Sie geschrieben haben, das wäre dann die Formel dazu
=RECHTS(C57;(LÄNGE(C57)-(SUCHEN(TEIL(C57;FINDEN(" „;C57;FINDEN(“ „;C57;1)+1)+1;5);C57;FINDEN(“ „;C57;FINDEN(“ „;C57;1)+1)+5)-1)))&WENN(C57"“;C57;"")

Das die Suche:
USA Fussball Real Salt LakeReal Salt Lake

Und das hier das Ergebnis laut Formel:
Real Salt LakeUSA Fussball Real Salt LakeReal Salt Lake

Ich habe die Formel von User Rudi O. verwendet, ich hoffe Sie sind mir nicht böse.

Ich muß zwar dadurch nun 140mal den Liganamen in eine Zelle schreiben, also für jede Liga die es in der Abfrage gibt einmal den Liganamen angeben.

Aber die Formel funktioniert.

Ich bedanke mich nochmals recht herzlich ein schönes Wochenende Grüße aus Hessen :smile:

#13

Ich habe die Formel noch einmal überarbeitet. Diese sollte ohne getrennte Eingabe der Liga funktionieren:

=WENN(ISTFEHLER(FINDEN(" „;C7;FINDEN(“ „;C7)+1)+1);C7;RECHTS(C7;(LÄNGE(C7)-FINDEN(“ „;C7;FINDEN(“ ";C7)+1)+1)/2))

Der Wert muss in diesem Fall in C7 stehen

1 Like
#14

So viele Lösungswege ist ja unglaublich. Vielen Dank ich habe die Formel in meine Sammlung aufgenommen.

Bin aber gerade mit der ersten Formel und dem nachtragen fertig geworden.

Aber auch der zweite Vorschlag funktioniert, wie ich gerade getestet habe.

Also vieln Dank
schönes WE
udn Grüße aus Hessen

#15

Hallo Hans Holger,

ich habe Deine Werte in die Spalte A kopiert und mit folgender Formel für alle Werte Erfolg gehabt:

=WENN(LÄNGE(A1)>15;RECHTS(A1;LÄNGE(A1)-FINDEN(RECHTS(A1;6);A1)-5);A1)

Gruß Merlin

#16

Vielen Dank Merlin1964,
noch eine Lösung die funktioniert, wie viele Wege es gibt um ein Problem zu lösen, lässt mich immer erstaunen. Excel hat wohl eine unmenge an Funktionen die zur Lösung führen.

Also vielen Dank Gruß hhv

#17

Drei Lösungen sollten reichen :wink:

#18

Das stimmt aber dennoch vielen Dank.

Sie waren der einzige der erst geschaut hatte ob es schon eine Lösung gibt. Habe eben die nächtse erhalten.

Problem hier auf der Seite, wählt man zu wenig User aus bekommt man manchmal keine Antwort. Sind es zuviele User, gibt es Lösungen am Band :smile: nur kann ich das Thema nicht schliessen, damit andere es sehen, Problem wurde gelöst.

Also vielen Dank

#19

Ich habe das Problem dass man als experte nicht die Antworten anderer User sieht schonmal an den Support gemeldet. Aber da kam nie eine Antwort. Es nervt, wenn man arbeit investiert und hinterher sieht, dass das Thema längst erledigt war.

schönen Sonntag noch

#20

Hi Hans Holger,

das Problem an Deinem „iterativen“ Problem ist, dass man dafür normalerweise eine Funktion erstellen würde, dann kannst Du auch mit Schleifen arbeiten:

siehe z.B.:
http://www.hei-fi.com/index.php/de/office-blog/16-ex…

Als Standardformel ohne Makrocode wird es immer komplexer, je mehr Fälle man abfangen will.

Beispiel:
Angenommen, die Formel ist in A1 (, A2, …)
und deine Werte stehen in B1 (, B2, …

Man geht nun beispielsweise von den Leerzeichen des Teils links aus, der nicht identisch ist.
Gibt es keinen solchen Teil, so muss man ja nur vergleichen, ob die beiden Hälften der Zeichenfolge gleich sind, das geht mit Identisch()

in A1:
=WENN(IDENTISCH(RECHTS(B1;LÄNGE(B1)/2);LINKS(B1;LÄNGE(B1)/2));RECHTS(B1;LÄNGE(B1)/2);B1)

in B1: FC GifuFC Gifu

A1 zeigt an: FC Gifu

steht links daneben "Fussball ", also: „Fussball FC GifuFC Gifu“, so nehme ich jetzt mal an, dass dieser Text immer mit einem Leerzeichen von den beiden Identischen Textteilen getrennt ist.
Stimmt diese Annahme, so kann man ihn entsprechend entfernen:

Gibt es also links einen Text mit einem am Ende zu entfernenden Leerzeichen, muss man den Teil des Strings erst abziehen und (zur Vermeidung von #Wert vorher prüfen, ob es überhaupt ein Leerzeichen gibt).

=WENN(IDENTISCH(RECHTS(B1;LÄNGE(B1)/2);LINKS(B1;LÄNGE(B1)/2));RECHTS(B1;LÄNGE(B1)/2);WENN(ISTFEHLER(FINDEN(" „;B1));B1;WENN(IDENTISCH(RECHTS(B1;LÄNGE(ERSETZEN(B1;1;FINDEN(“ „;B1);“"))/2);LINKS(ERSETZEN(B1;1;FINDEN(" „;B1);“");LÄNGE(ERSETZEN(B1;1;FINDEN(" „;B1);“"))/2));RECHTS(B1;LÄNGE(ERSETZEN(B1;1;FINDEN(" „;B1);“"))/2);B1)))

(In der Formel ist der erste Fall mit enthalten)

Wenn der linke nicht-identische Teil des Textes zwei Leerzeichen hat (wie z.B. bei „Slowenien Fussball FC GifuFC Gifu“), wird es schon richtig kompliziert, man muss dann ja immer zunächst das zweite Leerzeichen suchen, wofür man zunächst das erste benötigt …

Folgende Formel funktioniert, wenn Du sie in A1 einfügst und nach unten ausfüllst, wobei die Vereinsliste in B1, B2, … steht:
(Es sind alle Fälle darin enthalten und werden geprüft:

  • Text ist nicht doppelt
  • Text ist doppelt
  • Text ist doppelt plus text davor mit einem Leerzeichen
  • Text ist doppelt plus text davor mit 2 Leerzeichen)

=WENN(IDENTISCH(RECHTS(B1;LÄNGE(B1)/2);LINKS(B1;LÄNGE(B1)/2));RECHTS(B1;LÄNGE(B1)/2);WENN(ISTFEHLER(FINDEN(" „;B1));B1;WENN(IDENTISCH(RECHTS(B1;LÄNGE(ERSETZEN(B1;1;FINDEN(“ „;B1);“"))/2);LINKS(ERSETZEN(B1;1;FINDEN(" „;B1);“");LÄNGE(ERSETZEN(B1;1;FINDEN(" „;B1);“"))/2));RECHTS(B1;LÄNGE(ERSETZEN(B1;1;FINDEN(" „;B1);“"))/2);WENN(ISTFEHLER(FINDEN(" „;B1;FINDEN(“ „;B1)+1));B1;WENN(IDENTISCH(RECHTS(B1;LÄNGE(ERSETZEN(B1;1;FINDEN(“ „;B1;FINDEN(“ „;B1)+1);“"))/2);LINKS(ERSETZEN(B1;1;FINDEN(" „;B1;FINDEN(“ „;B1)+1);“");LÄNGE(ERSETZEN(B1;1;FINDEN(" „;B1;FINDEN(“ „;B1)+1);“"))/2));RECHTS(B1;LÄNGE(ERSETZEN(B1;1;FINDEN(" „;B1;FINDEN(“ „;B1)+1);“"))/2);B1)))))

Hat man diese Formel verstanden, könnte man jetzt sogar noch ein drittes, viertes, usw. Leerzeichen im Linken Teil in die Formel einbauen, entsprechend verlängert sich immer die „Finden“ Formel … das überlasse ich Dir … :smile:

Freundliche Grüsse,

e-a-s-y