Formuła ogólna
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),A1&"0123456789"))
Podsumowanie
Aby oddzielić tekst od liczb, możesz użyć formuły opartej na funkcji ZNAJDŹ, MIN i DŁ z funkcją LEWO lub PRAWO, w zależności od tego, czy chcesz wyodrębnić tekst, czy liczbę. W przedstawionym przykładzie formuła w C5 to:
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),B5&"0123456789"))
co zwraca 7, pozycję liczby 3 w ciągu „jabłka30”.
Wyjaśnienie
Przegląd
Formuła wygląda na złożoną, ale mechanika jest w rzeczywistości dość prosta.
Podobnie jak w przypadku większości formuł, które dzielą lub wyodrębniają tekst, kluczem jest zlokalizowanie pozycji szukanej rzeczy. Po uzyskaniu pozycji możesz użyć innych funkcji, aby wyodrębnić to, czego potrzebujesz.
W tym przypadku zakładamy, że liczby i tekst są połączone, a liczba pojawia się po tekście. Z oryginalnego tekstu, który pojawia się w jednej komórce, chcesz podzielić tekst i liczby na oddzielne komórki, na przykład:
| Oryginał | Tekst | Numer |
| Jabłka 30 | Jabłka | 30 |
| brzoskwinie24 | brzoskwinie | 24 |
| pomarańcze 12 | pomarańcze | 12 |
| brzoskwinie0 | brzoskwinie | 0 |
Jak wspomniano powyżej, kluczem w tym przypadku jest zlokalizowanie pozycji początkowej liczby, co można zrobić za pomocą następującego wzoru:
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),A1&"0123456789"))
Gdy masz już pozycję, aby wyodrębnić tylko tekst, użyj:
=LEFT(A1,position-1)
Aby wyodrębnić tylko liczbę, użyj:
=RIGHT(A1,LEN(A1)-position+1)
W pierwszym wzorze powyżej używamy funkcji ZNAJDŹ, aby zlokalizować pozycję początkową liczby. Dla funkcji find_text używamy stałej tablicowej (0,1,2,3,4,5,6,7,8,9), co powoduje, że funkcja FIND przeprowadza oddzielne wyszukiwanie dla każdej wartości w stałej tablicy. Ponieważ stała tablicowa zawiera 10 liczb, wynikiem będzie tablica z 10 wartościami. Na przykład, jeśli oryginalny tekst to „jabłka30”, wynikowa tablica będzie wyglądać tak:
(8,10,11,7,13,14,15,16,17,18)
Każda liczba w tej tablicy reprezentuje pozycję elementu w stałej tablicy wewnątrz oryginalnego tekstu.
Następnie funkcja MIN zwraca najmniejszą wartość na liście, która odpowiada pozycji pierwszej liczby, która pojawia się w oryginalnym tekście. Zasadniczo funkcja ZNAJDŹ pobiera wszystkie pozycje liczbowe, a MIN daje nam pierwszą pozycję liczby: zauważ, że 7 jest najmniejszą wartością w tablicy, która odpowiada pozycji liczby 3 w oryginalnym tekście.
Być może zastanawiasz się nad dziwną konstrukcją ciągu within_text w funkcji find:
B5&"0123456789"
Ta część formuły łączy każdą możliwą liczbę 0-9 z oryginalnym tekstem w B5. Niestety funkcja ZNAJDŹ nie zwraca zera, gdy wartość nie zostanie znaleziona, więc jest to po prostu sprytny sposób na uniknięcie błędów, które mogą wystąpić, gdy liczba nie zostanie znaleziona.
W tym przykładzie, ponieważ zakładamy, że liczba zawsze będzie występować jako druga w oryginalnym tekście, działa to dobrze, ponieważ MIN wymusza zwrócenie tylko najmniejszego lub pierwszego wystąpienia liczby. Dopóki liczba pojawi się w oryginalnym tekście, ta pozycja zostanie zwrócona.
Jeśli oryginalny tekst nie zawiera żadnych liczb, zostanie zwrócona pozycja „fałszywa” równa długości tekstu oryginalnego + 1. W przypadku tej fałszywej pozycji formuła LEWA powyżej nadal zwróci tekst, a formuła PRAWA zwróci pusty ciąg („”).
