Formuła ogólna
(=TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:100")),1)+0,"")))
Podsumowanie
Aby usunąć znaki nieliczbowe z ciągu tekstowego, możesz wypróbować tę eksperymentalną formułę opartą na funkcji TEXTJOIN, nowej funkcji w programie Excel 2019. W przedstawionym przykładzie formuła w C5 to:
(=TEXTJOIN("",TRUE,IFERROR(MID(B5,ROW(INDIRECT("1:100")),1)+0,"")))
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.
Wyjaśnienie
Działając od wewnątrz, formuła MID służy do wyodrębniania tekstu w B5, po jednym znaku na raz. Kluczem jest ROW / POŚREDNI kawałek:
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ń odpowiednio do swoich danych.
Ta tablica trafia do funkcji MID jako argument start_num . Dla num_chars używamy 1.
Funkcja MID zwraca tablicę taką jak ta:
("1"; "0"; "0"; ""; "a"; "p"; "p"; "l"; "e"; "s"; ""; ""; ""; " „…)
(dodatkowe elementy w tablicy usunięte w celu zwiększenia czytelności)
Do tej tablicy dodajemy zero. Jest to prosta sztuczka, która zmusza program Excel do próby przekształcenia tekstu w liczbę. Liczbowe wartości tekstowe, takie jak „1”, „2”, „3”, „4” itp. Są konwertowane, natomiast wartości nienumeryczne kończą się niepowodzeniem i generują błąd #VALUE. Używamy funkcji IFERROR do wychwytywania tych błędów i zwracania pustego ciągu („”), podczas gdy wartości liczbowe przechodzą do tablicy. Wynikiem jest tablica zawierająca tylko liczby i puste ciągi:
(1; 0; 0; ""; ""; ""; ""; "";….)
Wreszcie, to wynik idzie do tablicy funkcji TEXTJOIN jako text1 argument. Do separatora , używamy pusty ciąg ( „”) i dla ignore_empty dostarczamy PRAWDA. Następnie TEXTJOIN łączy wszystkie niepuste wartości w tablicy i zwraca wynik.
Uwaga: TEXTJOIN zwróci liczby jako tekst, na przykład „100,„ 500 ”itd. Jeśli chcesz uzyskać prawdziwy wynik liczbowy, dodaj zero lub zawiń całą formułę w funkcję WARTOŚĆ.
