Knifflige Aufgabe; Zahlen aus Text in Zellen exportieren

[JoGi98990]

Hallo zusammen, 

ich habe eine knifflige Frage, die entweder via SQL-Code (Makro) oder einer Formel in Office-Excel gelöst werden kann...
Ich bitte um eure Mithilfe, da ich selbst nicht so tief im Formel u. SQL-Thema stecke....ich bin mir jedoch sicher, dass bei der Flut an Möglichkeiten heutzutage dies ein Klacks für Excel-Bros sein sollte

Also, es geht um folgendes:

Ich möchte Fehlercodes aus einem Text aus Zellen exportieren. Hier die 1. Hürde:

Es gibt 5 Zellen mit Text, in denen Fehlercodes stehen können. 

z.B. Zelle 1)
Es wurde der Fehler 82 zusammen mit 50, 76 festgestellt

Zelle 2) 
Es wurde zudem A7 festgestellt. 

Als Ergebnis möchte ich in einer Zelle folgende Lösung finden: 82, 50, 76, A7 
Dabei spielt die Reihenfolge keine Rolle. Die Zahlen (inkl. A7) könnten auch in 4 getrennten Zellen (alleine) stehen, auch ok. 

Hürde 2: 
Die Formel oder der SQL-Code darf nicht nur die Zahlen aus dem Text exportieren, sondern muss auch (wie in dem Beispiel zu sehen) Codes wie z.B. A7, B5, C4 herausfiltern. Es wäre ok, wenn ich die Codes per Hand in die Formel oder den SQL-Code eintragen müsste um die es sich dreht. (stelle ich mir z.B. so vor: WENN in ZELLE A1= A7, B5, C4, DANN ... ) 

Vielleicht vereinfacht das den SQL-Code oder die Formel: Die Fehler-Codes im Text sind immer nur zweistellig also z.B. 82, A7, C4, 50, 05, 02, 04, 0A, 1A usw...

Das wars eigentlich schon^^

Könnt ihr mir weiterhelfen? Eine copy paste SQL-Code/Formel wo ich nur noch die Codes eintragen und die Zellen markieren müsste mit dem Inhalt des Textes und den Codes wäre genial. 

Vllt. für diejenigen, die sich fragen wozu ich das brauche --> Ich möchte eine Auswertung fahren, welche Fehlercodes zusammen am häufigsten auftreten. (Händisch leider nicht machbar). 

Grüße an euch Excelbros
JoGi98990

[Fennek]

Hallo,

eine Beispieldatei mit 5-10 Zeilen würde es anschaulicher machen!

Die Frage war nach SQL oder Formel, wie ist es mit VBA?

mfg

[schauan]

Hallöchen,

gibt es eigentlich auch A76 - also einen Buchstaben mit 2 Zahlen? Sind die Fehlercodes maximal 2-stellig? Werden einstellige Codes mit führender 0 geschrieben? Gibt es Fehlercodes mit Trennzeichen, z.B. 44.1 oder 45-2?

Im Prinzip könnte man per Makro die Texte von Satzzeichen bereinigen, an den Leerstellen trennen und schauen, wo eine Zahl drin steckt Hier mal ein Beispiel für die Zelle A1 mit maximal zweistelligen Fehlercodes:

Code:

Sub trennen()
'schauan
'Variablendeklarationen
'Text, Variant, Integer
Dim strZahl$, arrText, iCnt%
'Zellinhalt übernehmen, Komma durch Leerzeichen ersetzen
strZahl = Replace(Cells(1, 1), ",", " ")
'Text aufteilen anhand Leerzeichen
arrText = Split(strZahl, " ")
strZahl = ""
'Schleife über Textbestandteile
For iCnt = 0 To UBound(arrText)
 'Wenn die Textlänge ohne Leerzeichen vorn und hinten <= zwei ist, dann
 If Len(Trim(arrText(iCnt))) <= 2 Then
   'Wenn ohne Leerzeichen links eine Zahl steht
   If IsNumeric(Left(Trim(arrText(iCnt)), 1)) Then
     'Fehlercode ohne Leerzeichen vorn und hinten uebernehmen
     strZahl = strZahl & arrText(iCnt) & ","
   'Ende Wenn ohne Leerzeichen links eine Zahl steht
   End If
 'Ende Wenn die textlänge <= zwei ist, dann
 End If
'Ende Schleife über Textbestandteile
Next
'Ausgabe der Fehlercodes
MsgBox strZahl
End Sub

[JoGi98990]

Hallo zusammen, 

erstmal vielen Dank das ihr euch überhaupt gemeldet habt  

zu den Fragen:

--> Ich habe mal eine Orginal-Datei (etwas bereinigt; aus Datenschutzgründen) in den Anhang gesetzt. Die Datei hat eigentlich >500 Zeilen...
Gelbe Zellen (C, D, E) = so kommen die Daten rein
Blaue Zellen = so hätte ich gerne das Ergebnis, denn dann kann ich via Pivot super weiterarbeiten

