Analyse inkonsistent formatierter Textdaten mit Excel

345
smj7v3

Ich habe Schwierigkeiten, Text aus einer Tabelle zu analysieren. Ich habe eine Tabelle mit Hunderten von Datensätzen, die dem folgenden Beispiel ähneln (dies ist alles in einer Zelle):

7431340 03 POOLS E TECHNOLOGIES INC 90 NEW ENGLAND DRIVE 16 0.00 6,900 7,060

Ich muss jedes der folgenden in separate Zellen parsen: 

[7431340] [03 POOLS E TECHNOLOGIES INC] [90 NEW ENGLAND DRIVE] [7,060]

oder wie es in einer Excel-Tabelle erscheinen würde

End result in Excel, showing first number in column A, number plus company name in column B, street address in column C, and last number in row in column D

Ich habe das Problem, dass die Daten für jeden Datensatz inkonsistent sind. Mehr Beispiele:

7170258 111 HARBOR POINT OWNER LLC 115 TOWNE STREET 16 0.00 189,200 196,730 4469000 4 KIDZ INC 62 SOUTHFIELD AVENUE 16 0.00 3,000 9,500 6369875 3 HERMANOS LLC 912 EAST MAIN STREET 16 0.00 4,640 4 640

Ich möchte so viele manuelle Manipulationen wie möglich vermeiden, aber ich fürchte, ich habe kein Glück.

Wie kann ich die Daten analysieren, wenn das Format nicht konsistent ist?

0
Das eine Beispiel, das Sie angegeben haben, wirft zwei numerische Werte nach der Adresse ab. wird das konsistent sein? Sieht so aus, als würde Regel 1 die erste numerische Zeichenfolge in eine Zelle ziehen, Regel zwei ist, die Zahlen unmittelbar danach zu nehmen und zum Firmennamen hinzuzufügen, um in eine zweite Zelle zu gelangen. Regel drei ist, die Adresse in eine dritte Zelle zu verschieben. Regel vier übernimmt den letzten durch Komma formatierten Wert in eine vierte Zelle, wobei drei numerische Werte zwischen ihm und dem Ende der Straßenadresse verworfen werden. Ist das korrekt? K7AAY vor 5 Jahren 1
Wie @ K7AAY feststellt, haben Sie die Regeln für die Aufteilung der Daten in die vier Zellen nicht erläutert. Bearbeiten Sie Ihre Frage, um diese Informationen hinzuzufügen. Ich würde noch ein paar Beispiele helfen. Blackwood vor 5 Jahren 0
@ K7AAY Das Parsen der ersten und letzten numerischen Zeichenfolge ist immer konsistent, was der einfache Teil ist. Das eigentliche Problem, das ich habe, ist die Analyse der beiden Informationen in der Mitte. Hier liegen die Inkonsistenzen. smj7v3 vor 5 Jahren 0
Wie ich schrieb, sollte Ihre Lösung funktionieren, wenn Ihre Daten wirklich repräsentativ sind. Wenn dies nicht der Fall ist, müssen Sie Beispiele dafür angeben, wo es versagt, um zu sehen, ob es ein brauchbares Muster gibt. Ron Rosenfeld vor 5 Jahren 0

2 Antworten auf die Frage

0
Ron Rosenfeld

Wenn Ihre Beispiele wirklich repräsentativ sind, gibt es ein Muster, das mit regulären Ausdrücken extrahiert werden kann. Dies kann in Excel mit VBA implementiert werden.

Muster:

  • Anfang der Zeichenfolge
  • Ziffernfolge gefolgt von einem Leerzeichen
  • Teilzeichenfolge, die mit einer oder mehreren Ziffern beginnt und keine anderen Ziffern enthält
  • einen zweiten Teilstring, der mit einer oder mehreren Ziffern beginnt und keine weiteren Ziffern enthält
  • mehrere durch Leerzeichen getrennte Ziffern-Komma-Gruppen mit optionalem Komma (das letzte zurückgeben)

Alle Ihre Beispiele zeigen dieses Muster. Hier ist eine benutzerdefinierte Funktion, die das erledigt:

Option Explicit Function extrAddressPart(sAddr As String, lPart As Long) Dim RE As Object, MC As Object Const sPat As String = "^(\d+)\s+(\d+\D+)\s+(\d+\D+)\s+.*\s+([\d,]+)$"  Set RE = CreateObject("vbscript.regexp") With RE .Pattern = sPat .MultiLine = True .Global = False If .Test(sAddr) = True Then Set MC = .Execute(sAddr) extrAddressPart = MC(0).submatches(lPart - 1) End If End With End Function

Wenn Ihr Beispiel nicht wirklich repräsentativ ist, funktioniert diese Lösung natürlich nicht.

