☰

Wie teile ich eine Zahl in einer einzelnen Zelle ohne Kommas auf und füge sie in mehrere Zeilen ein?

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-access
Wie teile ich eine Zahl in einer einzelnen Zelle ohne Kommas auf und füge sie in mehrere Zeilen ein?

Ich möchte also Folgendes tun: Ich habe eine Tabelle mit einer Zelle, die eine Zahlenfolge enthält (einige sind länger als andere). Ich muss alle acht Zahlen extrahieren und in einzelne Zeilen einfügen.

So sieht die aktuelle Tabelle aus:

IFIELD1     IFIELD2   IFIELD3     IFIELD4    IMAGELIST
05/30/2009  ERMS      100 Day     L. C.      0000001200000013
05/30/2009  ESE       20 Day      J. R.      0000001400000015
05/30/2009  IHS       100 Day     H. W.      00000016
05/30/2009  LMS       100 Day     D. E.      0000001700000018

Das hier ist, was ich suche:

IFIELD1     IFIELD2   IFIELD3   IFIELD4   IMAGELIST
05/30/2009  ERMS    100 Day     L. C.     00000012
05/30/2009  ERMS    100 Day     L. C.     00000013
05/30/2009  ESE     20 Day      J. R.     00000014
05/30/2009  ESE     20 Day      J. R.     00000015
05/30/2009  IHS     100 Day     H. W.     00000016
05/30/2009  LMS     100 Day     D. E.     00000017
05/30/2009  LMS     100 Day     D. E.     00000018

Einige Zahlensätze bestehen aus bis zu 30 oder mehr 8er-Gruppen in einer Zeile. Dabei müssen über 1000 Datenzeilen getrennt werden. Gibt es irgendeinen Code, der diesen Vorgang weniger entmutigend macht? Ich verwende derzeit Links, Mitte und Rechts, aber ich werde Jahre brauchen, um damit fertig zu werden. Die Arbeitsblätter werden aus Access entfernt. Wenn es also eine einfachere Möglichkeit gibt, sie dort auszufüllen, bin ich für Vorschläge offen. Das Endprodukt muss in einer CSV-Datei gespeichert werden. Außerdem muss ich alle 8 Ziffern im fertigen Produkt behalten, bevor ich es als CSV-Datei speichere, damit ich den Link zum Speicherort des Bildes erstellen kann. Vielen Dank für Ihre Hilfe!

Antwort1

In Access gibt es eine recht einfache Lösung.

  1. Erstellen Sie eine Tabelle, die nichts anderes als eine Liste von Zahlen ist: 1, 2, 3, 4, ... nwobei ndie größte Anzahl von Elementen ist, die jemals in Ihrer Tabelle enthalten sein würde IMAGELIST. Es ist jedoch nicht sehr aufwendig, die Tabelle größer als nötig zu machen, also erhöhen Sie sie auf 50 oder so.
  2. Erstellen Sie eine Abfrage, die Ihre Originaltabelle und die Zahlentabelle einbeziehtohne FugenWenn Sie dies SELECT *jetzt tun würden, würden Sie nKopien aller Datensätze in Ihrer Originaltabelle erhalten.
  3. Anstatt das IMAGELISTFeld einzufügen, fügen Sie eine Formel hinzu, die die MIDFunktion IMAGELISTbasierend auf dem Wert aus Ihrer Zahlentabelle für das Feld ausführt.
  4. Filtern Sie, um nur Datensätze anzuzeigen, bei denen die Formel keine leere Zeichenfolge zurückgibt. Dies bedeutet, dass ein Datensatz mit IMAGELIST24 Zeichen nur dreimal angezeigt wird, anstatt nmit einer Reihe von leeren Datensätzen.

Hier ist das SQL für diese Abfrage:

SELECT Table1.ID, Table1.IFIELD1, Table1.IFIELD2, Table1.IFIELD3, Table1.IFIELD4, Table2.Number, Mid([IMAGELIST],([Number]-1)*8+1,8) AS ImageListParse
FROM Table1, Table2
WHERE Mid([IMAGELIST],([Number]-1)*8+1,8)<>""
ORDER BY Table1.ID, Table2.Number;

In meinem schnellen Modell Table1ist Ihre Originaltabelle Table2die Zahlentabelle , Numberdas einzige Feld darin Table2und es sind nur die Zahlen 1-n, und ich habe das Autonummerierungsfeld IDnur Table1zum Sortieren hinzugefügt.

Dabei wird davon ausgegangen, dass jedes Element IMAGELISTgenau 8 Zeichen lang ist. Bei unterschiedlicher Länge funktioniert dieser Ansatz nicht.

Antwort2

Mit den Excel-Arbeitsblattfunktionen ist das nicht allzu schwer. Nehmen wir an, Ihre Daten sind Sheet1mit Überschriften in Zeile 1 versehen. Erstellen Sie Sheet2. Füllen Sie es wie folgt aus:

  • A1→=Sheet1!A1
    • und ziehen Sie nach rechts, um alle Spalten abzudecken (also durch Column  E, basierend auf Ihrem Beispiel mit fünf Spalten),
    • oder kopieren Sie einfach die Header von Sheet1nach Sheet2.
  • A2→=OFFSET(Sheet1!A$1, $F2-1, 0)
    • und ziehen Sie nach rechts, um alle Spalten abzudeckenausser für IMAGELIST (d. h. durch Spalte  D).
    • Irgendwann möchten Sie die Sheet2Spalten (also die Spalten, die Datumsangaben enthalten) richtig formatieren.
  • E2→=LEFT($G2, 8)
  • F2→2
  • G2→=OFFSET(Sheet1!E$1, $F2-1, 0)
    • (Dies ist dasselbe wie die Formeln in Spalten  A- D, außer dass Sheet1auf Spalte  verwiesen wird E.)
  • H2→=RIGHT($G2, LEN($G2)-8)

Ziehen/füllen Sie Zeile 2 in Zeile 3. Ändern Sie dann

  • F3zu =IF(H2<>"", F2, F2+1)und
  • G3Zu=IF(H2<>"", H2, OFFSET(Sheet1!E$1, $F3-1, 0))

Ziehen/füllen Sie nun Zeile 3 nach unten, um so viele Zeilen wie nötig einzufügen, um alle Daten daraus zu erhalten Sheet1:

Kopieren und fügen Sie dann Werte und Formate ein.

Erläuterung:

  • Spalte  Fgibt an, welche Zeile aufSheet1 DasZeile (auf Sheet2) zieht Daten aus.
  • Jedes Mal, wenn wir zu einer neuen Sheet1Zeile vorrücken (also jedes Mal, wenn Column  Fzunimmt), erhält Column  Geine vollständige, 8×N-stelliger IMAGELISTWert von Sheet1.
  • Der Wert in der Spalte  Gwird aufgeteilt: die ersten acht Ziffern in die Spalte  Eund der Rest in die Spalte  H.
  • Solange wir einen nicht leeren Rest in Spalte haben  H, kopieren wir ihn in Spalte  Gund lassen Spalte  Funverändert (d. h., wir greifen auf dieselbe Zeile zu Sheet1).
  • Wenn uns die Daten in Spalte ausgehen  H, bedeutet das, dass wir ein 8×N-stelliger IMAGELISTWert von Sheet1inNZeilen ein Sheet2, also erhöhen wir die Spalte  Fund holen den nächsten IMAGELISTWert.

Antwort3

Um Ihr Ziel zu erreichen, müssen Sie zwei Schritte ausführen:

1 Teilen Sie die Zahlen in verschiedene Spalten auf, z. B. in Excel 2010: Daten – Text in Spalten – feste Breite – Breite auf 8 festlegen und dann auf „Fertig“ klicken.

2 Der zweite Schritt besteht darin, Ihre Daten zu entpivotieren. Leider ist das nicht einfach, aber es gibt im Internet viele Lösungen, wie diese:Ist es möglich, in Excel eine „Aufhebung der Pivotierung“ oder eine „Umkehrung der Pivotierung“ durchzuführen?

verwandte Informationen