Formuła ogólna
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Podsumowanie
Aby usunąć znaki numeryczne z ciągu tekstowego, możesz użyć formuły opartej na funkcji TEXTJOIN. W przedstawionym przykładzie formuła w C5 to:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Uwaga: jest to formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter, z wyjątkiem Excel 365.
Wyjaśnienie
Program Excel nie umożliwia rzutowania liter w ciągu tekstowym na tablicę bezpośrednio w formule. Aby obejść ten problem, ta formuła korzysta z funkcji ŚREDNIE z pomocą funkcji WIERSZ i POŚREDNI, aby osiągnąć ten sam wynik. Wzór w C5, skopiowany w dół, to:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Wygląda to na dość skomplikowane, ale sedno polega na tym, że tworzymy tablicę wszystkich znaków w B5 i testujemy każdy znak, aby sprawdzić, czy to liczba. Jeśli tak, odrzucamy wartość i zastępujemy ją pustym ciągiem znaków („”). Jeśli nie, dodajemy znak nienumeryczny do „przetworzonej” tablicy. Na koniec używamy funkcji TEXTJOIN (nowość w programie Excel 2019), aby połączyć wszystkie znaki razem, ignorując puste wartości.
Działając od wewnątrz, funkcja MID służy do wyodrębniania tekstu w B5, po jednym znaku na raz. Kluczem jest tutaj ROW i INDIRECT fragment:
ROW(INDIRECT("1:100"))
który obraca tablicę zawierającą 100 liczb, w ten sposób:
(1,2,3,4,5,6,7,8… .99,100)
Uwaga: 100 to maksymalna liczba znaków do przetworzenia. Zmień, aby dopasować swoje dane lub użyj funkcji LEN, jak wyjaśniono poniżej.
Ta tablica trafia do funkcji MID jako argument start_num . Dla num_chars używamy 1.
Funkcja MID zwraca tablicę taką jak ta:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Uwaga: dodatkowe elementy z tablicy zostały usunięte w celu zwiększenia czytelności.
Do tej tablicy dodajemy zero. Jest to prosta sztuczka, która zmusza program Excel do przekształcania tekstu w liczbę. Liczbowe wartości tekstowe, takie jak „1”, „2”, „3”, „4” itp. Są konwertowane bez błędów, ale wartości nienumeryczne zakończą się niepowodzeniem i wygenerują błąd #ARG. Używamy funkcji JEŻELI z funkcją ISERR, aby wychwycić te błędy. Kiedy widzimy błąd, wiemy, że mamy znak nienumeryczny, więc umieszczamy ten znak w przetwarzanej tablicy za pomocą innej funkcji MID:
MID(B5,ROW(INDIRECT("1:100")),1)
Jeśli nie wystąpi błąd, wiemy, że mamy liczbę, więc w miejsce liczby wstawiamy pusty ciąg („”) do tablicy.
Ostateczny wynik tablicy trafia do funkcji TEXTJOIN jako argument tekst1. Jako separator używamy pustego łańcucha (""), a dla ignore_empty podajemy TRUE. Następnie TEXTJOIN łączy wszystkie niepuste wartości w tablicy i zwraca wynik.
Dokładna długość tablicy
Zamiast zakodować na stałe liczbę, taką jak 100, w POŚREDNI, możesz użyć funkcji DŁ, aby zbudować tablicę z rzeczywistą liczbą znaków w komórce w następujący sposób:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN zwraca liczbę znaków w komórce jako liczbę, która jest używana zamiast 100. Umożliwia to automatyczne skalowanie formuły do dowolnej liczby znaków.
Usuwanie dodatkowej przestrzeni
Po usunięciu znaków numerycznych mogą pozostać dodatkowe znaki spacji. Aby usunąć spacje wiodące i końcowe oraz znormalizować spacje między wyrazami, możesz zawijać formułę pokazaną na tej stronie wewnątrz funkcji TRIM:
=TRIM(formula)
Z SEQUENCE
W programie Excel 365 nowa funkcja SEQUENCE może zastąpić powyższy kod WIERSZ + POŚREDNI:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Tutaj używamy SEQUENCE + LEN do zbudowania tablicy o odpowiedniej długości w jednym kroku.
Z LET
Możemy dodatkowo usprawnić tę formułę za pomocą funkcji LET. Ponieważ tablica jest tworzona dwukrotnie powyżej za pomocą SEQUENCE i LEN, możemy zdefiniować tablicę jako zmienną i utworzyć ją tylko raz:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Tutaj wartość tablicy jest ustawiana tylko raz, a następnie używana dwukrotnie w funkcji MID.