Excel kann keine regulären Ausdrücke in Suchen und Ersetzen ausführen? Ich benutze LibreOffice Calc und kann es. Moss vor 5 Jahren 0
Fügen Sie einige Beispiele hinzu, wie die Funktion ,, verwendet wird, und ein weiteres OP hat gemischte Trennzeichen. Wird dies für alle funktionieren! Rajesh S vor 5 Jahren 0
@RajeshS Welche Probleme haben Sie mit der Funktion? In seinen Beispielen ist das einzige Zeichenbegrenzungszeichen ein ``. Ron Rosenfeld vor 5 Jahren 0
@RonRosenfeld, überprüfe meine Lösung dort Ich habe gezeigt, wie man die Funktion verwendet, `= ReplaceAndSplit (A2,", ")`. Ich persönlich habe keine Probleme, aber für andere kann es so sein, dass ich geschrieben habe! Rajesh S vor 5 Jahren 0
@RajeshS In Ihrer Lösung erwähnen Sie verschiedene Trennzeichen. Das einzige Trennzeichen in den Beispielen ist aund wenn dies verwendet wird, teilt Ihre UDF die Zeichenfolge lediglich in einzelne Wörter auf. Sehen Sie sich meinen Screenshot an, um zu verstehen, wie seine Beispiele aussehen. Ron Rosenfeld vor 5 Jahren 0
@RonRosenfeld, Ja wahr, ich habe gezeigt, wie man mit verschiedenen Trennzeichen umgeht, da OP auch Sätze innerhalb von `[]`, `Comma` &` Space` hat. Rajesh S vor 5 Jahren 0
@RajeshS Aber das EINZIGE Trennzeichen, das er in seinen Beispielen verwendet, ist ein Leerzeichen, und Ihre UDF teilt die Zeichenfolge nur in einzelne Wörter auf. (Die Kommas in seinem Beispiel sind Tausendertrennzeichen; und die Klammern sind lediglich Anhaltspunkte dafür, wie er die Zeichenfolge analysieren möchte). Vielleicht sollten SIE zeigen, wie Ihre Lösung an HIS-Beispielen funktioniert, vielleicht mit einem Screenshot wie ich. Ron Rosenfeld vor 5 Jahren 0
Lassen Sie uns [diese Diskussion im Chat fortsetzen] (https://chat.stackexchange.com/rooms/84180/discussion-between-rajesh-s-and-ron-rosenfeld). Rajesh S vor 5 Jahren 0
0
Rajesh S

Da Sie sich mit mehreren Trennzeichen beschäftigen, möchte ich Ihnen in diesem Fall zwei mögliche Lösungen vorschlagen.

Lösung 1 (VBA-Makro):

Function ReplaceAndSplit(ByRef Text As String, ByRef DelimChars As String) As String() Dim DelimLen As Long, Delim As Long Dim strTemp As String, Delim1 As String, Arr() As String, ThisDelim As String strTemp = Text Delim1 = Left$(DelimChars, 1) DelimLen = Len(DelimChars) For Delim = 2 To DelimLen ThisDelim = Mid$(DelimChars, Delim, 1) If InStr(strTemp, ThisDelim) <> 0 Then _ strTemp = Replace(strTemp, ThisDelim, Delim1) Next ReplaceAndSplit = Split(strTemp, Delim1) End Function

Wie es funktioniert:

  1. Geben Sie diesen Code zusammen mit dem Datenblatt als Modul ein.

  2. Schreibe diese Formel in die Wunschzelle und fülle sie. Richtig, bis nötig, drücke F2und beende mit Ctrl+Shift+Enter.

    {=ReplaceAndSplit(A2,",")}

Hinweis:

  1. ","ist für CommaTrennzeichen.
  2. Es sollte " "für sein Space.
  3. "[ ]"für Brackets.

Lösung 2 (Excel-Formel):

=SUBSTITUTE(TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",256)),(COLUMNS($A:A)-1)*255+1,255)),"]","")

Hinweis:

Sie müssen diesen Teil der Formel entsprechend dem Trennzeichen bearbeiten, bevor Sie ihn ausführen, z.

  1. SUBSTITUTE($A1,","für Commaals Trennzeichen.
  2. SUBSTITUTE($A1," "für Spaceals Trennzeichen.
  3. SUBSTITUTE($A1,"["für Bracketals Trennzeichen.
Ich denke, Sie missverstehen seine Beispiele. Seine zweite Zeile mit den Klammern "[]" zeigt, wie die erste Zeile in Zellen aufgeteilt werden soll. Und die Kommas sind ein Tausendertrennzeichen; kein Trennzeichen Ron Rosenfeld vor 5 Jahren 0
@RonRosenfeld ,, darüber hat OP geschrieben ,, `Ich muss jedes der folgenden Elemente in separate Zellen parsen:` und OP möchte Daten innerhalb von [[]] in separaten Zellen place platzieren Rajesh S vor 5 Jahren 0
Nun, einer von uns versteht eindeutig nicht seine Beispiele. Ron Rosenfeld vor 5 Jahren 0
@Ron Rosenfeld mag sein, also lass das OP besser entscheiden ☺ Rajesh S vor 5 Jahren 0
@RonRosenfeld Sie haben eine korrekte Interpretation. Die Klammern repräsentieren die Zelle. Ich dachte, das wäre eine gute visuelle Darstellung. smj7v3 vor 5 Jahren 0
Ein Screenshot des Excel-Arbeitsblatts wurde hinzugefügt, um basierend auf der Beschreibung des OPs zu zeigen, wie das Beispiel aussehen würde. K7AAY vor 5 Jahren 0

Verwandte Probleme