VBA geht natürlich auch...

Zu deinen Fragen André:
gibt es eigentlich auch A76 - also einen Buchstaben mit 2 Zahlen? --> Nein, immer zweistellig --> 07, A9, C2, 49, etc...
Sind die Fehlercodes maximal 2-stellig? --> ja
Werden einstellige Codes mit führender 0 geschrieben? --> es gibt keine einstelligen Codes, die Codes können jedoch mit "0" anfangen --> 07, 05 z.B., so werden diese auch vom Techniker eingetragen.
Gibt es Fehlercodes mit Trennzeichen, z.B. 44.1 oder 45-2? --> nein 

Wie in der Beispiel-Datei zu sehen ist, kommt es auch vor das hier mal "05EEV" steht. Das kann häufiger der Fall sein. Dann müsste der Code so schlau sein, dass er erkennt, dass hier ein "Wort" mit einer "Zahl" zusammen hängt und dann die ersten zwei Silben abschneiden. Also aus  --> 05EEV --> 05 EEV --> 05 ist der Fehler, brauche ich und soll ausgegeben werden in Zelle --> EEV brauche ich nicht...
Wenn so etwas vorkommt, steht der Fehler, hier 05 immer vor dem "Wort" also niemals EEV05 --> immer 05EEV oder 07EEV oder 48Temperatursensor, so in der Form. 

(Eine ziemliche Herausforderung und knifflig, ich weis... ... zumindest kann ich mir vorstellen, dass das nicht so easy zu programmieren ist....ich hoffe auf euer Können :) ). 

Freue mich weiter mit euch am passenden Code zu arbeiten.



Grüße, 
JoGi98990  

[schauan]

Hallöchen,

hier wäre mal ein Beispiel für D8. Die 10 gehört doch auch dazu, oder?

Code:

Sub trennen()
'schauan
'Variablendeklarationen
'Text, Variant, Integer
Dim strZahl$, arrText, iCnt%
'Zellinhalt übernehmen, Kommata und Semikola durch Leerzeichen ersetzen
strZahl = Replace(Replace(Cells(8, 4), ",", " "), ";", " ")
'Text aufteilen anhand Leerzeichen
arrText = Split(strZahl, " ")
'Text zuruecksetzen
strZahl = ""
'Schleife über Textbestandteile
For iCnt = 0 To UBound(arrText)
'Texte auf linke zwei Zeichen kuerzen
arrText(iCnt) = Left(Trim(arrText(iCnt)), 2)
  'Wenn ohne Leerzeichen links eine Zahl steht
  If IsNumeric(Left(Trim(arrText(iCnt)), 1)) Or IsNumeric(Right(Trim(arrText(iCnt)), 1)) Then
    'Fehlercode ohne Leerzeichen vorn und hinten uebernehmen
    strZahl = strZahl & arrText(iCnt) & ","
  'Ende Wenn ohne Leerzeichen links eine Zahl steht
  End If
'Ende Schleife über Textbestandteile
Next
'Ausgabe der Fehlercodes
MsgBox strZahl
End Sub

[JoGi98990]

Hallo André 

wow...der Code ist schonmal sehr gut! Ich habe ihn mal ausprobiert (blicke in der schnelle noch nicht so ganz durch) aber es scheint schonmal in die richtige Richtung zu gehen wie ich mir das vorstelle.  --> Danke! Ich bekomme als Ausgabe jedoch immer "A9,10,5D,48" in einem Fenster zu sehen wenn ich das Makro ausführe...mach ich da vllt. etwas falsch? (Ich habe das Makro angelegt, --> zurückgewechselt in das Tabellenblatt --> 3 Zellen Markiert (C7 - E7) wo die Fehler rausgelesen werden sollen --> Makro ausgeführt. --> Ergebnis: Es öffnet sich ein kleines Fenster mit der Ausgabe "A9, 10, 5D, 48". Richtig wäre: AA, D6, 3E, 18. --> Ich habe einen Screenshot davon gemacht (Anhang).

Um das ganze nochmal zu konkretisieren habe ich in den Anhang die  Excel-Datei gehängt mit allen Fehlercodes die auftreten können und allen Hürden, die der Quell-Code überwinden muss (Stichpunkte). Siehe Excel-file:"Fehlercodes mit Beispieldatei"

Danke für eure gemeinsame Hilfe.  

Vllt. ist es möglich, dass Ihr die Excel-Datei mit dem Makro-Inhalt hier als Anhang wieder hochpostet wenn der Code funktioniert? :) 

Grüße, 
JoGi98990

[Phi.VBA]

Hallo JoGi,

unter der Annahme, dass Du einen PC (d.h. keinen MAC) nutzt, kann ich mir eine recht kompakte Lösung vorstellen.

mfg

[schauan]

Hallöchen,

Code:

mach ich da vllt. etwas falsch

schaue nochmal in meine letzte Antwort. Dort steht was von Beispiel und  D8...

