Excel - jak zachować tylko cyfry z komórki, a nie litery.
Obejrzyj wideo
- Sprawdź kolumnę, aby zobaczyć, czy w kodzie są jakieś cyfry
- Metoda płatności 1:
- Flash Fill
- Metoda Mike'a:
- Użyj funkcji SUBSTITUTE ze stałą tablicową.
- Nie będziesz musiał używać Ctrl + Shift + Enter, ponieważ jest to stała tablicowa
- Spowoduje to usunięcie jednej cyfry na raz
- Użyj funkcji AND, aby sprawdzić, czy każdy element w wynikowej tablicy jest równy oryginalnemu elementowi
- Metoda płatności 3:
- Użyj funkcji VBA, aby sprawdzić cyfry
Transkrypcja wideo
Bill: Hej. Witamy spowrotem. Czas na kolejny podcast Dueling Excel. Jestem Bill Jelen z. Dołączy do mnie Mike Girvin z ExcelIsFun. To jest nasz odcinek 186: tak, jeśli jakieś cyfry w komórce. Dzisiejsze pytanie jest wysłane przez Jen, zawiera 13 000 wierszy danych, musi przejrzeć komórkę, jeśli jakikolwiek znak jest cyfrą, zaznacz ją jako Tak, w przeciwnym razie Nie. W porządku. Cóż, Mike, mam nadzieję, że masz na to wspaniały sposób, bo ja nie.
Zamierzam użyć funkcji flash fill, a w przypadku flash fill podam tutaj wzór z kilkoma literami i cyframi. Chcę się upewnić, że podam wszystkie możliwe liczby, aby zrozumiała, co robię, i 0 w ten sposób. Mamy więc oryginalne dane, a potem poproszę o naprawienie tego za pomocą funkcji flash fill, aw poprawionej wersji pozbędziemy się wszystkich cyfr. Więc spojrzę i zobaczę, czy jest cyfra. Jeśli tak, pozbądź się go w ten sposób, a następnie naciśnij CONTROL + E, aby błyskawicznie wypełnić, a teraz powinniśmy mieć tylko litery, tylko litery.
A potem pytanie brzmi, czy to się zmieniło? Więc = JEŻELI to jest = do tego, czego szukamy, to oznacza, że nie było żadnych zmian, żadnych cyfr, a następnie mówimy Nie, w przeciwnym razie tak, tak i kliknij dwukrotnie, skopiuj to w dół, w porządku i wtedy wszystko, co ma „Nie”, nie ma tam cyfr. Pozbywamy się naszego pierwotnego wiersza, a po skopiowaniu tego, CONTROL + C, ALT + E, S, V, możemy pozbyć się wypełnienia flash. W porządku, Mike. Zobaczmy, co masz. (= JEŻELI (A2 = B2, „Nie”, „Tak”))
Mike: Wow ,. To chyba najbardziej zdumiewające, twórcze, unikalne użycie wypełnienia flash, jakie kiedykolwiek widziałem. Wymyśliłeś tekst, wyjąłeś rzeczywiste liczby, CONTROL + E, i natychmiast wyodrębniłeś wszystkie liczby i dostarczyłeś nowy ciąg tekstowy bez liczb, a następnie wykonałeś swoje IF. Absolutnie piękna.
W porządku. Przejdę do tego arkusza tutaj i użyję funkcji PODSTAW. Teraz, SUBSTITUTE, powiem mu, żeby spojrzał na ten tekst i OLD_TEXT, które chcę znaleźć i usunąć, no cóż, wszystkie są cyframi. Dlatego utworzę stałą tablicową (1, 2, 3, 4, 5 wszystkich cyfr i). To jest stała tablicowa i znajduje się w OLD_TEXT, ponieważ nie umieszczam tam ani jednego elementu, ale zamiast tego umieszczam kilka elementów. To jest operacja na tablicy argumentów funkcji. Jest tutaj 10 różnych elementów, które poinstruują SUBSTITUTE, aby dostarczył 10 oddzielnych elementów, właściwie, a jeśli znajdzie jeden z tych elementów, czego chcę? „”. To powie funkcji, aby nic tam nie umieszczała). (= PODSTAW (A2; (1,2,3,4,5,6,7,8,9,0); „”))
Teraz mój kursor jest na końcu. Kiedy naciskam klawisz F9, z pewnością, ponieważ jest tylko 0, wszystkie są dokładnie równe, z wyjątkiem ostatniego. W ostatnim przypadku SUBSTITUTE znalazł 0 i nie wstawił nic w jego miejsce. Teraz CONTROL-Z, CONTROL-ENTER i skopiuję to tutaj, F2 i F9. Tak więc, jeśli przejdziemy do 6, jest wersja 5, usunęła tam 5, usunęła tam 6 i tam usunęła 8, więc będą 1, 2, 3 różne elementy. Tylko wtedy, gdy wszystkie elementy są dokładnie równe oryginalnemu przedmiotowi, powie nam, że nie ma cyfr. UCIECZKA.
Mam zamiar wrócić na szczyt. Brzmi jak: F2, to jest test logiczny AND. Funkcja AND. Chcę sprawdzić, czy każdy z tych elementów w wynikowej tablicy jest równy oryginalnemu elementowi. Kiedy wszystkie są prawdziwe, powie mi, że w tym pierścieniu tekstowym nie ma liczb. ), CONTROL + ENTER, otrzymuję FALSE, ponieważ w jednym z nich wewnętrznie brakuje tego 0. Skopiuję to tutaj. Ten oczywiście otrzyma PRAWDA - tak samo jak te - ponieważ wszystkie wewnętrznie wygenerowane elementy, jeśli I F2 tutaj, wszystkie te, F9, są dokładnie równe oryginałowi. UCIECZKA. Teraz wchodzę na szczyt. Nawiasem mówiąc, nie musiałem używać CONTROL + SHIFT + ENTER, ponieważ kiedy używasz tej stałej tablicowej w formule tablicowej, nie musisz używać CONTROL + SHIFT + ENTER. (= AND (SUBSTITUTE (A2, (1,2,3,4,5,6,7,8,9,0), „”) = A2))
W porządku. Mam zamiar przejść do początku. To mój test logiczny. Jeśli wszystkie z nich okażą się prawdziwe, wpisz wartość, jeśli prawdziwe, w polu „NIE”, w przeciwnym razie wpisz TAK, ”). CONTORL + ENTER. Kliknij dwukrotnie. Będę musiał dwukrotnie kliknąć ten i wysłać go. W porządku. To było trochę zabawne z SUBSTITUTE, stałą tablicową, testem logicznym AND i IF, ale powiem ci co, nadal nie mogę uwierzyć w to flash fill, w jaki sposób użyłeś go do wyodrębnienia wszystkich numery z tego. W porządku, oddam ci to, MrExcel. (= JEŻELI (AND (SUBSTITUTE (A2, (1,2,3,4,5,6,7,8,9,0), „”) = A2), „Nie”, „Tak”))
Bill: Cóż, ta formuła z SUBSTITUTE i stałą tablicą oraz AND, to jest cholernie niesamowite. Musiałem użyć wypełniania błyskawicznego, ponieważ nie mogłem tego rozgryźć. To jest genialne. Teraz mam trzeci sposób. Spójrzmy na to.
Now, here's the way that I would really solve this, just a little bit of VBA. So, I do ALT+F11 to switch over to VBA, INSERT, MODULE, and then type this code. We're going to create a new function called HASNUMBERS and we're going to pass it to cell value, and we're going to start out with the word ALPHA. We look at every single character, and if that code, if the (ASC code - 06:35) of that character is between 48 to 57, then we say that it's NUMBERS, EXIT FUNCTION, and just keep going. Right, so, it looks until it finds a digit. When it does, it returns HASNUMBERS. So, here, we’re going to say =HASNUMBERS, point to that cell, and double-click to copy that down. Anytime it sees a digit over there, will get the NUMBERS, ALPHA, easy to sort them out. (=HasNumbers(A2))
W porządku, krótkie podsumowanie odcinka. Cel: sprawdź kolumnę, aby zobaczyć, czy w kodzie znaku są jakieś cyfry, kod w komórce. Użyłem funkcji flash fill, aby usunąć cyfry, a następnie funkcji length, aby sprawdzić, czy się zmieniła, czy nie. Mike miał genialną formułę, funkcję SUBSTITUTE ze stałą tablicową. Nie potrzebujesz CONTROL + SHIFT + ENTER. Musisz usunąć jedną cyfrę na raz, a następnie użyć funkcji AND, aby spojrzeć na wszystkie 10 wyników, aby sprawdzić, czy każdy jest równy oryginalnemu elementowi. Świetny sposób, a potem, mój rezerwowy, użyj funkcji VBA, aby sprawdzić cyfry.
Więc hej. Chcę podziękować wszystkim za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem z i ExcelIsFun.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Duel186.xlsm
