Office: WECHSELN & SVERWEIS gemeinsam nutzen (oder Alternative?)

Ich bräuchte bitte einmal Euer Schwarmwissen. Ich habe Zellen, deren Inhalt ich in Teilen ändern mag. Das Problem dabei, dass die Liste ziemlich lang wird, eine Verschachtelung der WECHSELN Formel ist mir bekannt... Ich denke irgendwie daran das mit einem SVERWEIS zu kombinieren oder so.

Aber kommen wir einfach einmal zur Herausforderung:



A1 ist der Ausgangspunkt. In B1:C4 habe ich die Zuordnungen.

Jetzt die Frage der Fragen, wie ich mit Hilfe von B1:C4 zu A2 und A3 komme. Sprich die Formeln für A2 und A3 suche ich, ohne die Werte fest in den Formeln zu setzen.

 

Hallo,

welche Excel Version?

 

Vielen Dank! Excel 2016. Mir geht es nicht darum Inhalte zu trennen. Zelle A1 will ich mit "WECHSELN" ändern. A2 bzw. A3 sollen die Ergebnisse sein. A3 vernachlässigen wir mal, A2 ist viel interessanter. Die Formel in A2 soll schauen ob es Text zwischen "1 " und " 3 4" gibt. Wird der Text - in dem Fall "2" - im Bereich B1:C4 gefunden, soll er gewechselt werden von "2" zu "ZWEI".

 

Hallo,

lade doch eine Datei herunter, wo ma den Text erkennt.
Im Bild sind nur 4 Zahlen zu sehen.

 

Ich habe das mal überarbeitet...



Die Formel für A2 suche ich. Dabei soll "gewechselt" werden zwischen "feiern " und " mal". Der Inhalt dazwischen ist nicht fix. Und wenn dieser im Bereich B1:C4 gefunden wird, soll er "gewechselt" werden. :) Uiuiuiui :)

 

Hallo,

für Excel Online oder 365.

Code:

=TEXTVERKETTEN(" ";1;LET(tt;TEXTTEILEN(A1;" ");WENNFEHLER(SVERWEIS(tt;$B$1:$C$4;2;0);tt)))

Für ältere mit Hilfsspalten.
=WENNFEHLER(SVERWEIS(GLÄTTEN(TEIL(WECHSELN($A1;" ";WIEDERHOLEN(" ";199));SPALTE(A1)*199-198;199));$B$1:$C$4;2;0);GLÄTTEN(TEIL(WECHSELN($A1;" ";WIEDERHOLEN(" ";199));SPALTE(A1)*199-198;199)))

Die einzellen Spalten mit &" "& Verketten.
=F1&" "&G1&" "&H1&" "&I1&" "&J1&" "&K1&" "&L1&" "&M1&" "&N1&" "&O1&" "&P1&" "&O6

 

Hallo,

neue Funktionenen für Excel2016 und höher.
Evtl. etwas für dich.

MS-Office-Forum - Einzelnen Beitrag anzeigen - ArrayFormeln für 2010, 2013, 2016, 2019

 

Hallo,

was ist mit dieser Formel in Excel2016 ? Funktioniert diese? Möglicherweise funktioniert die TEXTKETTE-Funktion erst ab Excel2019.
=TEXTKETTE(WENNFEHLER(SVERWEIS( XMLFILTERN("<t><w>"&WECHSELN(A1;" ";"</w><w>")&"</w></t>";"//w");$B$1:$C$4;2;0); XMLFILTERN("<t><w>"&WECHSELN(A1;" ";"</w><w>")&"</w></t>";"//w"))&" ")

Eventuell muss sie auch als Array-Funktion eingegeben werden (ob das nötig ist, kann ich mit meinem Excel365 nicht testen):
1) Eingabe vorgenannter Formel in die Formeleditierzeile (zB. mit Copy+Paste)
2) Abschluss der Formeleingabe nicht mit der EINGABE-Taste, sondern mit der Tastenkombination UMSCHALT+Strg+EINGABE
3) Danach erscheint die Formel in geschweiften Klammern eingeschlossen, wie folgt:
{=TEXTKETTE(WENNFEHLER(SVERWEIS(XMLFILTERN("<t><w>"&WECHSELN(A1;" ";"</w><w>")&"</w></t>";"//w");$B$1:$C$4;2;0);XMLFILTERN("<t><w>"&WECHSELN(A1;" ";"</w><w>")&"</w></t>";"//w"))&" ")}

 

Hallo,

falls die TEXTKETTE-Funktion in Excel2016 doch noch nicht funktioniert, könnte das auch über eine kleine benutzerdefinierte VBA-Funktion gelöst werden, die in ein allgemeines VBA-Codemodul zu speichern ist:

Code:

Public Function fnTextSpalte(varStr As Variant, Tr As String) As String
  fnTextSpalte = Join(WorksheetFunction.Transpose(varStr), Tr)
End Function

Die dazugehörige Formel im Arbeitsblatt lautet dann:
=fnTextSpalte(WENNFEHLER(SVERWEIS(XMLFILTERN("<t><w>"&WECHSELN(A1;" ";"</w><w>")&"</w></t>";"//w");$B$1:$C$4;2;0);XMLFILTERN("<t><w>"&WECHSELN(A1;" ";"</w><w>")&"</w></t>";"//w"));" ")
(Eventuell auch wieder als Array-Formel zu speichern).

 

Ihr seid mega! Vielen lieben Dank! Das TEXTVERKETTEN funktioniert noch nicht. Online ging es. Die TEXTKETTE gab auch nur den 1 Wert zurück. Ich habe das jetzt aber einfach mit den Hilfsspalten gemacht. Das funktioniert gut, ich habe mir das perfekt zurechtbiegen können!

MERCI und GRAZIE MILLE !!!