Alle Ziffern finden - Excel-Tipps

Excel - wie man nur die Ziffern einer Zelle behält, nicht die Buchstaben.

Schau Video

• Überprüfen Sie eine Spalte, um festzustellen, ob der Code Ziffern enthält
• Rechnungsmethode 1:
• Flash Fill
• Mike Methode:
• Verwenden Sie die SUBSTITUTE-Funktion mit einer Array-Konstante.
• Sie müssen Strg + Umschalt + Eingabetaste nicht verwenden, da es sich um eine Array-Konstante handelt
• Dadurch wird jeweils eine Ziffer entfernt
• Verwenden Sie die UND-Funktion, um festzustellen, ob jedes Element im resultierenden Array dem ursprünglichen Element entspricht
• Rechnungsmethode 3:
• Verwenden Sie eine VBA-Funktion, um nach Ziffern zu suchen

Video-Transkript

Bill: Hey. Willkommen zurück. Es ist Zeit für einen weiteren Duell-Excel-Podcast. Ich bin Bill Jelen aus. Ich werde von Mike Girvin von ExcelIsFun begleitet. Dies ist unsere Episode 186: Ja, wenn Ziffern in der Zelle vorhanden sind. Die heutige Frage wird von Jen gesendet, hat 13.000 Datenzeilen, muss durch eine Zelle schauen, wenn ein Zeichen eine Ziffer ist, markieren Sie es als Ja, sonst Nein. In Ordnung. Nun, Mike, ich hoffe, dass du eine großartige Möglichkeit hast, dies zu tun, weil ich es nicht tue.

Ich werde Flash Fill verwenden und in Flash Fill werde ich hier nur ein Muster mit einigen Buchstaben und Zahlen geben. Ich möchte sicherstellen, dass ich alle möglichen Zahlen einbinde, damit es versteht, was ich tue, und 0 so. Es gibt also die Originaldaten, und dann muss ich die Flash-Füllung für mich reparieren lassen, und in der festen Version werden wir alle Ziffern entfernen. Also werde ich schauen und sehen, ob es eine Ziffer gibt. Wenn dies der Fall ist, entfernen Sie es auf diese Weise und drücken Sie dann STRG + E, um die Füllung zu aktualisieren. Jetzt sollten nur noch die Buchstaben und nur die Buchstaben angezeigt werden.

Und dann ist die Frage, hat sich das geändert? Also = WENN dies = das ist, wonach wir suchen, dann bedeutet das, dass es keine Änderungen gab, keine Ziffern, dann sagen wir Nein, sonst Ja, so, und doppelklicken Sie, kopieren Sie das nach unten, in Ordnung, und dann hat alles mit einem Nein dort keine Ziffern. Wir werden unsere ursprüngliche Zeile los und sobald wir diese kopieren, STRG + C, ALT + E, S, V, können wir die Blitzfüllung loswerden. Okay, Mike. Mal sehen, was du hast. (= IF (A2 = B2, "Nein", "Ja"))

Mike: Wow. Das muss die erstaunlichste kreative, einzigartige Verwendung von Flash Fill sein, die ich je gesehen habe. Sie haben einen Text erfunden, die tatsächlichen Zahlen CONTROL + E herausgenommen und sofort alle Zahlen extrahiert und eine neue Textzeichenfolge ohne die Zahlen geliefert. Dann haben Sie Ihre IF ausgeführt. Wunderschön.

In Ordung. Ich werde gleich hier zu diesem Blatt kommen und die SUBSTITUTE-Funktion verwenden. Nun, SUBSTITUTE, ich werde ihm sagen, dass er sich diesen Text genau dort ansehen soll, und den OLD_TEXT, den ich finden und entfernen möchte, nun, sie sind alle Ziffern. Also werde ich eine Array-Konstante erstellen (1, 2, 3, 4, 5 alle Ziffern und). Das ist eine Array-Konstante, die sich in OLD_TEXT befindet, weil ich dort kein einzelnes Element platziere, sondern stattdessen eine Reihe von Elementen. Dies ist eine Funktionsargument-Array-Operation. Es gibt hier 10 verschiedene Artikel, die SUBSTITUTE anweisen, 10 separate Artikel zu liefern, richtig, und wenn es einen dieser Artikel findet, was möchte ich? "". Das sagt der Funktion, dass sie nichts dort ablegen soll,). (= ERSATZ (A2, (1,2,3,4,5,6,7,8,9,0), ""))

Jetzt ist mein Cursor am Ende. Wenn ich die Taste F9 drücke, sind alle bis auf die letzte genau gleich, da es nur eine 0 gibt. Für den letzten hat das SUBSTITUT die 0 gefunden und nichts an seine Stelle gesetzt. Nun, CONTROL-Z, CONTROL-ENTER und ich werden es bis hierher kopieren, F2 und F9. Wenn wir also zur 6 gehen, gibt es die 5-Version, sie hat die 5 dort entfernt, sie hat die 6 dort entfernt und sie hat die 8 dort entfernt, so dass es 1, 2, 3 verschiedene Elemente gibt, die unterschiedlich sind. Nur wenn alle Artikel genau dem Originalartikel entsprechen, werden wir darauf hingewiesen, dass keine Ziffern vorhanden sind. FLUCHT.

