Office: versch. Wörter (Farben) aus Text filter und in neue Spalte kopieren

Hallo,

Ich benötige dringend eine Formel um mehrere Schlagwörter (alles Farben) aus verschiedenen Spalten mit Text herauszufiltern und in eine neue Spalte einzufügen.

Ich habe eine große Tabelle mit Artikeln, in verschiedenen Spalten stehen Artikelnamen, Beschreibungen, Größe etc. manche dieser Spalten enthalten die entsprechende Farbe des Artikels (z.b rot, gelb, blau, transparent etc.)

Ich muss jetzt die Gesamte Tabelle prüfen und für jede Zeile die entsprechende Farbe seperat herausfiltern und diese in eine neue Spalte kopieren.

Beispiel:

Klebeband 50m rot

Dann soll er mir das Wort "rot" herausfiltern (die Faben muss ich natürlich vorher alle definieren) und in eine neue Spalte und in die jeweilige Zeile rauskopieren.

Die Farben stehen in verschiedenen Spalten von H bis L und sollen dann z.b in Spalte M rauskopiert werden. In M soll dann eben nur die Farbe stehen , rot, blau, transparent etc.

Ich bin leider kompletter Excel Neuling und stehe hier vor einer für mich unmöglichen Aufgabe.

Kann mir da jemand anhand einer konkreten Beispiel Formel helfen?

Vielen Dank im voraus

 

Hallo,

realisierbar. Doch anstelle Deines Bildes wäre eine entsprechende XLSX-Datei (mit der zusätzlichen "Farbnamen"-Spalte) hilfreich und die Angabe Deiner XLS-Version.

 

Benutze Autofilter und Kopiere den sichtbaren Bereich, Start, Bearbeiten, Suchen..., Gehe zu, Inhalte, sichtbare Zellen.
Excel hat auch einen Makrorecorder.

Du bist im falschen Forum. Bildbearbeitung ist ein anderes.
Bilder lade ich mir nicht runter, da Excel damit nichts anfangen kann.

Hochgeladene Bilder können zwar als solche in Excel importiert werden, sind jedoch bei der Lösung von Problemen nicht sehr hilfreich, da man die eigentlichen Daten nicht ohne große und zeitraubende Umwege direkt in die Tabelle übertragen kann.

Ich baue keine Datei nach, die Zeit hat schon jemand investiert.

Bilder statt Datei | Excel ist sexy!
Eine hochgeladene Arbeitsmappe erhöht die Wahrscheinlichkeit, dass Du eine Lösung für Dein Problem erhältst.
Erstelle folglich bitte eine Demomappe, aus der deine Aufgabenstellung klar erkennbar ist und lade diese Mappe mit einem aussagekräftigen Dateinamen hoch.
Der Name einer hochgeladenen Mappe wird im Beitrag automatisch angezeigt, sodass es bei Verwendung von aussagekräftigen Namen leichter fällt, sie später im Ablageordner wiederzufinden und sie gedanklich einem bestimmten Thema zuzuordnen. Namen wie Muster*, Test*, Mappe*, Beispiel*, Fehler*, Kalender*, UserForm*, Forum* usw. sind so allgemein, dass eine Zuordnung zu einem Thema unmöglich gemacht wird.
Es sollte ein aussagekräftiger Name sein.
Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten ändern. Schaue Datei
http://hajo-excel.de/gepackt/fremd/Datei_verschluesseln.zip
Bei Download beachte bitte: von unsicheren Servern file-upload lade ich keine Datei herunter (lt. Einschätzung meines Virenprogramms)

Das ist nur meine Meinung zu dem Thema.

 

Hallo,

Danke erstmal für die Antwort, sorry wegen der Bild Datei, eine echte Mappe macht natürlich mehr Sinn. Daher hier ein Ausschnitt daraus im Anhang. Spalten A bis M (im original H-T) sind hier die Richtwerte, in Spalte O (original U) soll dann die Farbe herausgefiltert werden. In der Demo Mappe habe ich als Beispiel die obersten 2 Werte von Hand eingetragen.

Zur Excel version:
Microsoft® Excel® 2016 MSO (Version 2303 Build 16.0.16227.20202) 32 Bit

 

Du brauchst keine Aktion, da nichts in Farbe.
Mein Vorschlag hat Dir nicht gefallen.
Ich bin dann raus.

Gruß Hajo

 

Ich glaube da gibts ein Missverständnis

Mit Farbe ist rein der TEXT gemeint, also das Wort rot, blau, ect.

Was genau heisst dein Vorschlag hat mir nicht gefallen? Ich habe ja im ersten Text so gut ich konnte erklärt was ich brauche, tut mir Leid wenn ich mit meiner Inkompetenz als Excel Neuling hier nicht gleich alles richtig mache, aber muss sich deswegen sicher keiner persöhnlich beleidigt fühlen. Dann bin ich hier definitiv falsch!

 

