Chcemy wyciągnąć liczbę z początku lub końca tekstu. Najpierw zajmiemy się pisaniem formuły do wyciągnięcia liczby z końca tekstu.

Jednym ze sposobów jest zaczęcie od znalezienie pozycji kolejnych cyfr. Możemy wykorzystać funkcję SZUKAJ.TEKST lub ZNAJDŹ. Obie znajdują pozycję szukanego tekstu (w naszym przykładzie liczby) w przeszukiwanym tekście, ale funkcja SZUKAJ.TEKST nie zwraca uwagi na wielkość liter, a funkcja ZNAJDŹ zwraca uwagę na wielkość liter. Ponieważ szukamy cyfr skorzystamy z funkcji, która ma krótszą nazwę.

Dalej ważne jest, że chcemy znaleźć pierwszą pozycję każdej cyfry. Żeby to zrobić musimy wpisać wszystkie cyfry w nawiasach klamrowych oddzielone slashami

{0\1\2\3\4\5\6\7\8\9}

Można sobie ułatwić życie i stworzyć serię cyfr w wierszu, odwołać się do niej, a następnie zaznaczyć całe odwołanie do tej serii i nacisnąć klawisz F9 – wtedy Excel zamiast odwołania wstawi potrzebną tablicę.

To, że zamiast odwołania do komórek skorzystamy z tablicy wpisanej na stałe (hardkodowej) pozwoli nam zatwierdzać formułę zwyczajnie, a nie jako formułę tablicową (za pomocą kombinacji klawiszy Ctrl + Shift + Enter).

=ZNAJDŹ({0\1\2\3\4\5\6\7\8\9}; A2)

Jednak przy wyszukiwaniu poszczególnych cyfr pojawią się błędy, gdy danej cyfry nie ma w przeszukiwanym tekście.

{10\#ARG!\#ARG!\#ARG!\#ARG!\11\#ARG!\#ARG!\#ARG!\#ARG!}

Obsługa błędów może być kłopotliwa, dlatego skorzystamy ze sztuczki, która ułatwi nam zadanie – do oryginalnego tekstu dołożymy na koniec ciąg wszystkich cyfr.

=ZNAJDŹ({0\1\2\3\4\5\6\7\8\9}; A2&"0123456789")

Dzięki temu zawsze znajdziemy każdą cyfrę, co najwyżej poza długością przeszukiwanego tekstu.

={10\13\14\15\16\11\18\19\20\21}

Ponieważ nas interesuje pierwsze wystąpienie jakiejkolwiek cyfry dlatego naszą dotychczasową formułę wstawimy do funkcji MIN.

=MIN(ZNAJDŹ({0\1\2\3\4\5\6\7\8\9}; A2&"0123456789"))

Teraz żeby odnaleźć ile znaków chcemy wyciągnąć z prawej strony tekstu (funkcja PRAWY) wystarczy znalezioną pozycję pierwszej cyfry odjąć od długości całego tekstu i dodać korygującą jedynkę.

=PRAWY(A2;DŁ(A2)-MIN(ZNAJDŹ({0\1\2\3\4\5\6\7\8\9}; A2&"0123456789"))+1)

Jak wyciągnęliśmy liczbę z końca tekstu to możemy teraz w łatwy sposób wyciągnąć sam tekst odejmując od długości całego tekstu długość liczby.

=LEWY(A2;DŁ(A2)-DŁ(C2))

Teraz zajmiemy się przypadkiem, gdy nasza liczba jest na początku tekstu. Musimy podejść do tego inaczej. Zaczniemy od tego, że usuniemy z tekstu po kolei każdą cyfrę za pomocą funkcji PODSTAW i pustego ciągu znaków:

=PODSTAW(I2;{0\1\2\3\4\5\6\7\8\9};"")

Uzyskamy wtedy dziesięć tekstów, w których nie będą się znajdować poszczególne cyfry, czyli w większości będzie to cały tekst, bo danej cyfry już na początku nie będzie w tekście.

={"5 Czekoladki"\"05 Czekoladki"\"05 Czekoladki"\"05 Czekoladki"\"05 Czekoladki"\"0 Czekoladki"\"05 Czekoladki"\"05 Czekoladki"\"05 Czekoladki"\"05 Czekoladki"}

Tylko, że nam nie są potrzebne same teksty, tylko ich długość, czyli naszą formułę wstawiamy do funkcji DŁ. Znowu ponieważ wpisaliśmy na stałe tablicę cyfr (hardkodowanie) nie musimy zatwierdzać naszej formuły jako formuły tablicowej.

=DŁ(PODSTAW(I2;{0\1\2\3\4\5\6\7\8\9};""))

Dzięki temu uzyskamy długość tekstu po usunięciu każdej kolejnej cyfry.

={12\13\13\13\13\12\13\13\13\13}

Czasami widać różnicę, do oryginalnej długości tekstu i właśnie tej różnicy szukamy. Wystarczy, że od długości oryginalnego tekstu odejmiemy te poszczególne długości.

=DŁ(I2)-DŁ(PODSTAW(I2;{0\1\2\3\4\5\6\7\8\9};""))

Wynikiem będzie tablica różnic

={1\0\0\0\0\1\0\0\0\0}

Zostaje nam ją tylko zsumować i mamy długość całej liczby, która znajduje się na początku tekstu.

=SUMA(DŁ(I2)-DŁ(PODSTAW(I2;{0\1\2\3\4\5\6\7\8\9};"")))

Teraz wystarczy, że tą sumę wstawimy do funkcji LEWY i udało nam się wyciągnąć szukaną liczbę na początku tekstu.

=LEWY(I2;SUMA(DŁ(I2)-DŁ(PODSTAW(I2;{0\1\2\3\4\5\6\7\8\9};""))))

Jak już wyciągnęliśmy liczbę, to z łatwością wyciągnąć możemy tekst, ewentualnie usuwając zbędne spacje:

=USUŃ.ZBĘDNE.ODSTĘPY(PRAWY(I2;DŁ(I2)-DŁ(J2)))

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP