Ich muss die letzten 7 Zahlenwerte (keine Zeichen) aus einer Zeichenfolge in einer Excel-Zelle extrahieren. Die Zeichenfolge enthält einen Dateinamen (der eine Mischung aus Buchstaben, Zahlen und einem Bindestrich sein kann) und dann 7 Zahlen, die in eine andere Spalte verschoben werden müssen.
z.BJohnson Set1- 0 0 1 14 9 54 0
Die Zahlenwerte variieren von 0 bis 999, daher ist es nicht möglich, nur die letzten 7 Zeichen zu extrahieren.
Antwort1
Es gibt also ein paar Tricks, mit denen Sie das erreichen können, was Sie möchten. Ich werde mein Bestes tun, um sie hier zu erklären und Ihnen dann eine Formel zu geben, die diese Tricks verwendet, um Ihnen das zu geben, was Sie möchten.
Der Schlüssel besteht darin, das Leerzeichen vor dem Beginn der Zahlenliste zu finden. Wenn Ihr Beispiel allgemein genug ist, ist dieses Leerzeichen immer das 7. Leerzeichen von rechts. Das Problem ist, dass das 7. von rechts jedes beliebige Leerzeichen von links sein kann, da es so klingt, als gäbe es keine Regeln für eine festgelegte Anzahl von Leerzeichen im Dateinamen. Und die SUBSTITUTEFunktion ermöglicht es Ihnen, anzugeben, welche Instanz (von links) einer Zeichenfolge ersetzt werden soll.
Um das siebte Leerzeichen von rechts zu erhalten, müssen wir es in Leerzeichen von links umwandeln. Dazu müssen wir die Anzahl der Leerzeichen in der gesamten Zeichenfolge kennen. Sobald wir diese haben, müssen wir nur noch subtrahieren.
Um die Gesamtzahl der Leerzeichen in der Zeichenfolge zu ermitteln, gibt es einen Trick mit den Funktionen SUBSTITUTEund LEN. Die Idee besteht darin, alle Leerzeichen in der Zeichenfolge durch nichts zu ersetzen und dann die Länge der Ausgabe mit der Länge der ursprünglichen Zeichenfolge zu vergleichen. Der Unterschied ist natürlich die Anzahl der Leerzeichen in der ursprünglichen Zeichenfolge. Für eine Zeichenfolge in A1gibt das Folgende also die Anzahl der Leerzeichen zurück:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
Daher hat die gewünschte Anzahl von Leerzeichen von links immer noch 6 weitere Leerzeichen links davon.
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6
Das nächste Problem ist, dass SUBSTITUTESie nur die Instanznummer angeben können, die Sie ändern möchten. FIND, was am bequemsten wäre, um die Zahlen zu extrahieren, verfügt nicht über diese Funktion. Der Trick besteht dann darin, SUBSTITUTEeine Zeichenfolge zu generieren, die Sie beliebig verwenden können FIND. Wenn Sie das 7. Leerzeichen von rechts durch eine Zeichenfolge ersetzen, die sonst nirgendwo in der Zeichenfolge vorkommt, haben Sie eine eindeutige Teilzeichenfolge, nach der Sie suchen können. So können Sie beispielsweise Folgendes verwenden, um das gewünschte Leerzeichen durch „REPLACE!ME“ zu ersetzen:
=SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6)
Jetzt müssen Sie nur noch den Index von „REPLACE!ME“ in der Zeichenfolge finden und alles rechts davon zurückgeben. Dies kann mit den Funktionen RIGHT, LEN, und erfolgen FIND. Die letzte Funktion ist etwas repetitiv, erledigt aber die Aufgabe.
=RIGHT(SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6),LEN(SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6))-FIND("REPLACE!ME",SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6))-LEN("REPLACE!ME")+1)