Pobierz przykładowy skoroszyt
Ten samouczek pokaże, jak usunąć liczby z tekstu w komórce w Excelu i Arkuszach Google.
Omówimy dwie różne formuły usuwania liczb z tekstu w programie Excel.
SUBSTITUTE Formuła funkcji
Możemy użyć formuły opartej na funkcji SUBSTITUTE. To długa formuła, ale jest to jeden z najłatwiejszych sposobów na usunięcie liczb z ciągu alfanumerycznego.
W tej formule 10 razy zagnieździliśmy funkcje SUBSTITUTE, tak jak to:
| 1 | =ZAMIANA(ZAMIANA(ZAMIANA(ZAMIANA(ZAMIANA(ZAMIANA(ZAMIANA(ZAMIANA(ZAMIANA(ZAMIANA(B3,1,""),2,""),3,""),4""),5," "),6,""),7,""),8,""),9,""),0,"") |
Formuła tablicy TEKSTJOIN
Aby usunąć liczby z ciągów alfanumerycznych, możemy również użyć złożonej formuły tablicowej, która składa się z funkcji TEXTJOIN, MID, ROW i ADRESS.
| 1 | {=TEXTJOIN("",TRUE,IF(ISERR(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0),MID(B3,ROW(INDIRECT("1:" &DŁUG(B3))),1),"))} |
Uwaga: TEXTJOIN to nowa funkcja programu Excel dostępna w programach Excel 2022+ i Office 365.
To złożona formuła, więc podzielimy ją na kroki, aby lepiej ją zrozumieć.
Krok 1
Funkcja MID służy do wyodrębniania ciągu alfanumerycznego na podstawie argumentów start_num i num_chars.
Jako argument numer_początkowy funkcji MID użyjemy wynikowej listy tablicowej z funkcji WIERSZ i ADR.POŚR.
| 1 | =WIERSZ(ADR.POŚR("1:"&DŁ(B3))) |
A dla argumentu liczba-znaków wstawimy 1. Po wstawieniu argumentów do funkcji MID, zwróci ona tablicę.
| 1 | {=ŚRODEK(B3,WIERSZ(POŚREDNIA("1:"&DŁUG(B3)))1)} |
Krok 2
Dodamy zero do każdej wartości w wynikowej tablicy (którą otrzymamy z powyższej funkcji MID). W programie Excel, jeśli dodasz liczby do znaków nieliczbowych, otrzymamy #ARG! Błąd. Tak więc po dodaniu 0 w powyższej tablicy otrzymamy tablicę liczb i #Wartość! Błędy.
| 1 | {=ŚRODEK(B3,WIERSZ(POŚREDNIA("1:"&DŁUG(B3)))1)+0} |
Krok 3
Po dodaniu 0 wynikowa tablica jest umieszczana w funkcji ISERR. Jak wiemy, funkcja ISERR zwraca TRUE dla błędów i FALSE dla wartości niebędących błędami.
Daje więc tablicę PRAWDA i FAŁSZ, PRAWDA dla znaków nienumerycznych i FALSE dla liczb.
| 1 | =CZY.BŁĄD(ŚREDNIA(B3;WIERSZ(POŚREDNIA("1:"&DŁ(B3)));1)+0) |
Krok 4
Teraz dodamy funkcję JEŻELI.
Funkcja JEŻELI sprawdzi wynik funkcji ISERR (krok 3). Jeśli jego wartość to TRUE, zwróci tablicę wszystkich znaków ciągu alfanumerycznego. W tym celu dodaliśmy kolejną funkcję MID bez dodawania na końcu zera. Jeśli wartość funkcji JEŻELI jest FAŁSZ, zwróci puste („”).
W ten sposób otrzymamy tablicę zawierającą tylko nieliczbowe znaki ciągu.
| 1 | =JEŻELI(CZY.POD(MID(B3,WIERSZ(ADR.POŚR("1:"&DŁUG(B3))),1)+0),MID(B3,WIERSZ(ADR.POŚR("1:"&DŁ(B3))),1 ),"") |
Krok 5
Na koniec powyższa tablica jest umieszczana w funkcji TEXTJOIN. Funkcja TEXTJOIN połączy wszystkie znaki z powyższej tablicy i zignoruje pusty ciąg.
Ogranicznik dla tej funkcji jest ustawiony jako pusty ciąg („”), a wartość argumentu ignore_empty jest wprowadzana jako TRUE.
To da nam pożądany wynik, tj. tylko nienumeryczne znaki ciągu alfanumerycznego.
| 1 | {=TEXTJOIN("",TRUE,IF(ISERR(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0),MID(B3,ROW(INDIRECT("1:" &DŁUG(B3))),1),"))} |
Uwaga: to jest formuła tablicowa. Podczas wprowadzania formuł tablicowych w programie Excel 2022 lub starszym należy użyć CTRL + SHIFT + ENTER aby wprowadzić wzór zamiast zwykłego WEJŚĆ.
Będziesz wiedział, że poprawnie wpisałeś formułę po wyświetlonych nawiasach klamrowych. NIE wpisuj ręcznie nawiasów klamrowych, formuła nie zadziała.
W usłudze Office 365 (i prawdopodobnie w wersjach programu Excel po 2022 r.) możesz po prostu wprowadzić formułę jak zwykle.
Funkcja przycinania
Gdy liczby zostaną usunięte z ciągu, mogą pozostać dodatkowe spacje. Aby usunąć wszystkie końcowe i wiodące spacje oraz dodatkowe spacje między słowami, możemy użyć funkcji TRIM przed główną formułą, w ten sposób:
| 1 | =PRZYC(C3) |
Usuń liczby z tekstu w Arkuszach Google
Formuła usuwania liczb z tekstu działa dokładnie tak samo w Arkuszach Google jak w Excelu:
