Ich muss die Zeit aus dem Text in einer Excel-Zelle ermitteln. Der Text enthält die Ticketnummer, den Tickettitel, das Zeitprotokoll und den Prozentsatz. Alle diese Angaben befinden sich in einer einzigen Zelle. Siehe Beispiel unten:
12345678: The bottom part is incorrect 0:15h 0%
Ich muss das bekommen 0:15. Ich habe versucht, die Funktionskombination LINKS und RECHTS zu verwenden, aber das funktioniert nur, wenn der Prozentsatz aus dem letzten Satz nur eine Ziffer hat. Der Prozentsatz variiert zwischen 0 % und 100 %. Die Länge des Tickettitels variiert ebenfalls.
Antwort1
Ich benutze kostenlosReg-Ex-Ersetzungs-Add-In* fügt Excel die Funktionalität regulärer Ausdrücke hinzu, mit der Sie diese Formel verwenden können:
=RegExFind(A2,"\d{1,2}:\d{1,2}")
* Ich bin nicht mit diesem Add-In verbunden
Antwort2
Die Antwort von Máté Juhász ist sicherlich die einfachste. Wenn Sie das Add-on jedoch nicht nutzen möchten und mit Regex nicht vertraut sind (und es auch nicht lernen möchten), gibt es einen etwas komplizierteren Weg, um das Ergebnis mithilfe integrierter Funktionen zu erhalten. Es lässt sich etwas vereinfachen, wenn Sie Hilfsspalten verwenden, um Wiederholungen zu vermeiden. Ich werde das verwenden, um zu erklären, wie es funktioniert.
Alle „Wörter“ sind durch Leerzeichen getrennt. Der Tickettitel kann eine unterschiedliche Anzahl von Wörtern enthalten. Das letzte Element, der Prozentsatz, steht nach dem letzten Leerzeichen und die Zeit nach dem vorletzten Leerzeichen. Spalte B berechnet die Anzahl der Leerzeichen mit dieser Formel:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
Diese Formel funktioniert, indem alle Leerzeichen entfernt werden und dann berechnet wird, um wie viele Zeichen die Zeichenfolge gekürzt wurde. Im Beispiel sind es 7 Leerzeichen.
In Spalte C wird das Ende der Zeichenfolge ab der Uhrzeit abgetrennt. Die Formel lautet:
=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),B1-1))+1,LEN(A1))
Dies funktioniert, indem das Leerzeichen vor der Zeit (also das vorletzte Leerzeichen) durch ein beliebiges Zielzeichen ersetzt wird, das in den Daten nicht vorkommt (in diesem Fall CHAR(1)). Dabei wird die Funktion SUBSTITUTE verwendet, mit der Sie eine bestimmte Instanz angeben können, die ersetzt werden soll. FIND sucht dann die Position dieses Zeichens und MID extrahiert den Rest der Zeichenfolge beginnend an der nächsten Position.
Das Ergebnis in Spalte D extrahiert die Zeit mit dieser Formel:
=LEFT(C1,FIND("h",C1)-1)
Es funktioniert, indem das „h“ gesucht und alles extrahiert wird, was davor steht.
Sie können dies in einer einzigen Formel tun, ohne Hilfsspalten zu verwenden. Wenn Sie Hilfszellreferenzen durch Hilfszellformeln ersetzen, würde diese Formel folgendermaßen aussehen:
=LEFT(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,LEN(A1)),FIND("h",MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,LEN(A1)))-1)
Beachten Sie, dass dies alles saubere Daten ohne nachstehende Leerzeichen voraussetzt. Wenn nachstehende Leerzeichen möglich sind, schließen Sie alle Verweise auf A1 mit TRIM() ein oder trimmen Sie A1 in einer Hilfsspalte und verweisen Sie darauf, um Wiederholungen zu vermeiden.
Antwort3
Versuchen Sie es mit der folgenden Formel:
=TRIM(RIGHT(TRIM(LEFT(SUBSTITUTE(TRIM(E10);"h ";REPT(" ";99));99));5))