Pobierz przykładowy skoroszyt
Ten samouczek będzie zademonstrować, jak znaleźć i wyodrębnić liczbę z ciągu tekstowego w programie Excel i Arkuszach Google.
Znajdź i wyodrębnij numer z ciągu
Czasami Twoje dane mogą zawierać liczby i tekst i chcesz wyodrębnić dane liczbowe.
Jeśli część liczbowa znajduje się po prawej lub lewej stronie ciągu, stosunkowo łatwo jest podzielić liczbę i tekst . Jeśli jednak liczby znajdują się wewnątrz ciągu, tj. między dwoma ciągami tekstowymi, będziesz musiał użyć znacznie bardziej skomplikowanej formuły (pokazanej poniżej).
TEXTJOIN - wyodrębnianie liczb w programie Excel 2016+
W programie Excel 2016 wprowadzono funkcję TEXTJOIN, która może wyodrębniać liczby z dowolnego miejsca ciągu tekstowego. Oto wzór:
| 1 | =TEKSTJOIN("",PRAWDA,JEŻELIBŁĄD((ŚRODEK(B3,WIERSZ(POŚREDNIA("1:"&LEN(B3)))),1)*1),"")) |
Zobaczmy, jak działa ta formuła.
Funkcje ROW, ADRESS i LEN zwracają tablicę liczb odpowiadających każdemu znakowi w ciągu alfanumerycznym. W naszym przypadku „Poniedziałek01Day” ma 11 znaków, więc funkcja WIERSZ zwróci tablicę {1;2;3;4;5;6;7;8;9;10;11}.
| 1 | =TEKSTJOIN("",PRAWDA,JEŻELIBŁĄD((B3; {1;2;3;4;5;6;7;8;9;10;11};1)*1);"")) |
Następnie funkcja MID wyodrębnia każdy znak z ciągu tekstowego, tworząc tablicę zawierającą oryginalny ciąg:
| 1 | =TEKSTJOIN("",PRAWDA,JEŻELIBŁĄD(({"M";"o";"n";"d";"a";"y";"0";"1";"D";"a ";"y"}*1),")) |
Pomnożenie każdej wartości w tablicy przez 1 stworzy tablicę zawierającą błędy Jeśli znakiem tablicy był tekst:
| 1 | =TEKSTJOIN("",PRAWDA,JEŻELIBŁĄD(({#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;0;1;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;0;1;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ! !}),")")) |
Następnie funkcja IFERROR usuwa wartości błędów:
| 1 | =TEKSTJOIN("",PRAWDA,{"";"";"";"";";"";0;1;"";"";""}) |
Pozostawienie tylko funkcji TEXTJOIN, która łączy ze sobą pozostałe liczby.
Zauważ, że formuła da ci wszystkie znaki numeryczne z twojego ciągu. Na przykład, jeśli Twój ciąg alfanumeryczny to Monday01Day01, w wyniku otrzymasz 0101.
Wyodrębnij liczby - przed programem Excel 2016
Przed Excelem 2016 można było użyć znacznie bardziej skomplikowanej metody wyodrębniania liczb z tekstu. Funkcji ZNAJDŹ można użyć razem z funkcjami JEŻELI.BŁĄD i MIN, aby określić zarówno pierwszą pozycję części liczbowej, jak i pierwszą pozycję części tekstowej po liczbie. Następnie po prostu wyodrębniamy część liczbową za pomocą funkcji MID.
| 1 | =MID(B3,MIN(JEŻELI.BŁĄD(ZNAJDŹ({0,1,2,3,4,5,6,7,8,9};B3),999999999)),MIN(JEŻELI.BŁĄD(SZUKAJ({"a" ,"b","c","d","e","f","g","h","I","j","k","l","m"," n","o","p","p","r","s","t","u","v","w","x","y","z" },B3,MIN(JEŻELI.BŁĄD(ZNAJDŹ({0,1,2,3,4,5,6,7,8,9},B3),999999999))),999999999))-MIN(JEŻELIBŁĄD(ZNAJDŹ() {0,1,2,3,4,5,6,7,8,9},B3),999999999))) |
Uwaga: To jest formuła tablicowa. Formułę należy wprowadzić, naciskając klawisze CTRL + SHIFT + ENTER, a nie tylko klawisz ENTER.
Zobaczmy krok po kroku, jak działa ta formuła.
Znajdź numer pięści
Możemy użyć funkcji ZNAJDŹ, aby zlokalizować pozycję początkową numeru.
| 1 | = MIN(JEŻELI.BŁĄD(ZNAJDŹ({1,2,3,4,5,6,7,8,9,0};B3);999999999)) |
Dla argumentu znajdź_tekst funkcji ZNAJDŹ używamy stałej tablicowej {0,1,2,3,4,5,6,7,8,9}, co powoduje, że funkcja ZNAJDŹ wykonuje oddzielne wyszukiwanie dla każdej wartości w stała tablicowa.
Argumentem wewnątrz_tekstowym funkcji ZNAJDŹ w naszym przypadku jest Monday01Day, w którym 1 znajduje się na pozycji 8, a 0 na pozycji 7, więc nasza tablica wyników będzie miała postać: {8,#VALUE,#VALUE,#VALUE, #WARTOŚĆ,#WARTOŚĆ,#WARTOŚĆ, #WARTOŚĆ, #WARTOŚĆ,7}.
Używając funkcji IFERROR zastępujemy błędy #VALUE przez 999999999. Następnie po prostu szukamy minimum w tej tablicy i otrzymujemy miejsce pierwszej liczby (7).
Pamiętaj, że powyższa formuła jest formułą tablicową, musisz nacisnąć Ctrl + Shift + Enter, aby ją uruchomić.
Znajdź pierwszy znak tekstowy po numerze
Podobnie jak w przypadku znajdowania pierwszej liczby w ciągu, używamy funkcji ZNAJDŹ, aby określić, gdzie tekst zaczyna się ponownie po liczbie, korzystając również z argumentu start_num funkcji.
| 1 | =MIN(JEŻELI.BŁĄD(ZNAJDŹ({"a","b","c","d","e","f","g","h","I","j","k ","l","m","n","o","p","p","r","s","t","u","v","w", "x","y","z"},B3,C3),999999999)) |
Ta formuła działa bardzo podobnie do poprzedniej używanej do lokalizowania pierwszej liczby, tylko używamy liter w naszej stałej tablicowej i dlatego liczby powodują błędy #VALUE. Należy pamiętać, że wyszukiwanie rozpoczynamy dopiero po pozycji określonej dla pierwszej liczby (będzie to argument start_num), a nie od początku ciągu.
Nie zapomnij nacisnąć Ctrl + Shift + Enter, aby użyć powyższej formuły.
Nie pozostaje nic, jak złożyć to wszystko razem.
Wyodrębnij numer z dwóch tekstów
Gdy mamy już pozycję początkową części liczbowej i początek części tekstowej, po prostu używamy funkcji MID, aby wyodrębnić żądaną część liczbową.
| 1 | =ŚRODEK(B3;C3;D3-C3) |
Inna metoda
Nie wyjaśniając tego bardziej szczegółowo, możesz również użyć poniższej złożonej formuły, aby uzyskać liczbę z wnętrza ciągu.
| 1 | =SUMPRODUCT(MID(0&B3,LARGE(INDEX(ISNUMBER(--MID(B3,ROW(INDIRECT("1:"&LEN(B3)))),1))*ROW(INDIRECT("1:"&LEN(B3)) )),0),ROW(ADR.POŚR("1:"&DŁ(B3))))+1,1)*10^WIER(ADR.POŚR("1:"&DŁ(B3)))/10) |
Należy pamiętać, że powyższa formuła da 0, gdy w ciągu nie zostanie znaleziona żadna liczba, a wiodące zera zostaną pominięte.
Znajdź i wyodrębnij numer z ciągu do tekstu w Arkuszach Google
Powyższe przykłady działają w Arkuszach Google w taki sam sposób, jak w programie Excel.