Die Frage nach der 10 hast Du noch nicht beantwortet. Wieso ist die kein Fehlercode?

Bei Deinen Formulierungen in D und E gibt es das Problem, dass wohl nicht alle numerischen Angaben Fehlercodes enthalten, z.B. die 1935l/h ergeben bestimmt nicht code 19. Stell Dir mal vor, dass dort einer schreibt, das der Fehler zuletzt am 14.01.2017 aufgetreten ist, 15 Minuten nach 16 Uhr, als 12 Mitarbeiter in einem Bus für 40 Personen 30 kmh über der erlaubten Geschwindigkeit von 10 kmh das Werk in Richtung A9 verließen. Kein Fehlercode im Satz, aber die Ausgabe wäre 14, 15, 16, 40, 30, 10, A9

Mit dem Code würdest Du dann schon mal die Daten aus Spalte D auswerten und übernehmen.

Code:

Sub trennen()
'schauan
'Variablendeklarationen
'Text, Variant, Integer
Dim strZahl$, arrText, iCnt%, icnt1%
'Startzeile festlegen
icnt1 = 7
'Schleife ab D7 solange bis in Spalte D eine leere Zelle kommt
Do While Cells(icnt1, 4) <> ""
 'Zellinhalt übernehmen, Kommata und Semikola durch Leerzeichen ersetzen
 strZahl = Replace(Replace(Cells(icnt1, 4), ",", " "), ";", " ")
 'Text aufteilen anhand Leerzeichen
 arrText = Split(strZahl, " ")
 'Text zuruecksetzen
 strZahl = ""
 'Schleife über Textbestandteile
 For iCnt = 0 To UBound(arrText)
 'Texte auf linke zwei Zeichen kuerzen
 arrText(iCnt) = Left(Trim(arrText(iCnt)), 2)
   'Wenn ohne Leerzeichen links eine Zahl steht
   If IsNumeric(Left(Trim(arrText(iCnt)), 1)) Or IsNumeric(Right(Trim(arrText(iCnt)), 1)) Then
     'Fehlercode ohne Leerzeichen vorn und hinten uebernehmen
     strZahl = strZahl & arrText(iCnt) & ","
   'Ende Wenn ohne Leerzeichen links eine Zahl steht
   End If
 'Ende Schleife über Textbestandteile
 Next
 'Codes uebernehmen
 Cells(icnt1, 6) = strZahl
 'Zeilenzaehler hochsetzen
 icnt1 = icnt1 + 1
'Ende Schleife ab D7 solange bis in Spalte D eine leere Zelle kommt
Loop
'Ausgabe der Fehlercodes
'MsgBox strZahl
End Sub

[Phi.VBA]

Wenn so etwas vorkommen würde

das der Fehler zuletzt am 14.01.2017 aufgetreten ist, 15 Minuten nach 16 Uhr, als 12 Mitarbeiter in einem Bus für 40 Personen 30 kmh über der erlaubten Geschwindigkeit von 10 kmh das Werk in Richtung A9 verließen.

wäre die Aufgabe nicht lösbar (programmierbar).

(Aber: ein Datum könnte man filtern)

Nachfrage: Wieviele Zeilen können es werden? Falls es (weit!) über 10.000 Zeilen werden können, müßte der Code "schnell" sein, d.h. anderst programmiert.

[schauan]

Hallöchen,

Doppelbuchstaben habe ich in meinem Code übrigens auch nicht berücksichtigt. Das wäre auch nicht einfach z.B. bei AAlen, ABba, AChen, usw.

Zum Beschleunigen würde ich natürlich eher Arrays verwenden als alle Zeilen durchzugehen.

Übrigens könnte man auch schauen, ob. z.B. Uhr danach kommt oder spezielle Sonderzeichen, oder kmh bzw. km/h und andere Einheiten, usw. Genauso könnte man bei zwei Buchstaben ein Wörterbuch zum Vergleich anlegen. Wird sicher nicht 100%ig, aber man könnte das Ergebnis immer weiter in die Richtung bringen.

@ Jogi, man kann mit dem Hintergrundwissen, dass unter diesen Bedingungen wohl keine 100%ige Lösung erreicht werden kann, versuchen, sich mit einem Stand zu arrangieren, der manuelle Nacharbeit benötigt. Wenn der Ansatz jetzt besser passt, können wir ja mit Doppelbuchstaben und der Spalte E usw. weiter machen

Ich könnte mir aber auch eine Lösung vorstellen, wo Du dem Anwender in mehreren Spalten die Auswahl von Fehlercodes anbietest und er dann einen Text als Beschreibung dazu gibt. Du würdest z.B. den Bereich der Spalten G bis K (oder mehr, je nachdem, wie viele Fehler maximal in einem Auftrag zu erwarten sind) zur Eintragung anbieten. Mit einer Datengültigkeit kannst Du sogar dem Anwender in diesen Zellen die Codes zur Auswahl anbieten.