Ich werde wieder nach oben kommen. Es klingt wie F2, dies ist ein UND-logischer Test. UND-Funktion. Ich möchte überprüfen, ob jedes einzelne dieser Elemente in diesem resultierenden Array = zum ursprünglichen Element ist. Wenn sie alle wahr sind, wird es mir sagen, dass dieser Textring keine Zahlen enthält. ), CONTROL + ENTER, ich bekomme eine FALSE, weil einer von ihnen intern diese 0 fehlt. Ich werde dies hier unten kopieren. Dieser wird natürlich WAHR - genau wie diese -, weil alle intern generierten Elemente, wenn ich hier F2 habe, alle diese Elemente, F9, genau dem Original entsprechen. FLUCHT. Jetzt komme ich nach oben. Ich musste übrigens nicht CONTROL + SHIFT + ENTER verwenden, denn wenn Sie diese Array-Konstante in Ihrer Array-Formel verwenden, müssen Sie CONTROL + SHIFT + ENTER nicht verwenden. (= UND (ERSATZ (A2, (1,2,3,4,5,6,7,8,9,0), "") = A2))

In Ordung. Ich werde zum Anfang kommen. Das ist mein logischer Test. Wenn sich herausstellt, dass alle zutreffend sind, geben Sie in "NEIN" den Wert "Wenn zutreffend" ein, andernfalls setzen Sie bitte ein JA. CONTORL + ENTER. Doppelklicken Sie darauf. Ich muss auf diesen doppelklicken und ihn nach unten senden. In Ordung. Das hat ein wenig Spaß gemacht mit dem SUBSTITUTE, einer Array-Konstante, einem UND-logischen Test und dem IF, aber ich sage Ihnen was, ich kann diese Flash-Füllung immer noch nicht glauben, wie Sie sie verwendet haben, um im Wesentlichen alles zu extrahieren Zahlen davon. Okay, ich werde es Ihnen zurückwerfen, MrExcel. (= IF (AND (SUBSTITUTE (A2, (1,2,3,4,5,6,7,8,9,0), "") = A2), "No", "Yes"))

Bill: Nun, diese Formel mit SUBSTITUTE und der Array-Konstante und AND, das ist verdammt erstaunlich. Ich musste Flash Fill verwenden, weil ich das nicht herausfinden konnte. Das ist genial. Jetzt habe ich einen dritten Weg. Schauen wir uns das an.

Hier ist die Art und Weise, wie ich das wirklich lösen würde, nur ein bisschen VBA. Also mache ich ALT + F11, um zu VBA, INSERT, MODULE zu wechseln und dann diesen Code einzugeben. Wir werden eine neue Funktion namens HASNUMBERS erstellen und sie an den Zellenwert übergeben, und wir werden mit dem Wort ALPHA beginnen. Wir schauen uns jedes einzelne Zeichen an, und wenn dieser Code, wenn der (ASC-Code - 06:35) dieses Zeichens zwischen 48 und 57 liegt, dann sagen wir, dass es NUMMERN sind, FUNKTION BEENDEN und einfach weitermachen. Richtig, es sieht so aus, bis es eine Ziffer findet. Wenn dies der Fall ist, wird HASNUMBERS zurückgegeben. Also, hier werden wir = HASNUMBERS sagen, auf diese Zelle zeigen und doppelklicken, um sie nach unten zu kopieren. Jedes Mal, wenn dort eine Ziffer angezeigt wird, können die ZAHLEN ALPHA leicht aussortiert werden. (= HasNumbers (A2))

Okay, kurze Zusammenfassung der Episode. Ziel: Überprüfen Sie die Spalte, um festzustellen, ob der Zeichencode, der Code in der Zelle, Ziffern enthält. Ich habe Flash-Füllung verwendet, um die Ziffern zu entfernen, und dann die Längenfunktion, um festzustellen, ob sie sich geändert hat oder nicht. Mike hatte eine brillante Formel, SUBSTITUTE-Funktion mit einer Array-Konstante. Sie brauchen nicht STRG + UMSCHALT + EINGABETASTE. Sie müssen jeweils eine Ziffer entfernen und dann mit der UND-Funktion alle 10 Ergebnisse anzeigen, um festzustellen, ob jede dem Originalelement entspricht. Genialer Weg, und dann, mein Fallback, verwenden Sie eine VBA-Funktion, um nach Ziffern zu suchen.

Nun, hey. Ich möchte allen für ihren Besuch danken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von und ExcelIsFun.

Download-Datei

Laden Sie die Beispieldatei hier herunter: Duel186.xlsm