Nauczyliśmy się, jak usuwać wartości liczbowe z komórki w programie Excel 2016 i starszych. Formuły, których użyliśmy, były nieco skomplikowane, ale teraz Excel 2019 i 365 są w grze.
Excel 2019 i 365 wprowadzają kilka nowych funkcji, które ułatwiają usuwanie znaków nienumerycznych i pobieranie tylko wartości liczbowych w nowej komórce. Wykorzystamy formuły, które mogą nam w tym pomóc wygodniej.
Formuła ogólna
| =TEKSTJOIN("",PRAWDA,JEŻELIBŁĄD(ŚRODEK(pomieszany_tekst,SEKWENCJA(Liczba znaków),1)+0,")) |
Pomieszany_tekst: To jest tekst źródłowy, z którego chcesz wyodrębnić wszystkie wartości liczbowe.
Liczba znaków: Jest to całkowita liczba znaków, które chcesz przetworzyć. Pomieszany_tekst nie powinien mieć więcej znaków niż ta liczba (łącznie znaki i cyfry).
Zobaczmy przykład, aby wszystko było jasne.
Przykład: Usuń znaki nieliczbowe i wyodrębnij wszystkie liczby
Więc tutaj mamy trochę pomieszanego tekstu. Ten tekst zawiera kilka liczb i kilka znaków nienumerycznych. Muszę pozbyć się znaków nienumerycznych i uzyskać wartości liczbowe tylko w kolumnie D.
Nie spodziewam się, że całkowita liczba znaków w pomieszanym tekście będzie większa niż 20. Tak więc wartość NumChars wynosi tutaj 20. Możesz zwiększyć tę liczbę, jeśli zajdzie taka potrzeba.
Zastosuj tutaj powyższą ogólną formułę, aby usunąć znaki nieliczbowe.
| =TEKSTJOIN("",PRAWDA,JEŻELIBŁĄD(ŚRODEK(C3,SEKWENCJA(20),1)+0,"")) |
A kiedy naciśniesz przycisk Enter. Wszystkie znaki nienumeryczne zostaną usunięte. Przeciągnij tę formułę w dół, aby usunąć znaki z ciągu ze wszystkich komórek w kolumnie C3.
Jak to działa?
Najpierw zobaczmy, jak krok po kroku rozwiązano tę formułę.
| 1-> POŁĄCZENIE TEKSTU("",PRAWDA,JEŻELIBŁĄD(ŚRODEK(C3,SEKWENCJA(20),1)+0,"")) |
| 2-> TEKSTJOIN("",PRAWDA,JEŻELIBŁĄD(ŚRODEK("12asw12w123",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},1)+0,"")) |
| 3-> TEKSTJOIN("",PRAWDA,JEŻELIBŁĄD({"1";"2";"a";"s";"w";"1";"2";"w";"1";"2";"3";"";"";"";"";"";"";"";"";""}+0,"")) |
| 4-> TEKSTJOIN("",PRAWDA,JEŻELIBŁĄD({1;2;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;1;2;#WARTOŚĆ!;1;2;3;#WARTOŚĆ!;#WARTOŚĆ!;… ;#WARTOŚĆ!}+0,"")) |
| 5-> TEKSTJOIN("",PRAWDA,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Jak widać, formuła zaczyna rozwiązywać się od środka. Najpierw rozwiązana jest funkcja SEQUENCE. Ponieważ przeszliśmy 20. Zwraca tablicę liczb zaczynającą się od 1 do 20.
Ta tablica służy funkcji MID jako numer początkowy. Funkcja mid przechodzi do każdego indeksu w łańcuchu i dzieli każdy znak. Możesz to zobaczyć w kroku 3.
Następnie dodajemy 0 do każdego znaku. W programie Excel, jeśli spróbujesz dodać liczbę do znaków nienumerycznych, skutkuje to #ARG! Błąd. Otrzymujemy więc tablicę liczb i #VALUE! Błędy. Znaki nienumeryczne zniknęły.
Następna funkcja JEŻELI.BŁĄD zastępuje wszystkie błędy #VALUE na „” (puste). Pozostały nam teraz wartości liczbowe i spacje.
Wreszcie ta tablica jest obsługiwana przez funkcję TEXTJOIN. Funkcja TEXTJOIN łączy je i otrzymujemy ciąg, który zawiera tylko liczby.
Wzmocnienie formuły
Powyższa formuła używała zakodowanej na sztywno liczby do przetwarzania liczby znaków (wzięliśmy dwadzieścia). Ale możesz chcieć, aby był dynamiczny. W takim przypadku dobrze zgadłeś, użyjemy funkcji LEN. Przetwarzanie zajmie dokładną liczbę znaków. Tak będzie formuła.
| =ZŁĄCZ TEKST("",PRAWDA,JEŻELIBŁĄD(ŚREDNI(pomieszany_tekst;SEKWENCJA()LEN(pomieszany_tekst)),1)+0,"")) |
W tym przypadku funkcja LEN automatycznie wykryje dokładną liczbę znaków w ciągu alfanumerycznym. To odciąży cię od określenia maksymalnej liczby znaków.
Alternatywa dla funkcji SEKWENCJA
Jeśli nie chcesz używać funkcji SEKWENCJA, możesz użyć kombinacji funkcji WIERSZ i ADR.POŚR do generowania numerów sekwencyjnych.
| =DOŁĄCZ DO TEKSTU("",PRAWDA,JEŻELIBŁĄD(ŚREDNI (pomieszany_tekst,WIERSZ(POŚREDNI("1:"&LEN(pomieszany_tekst))),1)+0,"")) |
ADR.POŚR przekonwertuje tekst („1:20”) na rzeczywisty zakres, a następnie funkcja WIERSZ wyświetli listę wszystkich numerów wierszy od 1 do 20. (20 to tylko przykład. Może to być dowolna liczba).
Więc tak, w ten sposób można wyrwać znaki nienumeryczne z ciągu alfanumerycznego w programie Excel. Mam nadzieję, że wyjaśniłem wystarczająco, a ten artykuł ci pomógł. Jeśli masz jakiekolwiek pytania dotyczące tego tematu lub jakiegokolwiek innego tematu programu Excel/VBA. Do tego czasu utrzymuj Doskonałość.
Podziel liczby i tekst z ciągu w programie Excel 2016 i starszym: Kiedy nie mieliśmy funkcji TEXTJOIN, używaliśmy funkcji LEFT i RIGHT z kilkoma innymi funkcjami do dzielenia znaków numerycznych i nienumerycznych z ciągu.
Wyodrębnij tekst z ciągu w programie Excel za pomocą funkcji LEWO i PRAWO programu Excel: Aby usunąć tekst w programie Excel z ciągu znaków, możemy użyć funkcji LEWO i PRAWO programu excel. Te funkcje pomagają nam dynamicznie ciąć łańcuchy.
Usuń wiodące i końcowe spacje z tekstu w programie Excel: Spacje wiodące i końcowe są trudne do rozpoznania wizualnie i mogą zepsuć dane. Usunięcie tych znaków z ciągu jest podstawowym i najważniejszym zadaniem w czyszczeniu danych. Oto, jak możesz to łatwo zrobić w programie Excel.
Usuń znaki od prawej: Aby usunąć znaki z prawej strony ciągu w Excelu, używamy funkcji LEWO. Tak, funkcja LEWO. Funkcja LEWO zachowuje podaną liczbę znaków z LEWEJ i usuwa wszystko z jej prawej strony.
Usuń niechciane znaki w programie Excel: Aby usunąć niechciane znaki z ciągu w programie Excel, używamy funkcji SUBSTITUTE. Funkcja SUBSTITUTE zastępuje podane znaki innym danym znakiem i tworzy nowy, zmieniony ciąg.
Jak usunąć tekst w programie Excel, zaczynając od pozycji w programie Excel?: Aby usunąć tekst z pozycji początkowej w ciągu, używamy funkcji REPLACE programu Excel. Ta funkcja pomaga nam określić pozycję początkową i liczbę znaków do usunięcia.
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność | Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO programu Excel| Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z Excela LICZ.JEŻELI Funkcja| Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMIF w programie Excel? | To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.