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 z nowymi zabawkami, mam na myśli funkcje.
Excel 2019 i 365 wprowadzają kilka nowych funkcji (TEXTJOIN i SEQUENCE), które mogą ułatwić zadanie usuwania znaków numerycznych i pobierania tylko wartości nienumerycznych w nowej komórce. Wykorzystamy formuły, które mogą nam w tym pomóc wygodniej.
Formuła ogólna
| =TEKSTJOIN("",PRAWDA,JEŚLI(CZY.BŁĄD(ŚRODEK(pomieszany_tekst,SEKWENCJA(Liczba znaków),1)+0),ŚRODEK(pomieszany_tekst,SEKWENCJA(Liczba znaków),1),""))) |
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 numeryczne i wyodrębnij wszystkie alfabety i znaki nienumeryczne.
Więc tutaj mamy trochę tekstu alfanumerycznego. Ten tekst zawiera kilka liczb i kilka znaków nienumerycznych. Muszę pozbyć się znaków numerycznych i uzyskać tylko alfabety i inne wartości znaków w kolumnie D.
Nie spodziewam się, że całkowita liczba znaków w pomieszanym tekście będzie większa niż 100. Zatem wartość NumChars wynosi tutaj 100. W razie potrzeby możesz zwiększyć lub zmniejszyć tę liczbę.
Zastosuj tutaj powyższą ogólną formułę, aby usunąć znaki numeryczne.
| =TEKSTJOIN("",PRAWDA,JEŚLI(CZY.BŁĄD(ŚRODEK(B3,SEKWENCJA(100),1)+0),ŚRODEK(B3,SEKWENCJA(100),1),""))) |
Kiedy naciśniesz przycisk Enter, wszystkie znaki numeryczne zostaną usunięte i pozostaną tylko wartości alfabetyczne. Przeciągnij tę formułę w dół, aby usunąć liczby z ciągu ze wszystkich komórek w kolumnie B.
Jak to działa?
Najpierw zobaczmy, jak krok po kroku rozwiązano tę formułę.
| 1-> POŁĄCZENIE TEKSTU("",PRAWDA,JEŚLI(CZY.BŁĄD(ŚRODEK(B3,SEKWENCJA(100),1)+0),ŚRODEK(B3,SEKWENCJA(100),1),""))) |
| 2-> TEKSTJOIN("",PRAWDA,JEŚLI(CZY.BŁĄD(ŚRODEK(„This1 is… site”,{1,2,3,… 100},1)+0),ŚRODEK(B3,SEKWENCJA(100),1),""))) |
| 3-> TEKSTJOIN("",PRAWDA,JEŚLI(CZY.BŁĄD({"T";"h";"i";"s";" ","1"… "";""}+0),ŚRODEK(B3,SEKWENCJA(100),1),""))) |
| 4-> TEKSTJOIN("",PRAWDA,JEŚLI(CZY.BŁĄD({#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;1…;#WARTOŚĆ!}),ŚRODEK(B3,SEKWENCJA(100),1),""))) |
| 5-> TEKSTJOIN("",PRAWDA,JEŚLI({PRAWDA;PRAWDA;PRAWDA;PRAWDA;FAŁSZ… ;PRAWDA},ŚRODEK(B3,SEKWENCJA(100),1),""))) |
| 6-> TEKSTJOIN("",PRAWDA,{"Ten";""… .;""}) |
| 7-> "To jest strona numer jeden" |
Dla ułatwienia czytania nie napisałem całej tablicy. Użyłem kropek (… ) do oznaczenia długich tablic.
Jak widać, formuła zaczyna rozwiązywać się od środka. Najpierw rozwiązana jest funkcja SEQUENCE. Ponieważ przekazaliśmy 100 jako liczbę znaków. Zwraca tablicę liczb od 1 do 100.
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 w tablicy. Widać to w kroku 3. Nie pokazałem całej tablicy, ponieważ zajmie zbyt dużo miejsca. {"T";"h";"i";"s";" ","1"… "";""}
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. {#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;1…;#WARTOŚĆ!}
Ta tablica jest obsługiwana przez CZY.BŁĄD funkcjonować. Jak wiesz, funkcja ISERROR zwraca TRUE dla błędów i FALSE dla wartości niebędących błędami. Stąd otrzymujemy tablicę TRUE i FALSE. TRUE dla znaków nienumerycznych i FALSE liczb. {PRAWDA;PRAWDA;PRAWDA;PRAWDA;FAŁSZ…;PRAWDA}.
To samo dzieje się z instrukcjami sekcji TRUE funkcji JEŻELI (ŚRODEK(B3,SEKWENCJA(100),1)). Zwraca tablicę wszystkich znaków ciągu alfanumerycznego w B3.
Teraz dla każdej instrukcji TRUE funkcja JEŻELI zwraca odpowiedni znak z tablicy sekcji prawda. Dla FALSE, JEŻELI zwraca puste (""). Teraz będziesz mieć tablicę, która nie zawiera żadnego znaku numerycznego. {"Ten";""… .;""}.
Wreszcie ta tablica jest obsługiwana przez TEKSTJOIN funkcjonować. Funkcja ta łączy ze sobą podane teksty, ignorując puste wartości, z zadanym ogranicznikiem. Stąd otrzymujemy ciąg, który nie zawiera żadnych znaków numerycznych. To jest strona numer jeden.
Wzmocnienie formuły
Powyższa formuła wykorzystuje zakodowaną liczbę do przetwarzania liczby znaków (wzięliśmy 100). Ale możesz chcieć, aby był dynamiczny. W takim razie dobrze zgadłeś, możesz użyć funkcji LEN. Przetworzenie zajmie dokładną liczbę znaków. Tak będzie formuła.
| =TEKSTJOIN("",PRAWDA,JEŻELI(BŁĄD(ŚREDNIO(POmieszany_tekst;SEKWENCJA()LEN(pomieszany_tekst),1)+0),MID(tekst pomieszany,SEKWENCJA(LEN(pomieszany_tekst),1),""))) |
W tym przypadku funkcja LEN automatycznie wykryje dokładną liczbę znaków w ciągu alfanumerycznym. Zwolni to z ciężaru określania 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.
| =TEKSTJOIN("",PRAWDA,JEŻELI(BŁĄD(ŚREDNIO(POmieszany_tekst;WIERSZ(POŚREDNI("1:"&LEN(Liczba znaków))),1)+0),MID(tekst pomieszany,WIERSZ(POŚREDNI("1:"&LEN(Liczba znaków))),1),""))) |
ADR.POŚR przekonwertuje tekst („1:100”) na rzeczywisty zakres, a następnie funkcja WIERSZ wyświetli listę wszystkich numerów wierszy od 1 do 100. (100 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ść.
Jak usunąć znaki nieliczbowe z komórek w programie Excel 2019: Aby usunąć znaki nienumeryczne z ciągu alfanumerycznego w programie Excel, używamy nowej funkcji TEXTJOIN. Pasek nienumerycznych znaków z ciągu może pomóc nam oczyścić nasze dane w celu lepszej analizy danych. Więc oto jak to robisz
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.