Knifflige Aufgabe; Zahlen aus Text in Zellen exportieren

[Phi.VBA]

Hallo JoGi,

wenn man in Texten "wühlen" möchte, kann man sich leicht in Ausnahmen verlieren und mit einen umfangreichen, schwer zu wartenden Code enden. In der Liste aller Code habe ich die Hex-Zahlen erkannt (00-FF) und RegEx genutzt. Damit steckt das know-how in nur einer Zeile, dem Pattern. Der Rest ist Standard und war dementsprecht zügig zu programmieren.

Ein Fehler könnte bei Texten wie "1254Abteilung" auftreten, da der Text vor dem Hex-Code nicht geprüft wird. Falls das ein Problem sein sollte, kann man ein "\b" (Wortanfang) voranstellen. Der Aufwand wäre ähnlich einer Neuprogrammierung.

Code:

Sub iPhi()
'Starten im Menü:    Ansicht -> Makro -> iPhi                                                    
'Annahmen: Fehlercodes sind HEX-Codes und es folgt keine Zahl
'Texte mit den Codes ab Zeile 7 in Spalte C, D und E, beliebig viele Zeilen
'falsches Erkennen bei "BAB", "ABS", "FER"

Dim RegEx As Object: Set RegEx = CreateObject("vbscript.regexp")
Dim Ar() As String, Res() As String
With RegEx
   .Pattern = "[0-9A-F]{2}\D"                                
   .Global = True
   .ignorecase = False
End With
With ActiveSheet                                                      
   lr = .Cells(Rows.Count, 3).End(xlUp).Row
   Arr = Range("C7:E" & lr)
   ReDim Ar(UBound(Arr))                                              
   For i = 1 To UBound(Ar)
       Ar(i - 1) = " " & Arr(i, 1) & " " & Arr(i, 2) & " " & Arr(i, 3) & " "
   If RegEx.test(Ar(i - 1)) Then
           Set RR = RegEx.Execute(Ar(i - 1))
       ReDim Res(RR.Count - 1)
           For r = 0 To RR.Count - 1
'jeder Code nur einmal
           If InStr(1, Join(Res, ", "), Left(RR(r), 2)) = 0 Then Res(r) = Left(RR(r), 2)
           Next r
           With Cells(i + 6, "F")
               .Value = Trim(Replace(Join(Res, ", "), ", ,", ",", , True))
           Do While Right(.Value, 1) = ","
               .Value = Trim(Left(.Value, Len(.Value) - 1))
           Loop
           End With
   End If
   Next i
End With
End Sub

Der Wunsch, keine doppelten Codes pro Zeile zu zeigen, war ähnlich aufwändig wie der Rest.

Bis zu mehreren 10.000 Zeilen sollte der Code nicht länger als wenige Sekunden benötigen, falls es ca 1 Mio Zeilen wäre, kann man das Zurückschreiben in die Tabelle noch beschleunigen.

mfg

[JoGi98990]

Hallo zusammen, 

sorry, das ich mich letzte Woche nicht mehr gemeldet habe. 

Erstmal vielen Dank für eure kompetente Hilfe. Dank euch habe ich jetzt einen passenden Code der das macht was ich wollte.

@ Andre und Phi.VBA vielen Dank!! 

Ich werde damit jetzt erstmal weiter arbeiten und falls weitere Optimierungen notwendig sind komme ich auf euch zurück  

Grüße an alle, 
Jogi

[JoGi98990]

Hallo liebe Excel-Experten  

Wie Mitte diesen Jahres habe ich mein Projekt, Informationen aus einem Kurztext mit beinhalteten Fehlercodes auszulesen fortgesetzt. 

Ich werde darüber voraussichtlich eine schriftliche Arbeit innerhalb meines Studiums verfassen. 

Jedoch sind die Anforderungen an den Quellcode (das Auslesen der Informationen aus der Zelle mit dem Text) gewachsen. 

Zur Erinnerung: 
Ich habe eine Excel-Datei aus SAP mit Techniker-Einträgen. Diese beinhaltet mehrere tausend (>10.000) Zeilen. 
In einer Spalte (Name: Kurztext) stehen Kurzinformationen zum Auftrag (inkl. Fehlercodes und Schlagwörter).

Beispiel: "An der Feldanlage Müller ist der Fehler 99, 48, A9, 8C aufgetaucht. Der Verdichter muss getauscht werden."
Ausgabe: 99, 48, A9, 8C

Mein Ziel ist es, diese Fehlercodes aus dem Text herauszulesen und zu Analysieren -> Analyse via Tableau (hier habe ich bereits ein 3 Monate Studium zur Datenanalyse absolviert). 
Die Fehlercodes stehen mitten im Text und nur in einer Spalte/Zelle. 

Hierzu habe ich bereits einen Quellcode mitte des Jahres erhalten. Dieser war super und hat funktioniert -> jedoch kann ich diesen nicht lesen (soweit nicht schlimm, ich kann ihn nur nicht anpassen und erweitern ) 

Zudem haben sich die Anforderungen an den Quellcode erweitert-> Mehr Fehlercodes und Neu -> auch Wörter die Ausgelesen werden sollen. 
Die Fehlercodes stehen in der Beispieldatei (xls. Anhang)-> sheet "Fehlercodes" -> diese habe ich farblich gekennzeichnet, zur besseren Übersicht. 

