Note: The other languages of the website are Google-translated. Back to English

Usuń lub usuń znaki nienumeryczne z ciągów tekstowych

Czasami może być konieczne usunięcie wszystkich znaków nienumerycznych z ciągów tekstowych i zachowanie tylko liczb, jak pokazano na zrzucie ekranu. W tym artykule przedstawimy kilka formuł do rozwiązania tego zadania w programie Excel.


Usuń lub usuń wszystkie znaki nieliczbowe z ciągów tekstowych za pomocą formuł

W Excel 2019 i Office 365 nowa funkcja TEXTJOIN łącząca się z funkcjami IFERROR, MID, ROW i INDIRECT może pomóc w wyodrębnieniu tylko liczb z ciągu tekstowego, składnia ogólna to:

=TEXTJOIN("",TRUE,IFERROR(MID(text,ROW(INDIRECT("1:100")),1)+0,""))
  • text: ciąg tekstowy lub wartość komórki, z której chcesz usunąć wszystkie znaki nieliczbowe.

1. Skopiuj lub wprowadź poniższą formułę do pustej komórki, w której chcesz wyświetlić wynik:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))

2. A następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać pierwszy wynik, patrz zrzut ekranu:

3. Następnie wybierz komórkę formuły, a następnie przeciągnij uchwyt wypełniania w dół do komórek, do których chcesz zastosować tę formułę, wyodrębniono tylko liczby, a wszystkie inne znaki nieliczbowe zostały usunięte, zobacz zrzut ekranu:


Wyjaśnienie wzoru:

WIERSZ (POŚR. („1: 100”): Liczba 1: 100 we wzorze POŚREDNI oznacza, że ​​funkcja MID oblicza 100 znaków ciągu tekstowego. Ta tablica będzie zawierała 100 liczb, takich jak: {1; 2; 3; 4; 5; 6; 7; 8 .... 98; 99; 100}.
Uwagi: Jeśli twój ciąg tekstowy jest znacznie dłuższy, możesz zmienić liczbę 100 na większą według potrzeb.

ŚREDNI (A2, WIERSZ (POŚR. ("1: 100")), 1: Ta funkcja MID służy do wyodrębniania tekstu w komórce A2, aby uzyskać jeden znak, i będzie to tablica taka:
{"5", "0", "0", "", "K", "u", "t"; "o"; "o"; "l"; "s"; ""; "f" ; "o"; "r"; ""; "E"; "x"; "c"; "e"; "l"; ""; ""; ""; ""; ""; "". ..}

MID(A2,ROW(INDIRECT("1:100")),1)+0: Dodanie wartości 0 po zastosowaniu tej tablicy do wymuszenia na tekście liczby, numeryczna wartość tekstowa zostanie przekonwertowana na liczbę, a wartości nieliczbowe zostaną wyświetlone jako wartość błędu #ARG:
{"5"; "0"; "0"; # WARTOŚĆ!; # WARTOŚĆ!; # WARTOŚĆ!; # WARTOŚĆ!; # WARTOŚĆ!; # WARTOŚĆ! !;#WARTOŚĆ! !;#WARTOŚĆ!...}

IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0: Ta funkcja JEŻELI BŁĄD służy do zastępowania wszystkich wartości błędów pustym ciągiem znaków, takim jak ten:
{"5"; "0", "0", ""; ""; ""; ""; ""; ""; ""; ""; …}

TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,"")): W końcu ta funkcja TEXTJION połączy wszystkie niepuste wartości w tablicy, która została zwrócona przez funkcję IFFERROR i zwróci wynik.


Uwagi:

1. Przy powyższym wzorze liczby zostaną zwrócone w formacie tekstowym, jeśli potrzebujesz prawdziwej wartości liczbowej, zastosuj tę formułę, pamiętaj, aby nacisnąć Ctrl + Shift + Enter klucze razem, aby uzyskać prawidłowy wynik.

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))+0

2. We wczesnych wersjach programu Excel ta formuła nie będzie działać, w tym przypadku może pomóc następująca formuła, skopiuj lub wprowadź tę formułę do pustej komórki:

=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)


Usuń lub usuń wszystkie znaki nienumeryczne z ciągów tekstowych za pomocą łatwej funkcji

