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.

Porada 294 - Jak wyciągnąć liczbę z początka lub końca tekstu 01

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)

Porada 294 - Jak wyciągnąć liczbę z początka lub końca tekstu 02

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")

Porada 294 - Jak wyciągnąć liczbę z początka lub końca tekstu 03

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}

Porada 294 - Jak wyciągnąć liczbę z początka lub końca tekstu 04

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"))

Porada 294 - Jak wyciągnąć liczbę z początka lub końca tekstu 05

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)

Porada 294 - Jak wyciągnąć liczbę z początka lub końca tekstu 06

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))

Porada 294 - Jak wyciągnąć liczbę z początka lub końca tekstu 07

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};"")

Porada 294 - Jak wyciągnąć liczbę z początka lub końca tekstu 08

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 . 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};""))

Porada 294 - Jak wyciągnąć liczbę z początka lub końca tekstu 09

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};""))

Porada 294 - Jak wyciągnąć liczbę z początka lub końca tekstu 10

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};"")))

Porada 294 - Jak wyciągnąć liczbę z początka lub końca tekstu 11

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};""))))

Porada 294 - Jak wyciągnąć liczbę z początka lub końca tekstu 12

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)))

Porada 294 - Jak wyciągnąć liczbę z początka lub końca tekstu 13

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