Hallo Squ1d82,

warum hast Du nicht den Teil Deiner XlSX-Datei, den Du bildlich hier dargestellt hattest, nicht in einer Dateikopie wie von mir vorgeschlagen eingestellt?

 

Hallo neopa,

Ich habe ja nachträglich eine Excel Mappe hochgeladen (siehe Kommentar #4) . Es ist nicht der gleiche Auszug wie auf dem Bild das stimmt, aber hier sind auch die obene Spalten entsprechend benannt was die Orientierung sicher erleichtert. Die Original Mappe hat mehr als 13.000 Zeilen, aber welcher Bereich davon als Beispiel dient, spielt ja für meine Fragestellung keine Rolle.

Wenn noch irgendetwas unklar ist, versuche ich dies gerne zu erklären, denke aber eigentlich aus dem ersten Text geht hervor was ich benötige. Wie schon erklärt, bin leider absoluter Excel Neuling und kenne (bis vielleicht auf SUMME) keinerlei Excel Funktionen.

 

Hallo Squ1d82,

Deine eingestellte Datei hatte ich gesehen. Eine Datei mit ein paar mehr Daten mit verschiedener "Färbung" und der Spalte mit ein paar entsprechenden Farbmerkmalen nach denen zu suchen ist (von denen welche gefunden und andere nicht) wäre aber hilfreicher. Können in einer Datenzeile auch verschiedene Farbmerkmale gefunden werden? Sollen alle gelistet werden?

 

Guten Morgen,

Ich habe nun nochmal einen anderen Auschnitt rauskopiert der das ganze nochmal besser verdeutlicht. Die entsprechenden Zielwörter habe ich rot eingefärbt, diese können in verschiedenen Spalten auftauchen, auch in mehreren einer Zeile. Diese rot eingefärbten Wörter (immer Farben) sollen dann rausgefiltert und am Ende der Tabelle (von mir gelb eingefärbt) in eine extra Spalte kopiert werden.
Jetzt ist in dem Beispiel hier in Spalte E ganz unten ein dummer Zufall das dort auch in dem Fall die Farbe des Stift Schaftes mit grau beschrieben wird (grün markiert). In dem Fall wäre grau natürlich falsch und pink, weinrot bzw. hellblau korrekt. Das ist natürlich ein Problem, ich weiss nicht was da dann passieren würde, schreibt er beide Farben raus oder bringt ne Fehlermeldung? .. beides wäre nicht schlimm, da ich sowieso am Ende noch nachkorrigieren muss. Es gibt ggf. auch ganze Zeilen in denen gar keine Farbe beschrieben wird, wenn hier nichts, oder ein Falschwert am Ende rauskopiert würde, wäre das dann auch nicht schlimm. Es geht mir nur darum das ich nicht für über 13.000 Artikel einzeln von Hand die Farben rausschreiben muss.

 

Hallo,

ich habe dir in der beiliegenden Excel-Datei eine kleine Farbentabelle namens "tabFarben" eingefügt. Diese wird verwendet, um in den einzelnen Textzeilen nach den Farbbezeichnungen zu suchen.

Verwendet wird diese Tabelle in der ebenfalls enthaltenen benutzerdef. VBA-Funktion
=FarbenAuflisten(Textzeile; TabelleFarben)
die 2 Parameter benötigt: 1.Parameter (Textzeile) enthält den Text, der nach Farben durchsucht werden soll, und der 2.Parameter "TabelleFarben" enthält die vorhin erwähnte Farbentabelle.

Wie sie zu verwenden ist, wird im Arbeitsblatt im Bereich J1:J29 gezeigt (blaue Farbbezeichnungen).

Code:

Public Function FarbenAuflisten(Txt As String, rngFarben As Range) As String
  Dim TxtL As String
  Dim rowFarbe As Range
  Dim strFarben As String, strFarbe As String, strFarbe1 As String
 
  strFarben = ""
  TxtL = LCase(Txt)
 
  For Each rowFarbe In rngFarben.Rows
    strFarbe = LCase(rowFarbe.Cells(1, 1))
    If InStr(TxtL, strFarbe) Then
       strFarben = strFarben & ", " & strFarbe
    Else
       strFarbe1 = LCase(rowFarbe.Cells(1, 2))
       If Len(strFarbe1) Then
          If InStr(TxtL, strFarbe1) Then
            strFarben = strFarben & ", " & strFarbe
          End If
       End If
    End If
  Next rowFarbe
 
  FarbenAuflisten = Mid$(strFarben, 3)

End Function

 

Halo Exl121150,

Zunächst mal vielen vielen Dank für deine Mühe, hätte nie gedacht das es soo aufwendig ist!

Das ist exakt das was ich brauche! ... Leider konnte ich den Code nicht auf die original Datei übertragen, da meldet er Fehler #NAME?

Ich bin einfach hingegangen und habe deine Farbtabelle 1:1 in meine Original Datei eingefügt und dann die zu suchenden Spalten eingetragen (im original sind es ein paar mehr und die beginnen in Zeile 2) , leider klappt das aber nicht. Was muss ich da noch beachten?

=FarbenAuflisten($H2&$I2&$J2&$K2&$L2&M2&N2&O2&P2&Q2&R2&S2&T2;tabFarben)

Das oben wäre der Code so wie er laut deinem Beispiel dann eigentlich bei meiner Tabelle lauten müsste, die Farbtabelle habe ich genau wie bei dir, rechts daneben kopiert. Wo liegt der Fehler?

 

Hallo,

dieser Fehler erscheint, wenn Excel einen Namen nicht findet. Ich nehme an, dass dies wahrscheinlich der Name der Funktion sein wird. Du musst das VBA-Makro in ein allgemeines Codemodul kopieren - nur dann existiert der Funktionsname auch in den Arbeitsblättern. In das allgemeine Codemodul gelangst du, wenn du im Arbeitsblatt die Tastenkombination Alt+F11 drückst. Im Projektfenster musst du unter "Module" ein neues Modul anlegen (zB. "Modul1") und in dieses ist dann der VBA-Code der Funktion - wie hier nachfolgend rechts zu sehen - kopiert werden:


Die Farbtabelle "tabFarben" kannst du als struktur.Tabelle anlegen wie in meiner Datei oder auch nur als gewöhnlichen Zellbereich. Die Funktion benötigt diese struktur.Tabelle bzw. den Zellbereich - so wie in meiner Datei zu sehen.

Ich habe die VBA-Funktion etwas verbessert. Sie lautet jetzt:
FarbenAuflisten1(rngTxt As Range, rngFarben As Range) As String
und sie kann einfacher aufgerufen werden, zB. für die 1. Zeile: =FarbenAuflisten1($A1:$G1;tabFarben)

Code:

Public Function FarbenAuflisten1(rngTxt As Range, rngFarben As Range) As String
  Dim TxtL As String
  Dim rowFarbe As Range, ZelleTxt As Range
  Dim strFarben As String, strFarbe As String, strFarbe1 As String
  Dim lngPos As Long
 
  strFarben = ""
 
  For Each ZelleTxt In rngTxt.Cells
      TxtL = LCase(ZelleTxt.Value)
 
      For Each rowFarbe In rngFarben.Rows
        strFarbe = LCase(rowFarbe.Cells(1, 1))
        lngPos = InStr(TxtL, strFarbe)
     
        If IstLinksKeinBuchstabe(TxtL, lngPos) Then
           strFarben = strFarben & ", " & strFarbe
        Else
           strFarbe1 = LCase(rowFarbe.Cells(1, 2))
           If Len(strFarbe1) Then
              lngPos = InStr(TxtL, strFarbe1)
              If IstLinksKeinBuchstabe(TxtL, lngPos) Then
                strFarben = strFarben & ", " & strFarbe
              End If
           End If
        End If
      Next rowFarbe
  Next ZelleTxt
 
  FarbenAuflisten1 = Mid$(strFarben, 3)

End Function
Private Function IstLinksKeinBuchstabe(txt As String, Ps As Long) As Boolean
  Dim ch As String
  Select Case Ps
    Case 0:
       IstLinksKeinBuchstabe = False
    Case 1:
       IstLinksKeinBuchstabe = True
    Case Else:
       ch = Mid$(txt, Ps - 1, 1)
       IstLinksKeinBuchstabe = Not (ch Like "[A-ZÄÖÜa-zäöüß]")
  End Select
End Function

Die alte Funktion FarbenAuflisten(Txt; rngFarben) ist auch noch enthalten, falls gewünscht.

Nachfolgend die aktualisierte Datei.

 

Es hat funktioniert!!!

Ich danke dir vielmals!!!!

Das Thema kann somit als gelöst geschlossen werden,

Mfg,

Dirk

 

Hallo,

Ich hätte doch nochmal eine Frage an den Exl121150 bitte:

Im Grunde ist alles genau so wie ich es brauche, cool wäre aber noch wenn man definieren könnte das er wörter (farben) die in mehreren Spalten, also mehrfach in einer Zeile vorkommen, nur 1x rausgeben soll.

Aktuell filtert er ja z.b das wort rot so oft raus wie es in einer Zeile vorkommt. In der Ziel Spalte steht dann halt "rot, rot, rot".
Kann man definieren das doppelte begriffe nur 1x rausgefliltert werden?