>> Die Informationen mit den Fehlercodes stehen jetzt immer nur noch in einer Spalte (zuvor 3 Spalten) die da heißt -> "Kurztext". 

Mein Hilferuf bittet um folgendes : 
Ich habe alle Anforderungen an den Quellcode in dem Word-Dokument niedergeschrieben und eine Excel-Datei erzeugt wo der Code ausprobiert werden kann. 

Kann mir bitte jemand helfen, den bestehenden Quellcode (kann auch ein neuer sein) anzupassen auf die Anforderungen in der doc. Datei? 

Hierfür wäre ich wirklich sehr dankbar. 

Grüße, 
Jogi

[JoGi98990]

ps.: ich habe das Thema von "erledigt" wieder auf "nicht erledigt gesetzt

Ich würde mich freuen wenn mir jemand bei meinem letzten Post helfen kann. 

Grüße, 
Jogi :)

[schauan]

Hallöchen,

erst mal eine Zwischenfrage. Was kann alles als Trennzeichen auftreten?

[snb]

Ich ahnte nicht das SAP solche inkonsistende Daten ausspucken konnte.
Das Beginn der Lösung fängt an in SAP, nicht in Excel oder VBA.
So zurück zur SAP-spezialisten. Mann sollte solche 'rubbish' nicht anfassen.

[JoGi98990]

Du meinst wahrscheinlich die Trennzeichen mit der die Fehlercodes in der Kurztextspalte abgegrenzt werden gell?

Die Techniker (jeder schreibt halt den Inhalt etwas anders auf) benutzen meistens die Trennzeichen :

Komma (,)
Semikolon (;)
Querstrich (/)
oder auch einfach nur ein Leerzeichen (_) -> also keinen Unterstrich sondern eine Leertaste als Leerzeile 

Beispiele: 
A9, AA, A0
A9;18,29
10 20 17
10/20/A9

Grüße, 
JoGi

[JoGi98990]

Hallo snb, 

ich bin ganz auf deiner Seite das es aus Sicht von Analytics intelligenter wäre die Daten gleich in SAP zu systematisieren. 
Jedoch wird das SAP (CS) System für die Techniker-Disopsition und als Auftragsmanagement System verwendet. 
Es gibt ca. 200 Techniker die hier Informationen von Aufträgen an Feldanlagen eingeben. 

Mein Ziel ist es mit advanced und predictive Analytics Methoden Fehlerbilder und Zusammenhänge zu erkennen und diese transparent aufzuzeigen. 

Dafür brauche ich eine Vorfilterung der Daten. Dies geht nur indem ich mir die Daten aus SAP als Excel ziehe und dort aufbereite. 

Grüße, 
JoGi

[schauan]

Hallöchen,

das bringt erst mal einiges, aber nicht alles. 320 Liter, den 20.07. und wenn's mal auftaucht 10 m oder 10_m oder 10m usw. musst Du aber selbst nalysieren ...

Code:

Sub test()
Dim arrZeilen, strCodes$, arrTmp
Dim iCnt%, jCnt%
'Daten aufnehmen
arrZeilen = Sheets("Daten").Range("G1:G28")
'Schleife ueber alle Zeilen
For iCnt = 1 To UBound(arrZeilen)
  strCodes = ""
  'Trennzeichen ersetzen
  arrZeilen(iCnt, 1) = Replace(Replace(Replace(Replace(arrZeilen(iCnt, 1), ",", "|"), "/", "|"), " ", "|"), ";", "|")
  Do While InStr(1, arrZeilen(iCnt, 1), "||") > 0: arrZeilen(iCnt, 1) = Replace(arrZeilen(iCnt, 1), "||", "|"): Loop
  'Array bilden
  arrTmp = Split(arrZeilen(iCnt, 1), "|")
  For jCnt = LBound(arrTmp) To UBound(arrTmp)
    'wenn die linken beiden Zeichen numerisch sind, dann abtrennen
    If IsNumeric(Left(arrTmp(jCnt), 2)) Then
      arrTmp(jCnt) = Left(arrTmp(jCnt), 2) 'Trennung der ersten beiden Zeichen wenn numerisch
      strCodes = strCodes & arrTmp(jCnt) & ","
    'Vergleich mit Codes aus dem Bereich B1:K24
    ElseIf WorksheetFunction.CountIf(Sheets("Fehlercodes").Range("B1:K24"), arrTmp(jCnt)) > 0 Then
      strCodes = strCodes & arrTmp(jCnt) & ","
    End If
  Next
  'Ergebnis eintragen
  Cells(iCnt, 8) = strCodes
Next
End Sub

[JoGi98990]

Hi Schauan, 

wow, erstmal vielen Dank für deine Zeit und Arbeit die du da investiert hast. Der Code ist ja doch länger als ich dachte. 

Ich habe ihn mal anhand der Beispieldatei ausprobiert. 
Habe leider ein paar Fehler festgestellt bzw funktioniert er bei mir nicht so wie es sein sollte  . Ich habe diese mal markiert und korrigiert, so wie es sein müsste. (Vllt. ist das auch nicht so gut erklärt gewesen in meiner Word-Datei, das tut mir leid.). Ich denke aber das es hier jetzt klar wird. 

Meinst du es ist möglich, das du dir das nochmal anschauen kannst? Du würdest mir echt weiterhelfen. 

Grüße, 
JoGi