Być może jest zbyt długo, aby zapamiętać powyższe formuły, tutaj wprowadzę Kutools dla programu Excel dla ciebie, z jego Usuń znaki funkcja, możesz usunąć numeryczne, alfabetyczne, niedrukowalne lub alfanumeryczne znaki z ciągów tekstowych za pomocą kilku kliknięć. Kliknij, aby pobrać Kutools for Excel za bezpłatną wersję próbną!


Używane funkcje względne:

  • TEXTJOIN:
  • Funkcja TEXTJOIN łączy wiele wartości z wiersza, kolumny lub zakresu komórek za pomocą określonego separatora.
  • MID:
  • Funkcja MID służy do wyszukiwania i zwracania określonej liczby znaków ze środka danego ciągu tekstowego.
  • ROW:
  • Funkcja Excel WIERSZ zwraca numer wiersza odwołania.
  • INDIRECT:
  • Funkcja Excel POŚREDNI konwertuje ciąg tekstowy na prawidłowe odwołanie.
  • IFERROR:
  • Funkcja JEŻELI BŁĄD służy do zwracania niestandardowego wyniku, gdy formuła oceni błąd, i zwraca normalny wynik, gdy nie wystąpił błąd.

Więcej artykułów:


Najlepsze narzędzia biurowe

Kutools dla programu Excel - pomaga wyróżnić się z tłumu

Chcesz szybko i perfekcyjnie zakończyć swoją codzienną pracę? Kutools dla programu Excel oferuje 300 zaawansowanych zaawansowanych funkcji (Łącz skoroszyty, sumuj według koloru, dziel zawartość komórek, konwertuj daty itd.) i oszczędzaj 80% czasu.

  • Zaprojektowany dla 1500 scenariuszy pracy, pomaga rozwiązać 80% problemów z Excelem.
  • Zmniejsz tysiące kliknięć klawiatury i myszy każdego dnia, odciąż zmęczone oczy i dłonie.
  • Zostań ekspertem Excela w 3 minuty. Nie musisz już pamiętać żadnych bolesnych formuł i kodów VBA.
  • 30-dniowy nieograniczony bezpłatny okres próbny. 60-dniowa gwarancja zwrotu pieniędzy. Bezpłatna aktualizacja i wsparcie przez 2 lata.
Wstążka programu Excel (z zainstalowanym Kutools dla programu Excel)

Karta Office - Włącz czytanie i edycję na kartach w Microsoft Office (w tym Excel)

  • Jedna sekunda, aby przełączać się między dziesiątkami otwartych dokumentów!
  • Zmniejsz liczbę kliknięć myszą każdego dnia, pożegnaj się z dłonią myszy.
  • Zwiększa produktywność o 50% podczas przeglądania i edytowania wielu dokumentów.
  • Wprowadza wydajne zakładki do pakietu Office (w tym Excel), podobnie jak Chrome, Firefox i nowy Internet Explorer.
Zrzut ekranu programu Excel (z zainstalowaną kartą Office)
Sortuj komentarze według
Komentarze (4)
Brak ocen. Oceń jako pierwszy!
Ten komentarz został zminimalizowany przez moderatora na stronie
Dzięki za to. Fajna formuła. Jak mógłbym ją zmienić, aby jeśli komórka zawierała tylko litery, formuła wpisywała 0 w komórce wyników (a nie tylko puste, jak to jest w tej chwili)? Myślałem, że mogę to zrobić, zawijając formułę w kolejne oświadczenie IF, ale nie zajdę zbyt daleko.
Ten komentarz został zminimalizowany przez moderatora na stronie
Witaj Glenn,Aby wyświetlić wyniki jako spacje, a nie zera, zastosuj następującą formułę:=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

Proszę spróbować, mam nadzieję, że może ci to pomóc!
Ten komentarz został zminimalizowany przez moderatora na stronie
Wow, to zajmie trochę czasu Dzięki za poświęcenie czasu na odpowiedź 👍
Ten komentarz został zminimalizowany przez moderatora na stronie
Jaka jest ta formuła ustawień francuskich?
Nie ma tu jeszcze żadnych komentarzy
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje

Bądż na bieżąco

Prawa autorskie © 2009 - www.extendoffice.com. | Wszelkie prawa zastrzeżone. Zasilany przez ExtendOffice, | Mapa strony
Microsoft i logo Office są znakami towarowymi lub zastrzeżonymi znakami towarowymi Microsoft Corporation w Stanach Zjednoczonych i / lub innych krajach.
Chronione przez Sectigo SSL