Potrzebujemy wyciągnąć wszystkie cyfry z tekstu jako liczbę. Nasze ograniczenia są takie, że nie możemy skorzystać z pomocy funkcji POŁĄCZ.TEKSTY czy ZŁĄCZ. TEKST, a dodatkowo nasza formuła nie może być zatwierdzana za pomocą kombinacji klawiszy Ctrl + Shift + Enter. To ciężkie zadanie, ale już ktoś taką formułę wymyślił:

https://www.mrexcel.com/forum/excel-questions/443983-extract-only-numbers-text-string.html
Poniżej opiszemy ją krok po kroku.

Żeby wyciągnąć wszystkie cyfry musimy wyciągnąć każdy znak i sprawdzić, czy jest on cyfrą/liczbą. Możemy to zrobić za pomocą funkcji FRAGMENT.TEKSTU, która wyciąga z testu fragment od wskazanego numeru znaków o wskazanej długości. W tym przykładzie będziemy chcieli wyciągać pojedyncze znaki, czyli długość tekstu = 1. Większym problemem jest to, że potrzebujemy wyciągnąć każdy pojedynczy znak, czyli najpierw wyciągamy pierwszy znak, potem drugi itd, czyli potrzebujemy tablicy znaków, która liczy od 1 do ilości znaków w tekście, który analizujemy.

Żeby to zrobić skorzystamy ze sztuczki, którą znajdziesz w internecie – wykorzystując funkcję ADR.POŚR odwołamy się od wiersza 1 do długości ciągu tekstowego:

=ADR.POŚR("1:"&DŁ(A2))

Porada 296 - Jak wyciągnąć wszystkie cyfry z tekstu bez POŁĄCZ.TEKSTY i formuł tablicowych 01

Tylko to dużo więcej niż potrzebujemy. Nam wystarczą tylko numery wierszy, czyli wstawiamy powyższą formułę do funkcji WIERSZ.

=WIERSZ(ADR.POŚR("1:"&DŁ(A2)))

Właśnie zbudowaliśmy tablicę numerującą znaki do funkcji FRAGMENT.TEKSTU.

{1;2;3;4;5;6;7;8;9;10;11;12;13;14}

Wystarczy ją tylko w niej umieścić pamiętając, że zawsze wyciągamy pojedyncze znaki:

=FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1)

Uzyskamy dzięki temu tekst rozłożony na pojedyncze znaki (tablicę znaków):

{"5";" ";"S";"u";"k";"i";"e";"n";"k";"a";" ";"1";"0";"0"}

Tylko poważnym problemem jest to, że funkcja FRAGMENT.TEKSTU wszystkie znaki zwraca jako tekst (są w podwójnych cudzysłowach). Na szczęście dowolna operacja matematyczna na liczbie zapisanej jako tekst zamienia ją ponownie na „prawdziwą” liczbę ? Skorzystamy z tutaj podwójnej negacji, czyli dwóch znaków minus przed funkcją. Wynikiem tego będzie tablica liczb i błędów

{5;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;1;0;0}

Teraz możemy sprawdzić gdzie są liczby za pomocą funkcji CZY.LICZBA.

=CZY.LICZBA(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1))

Porada 296 - Jak wyciągnąć wszystkie cyfry z tekstu bez POŁĄCZ.TEKSTY i formuł tablicowych 02

Wynikiem tej formuły jest tablica wartości logicznych PRAWDA (którym chcemy przypisać ich pozycję na liście) i FAŁSZ (którym chcemy przypisać 0)

{PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;PRAWDA;PRAWDA}

Żeby to zrobić wystarczy, że przemnożymy ją przez już wcześniej stworzoną tablicę po numerach wierszy

=CZY.LICZBA(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1))*WIERSZ(ADR.POŚR("1:"&DŁ(A2)))

Porada 296 - Jak wyciągnąć wszystkie cyfry z tekstu bez POŁĄCZ.TEKSTY i formuł tablicowych 03

Wynikiem tej formuły będzie tablica z pozycjami cyfr i zerami tam gdzie cyfr nie było:

{1;0;0;0;0;0;0;0;0;0;0;12;13;14}

Do naszych potrzeb potrzebujemy ustawić tą tablicę od największych wartości do najmniejszych, czyli skorzystamy z funkcji MAX.K, ale zanim to zrobimy wykonamy pewną sztukę, która sprawi, że na zakończenie nie będziemy musieli zatwierdzać naszej formuły jako formuły tablicowej. Sztuczka ta polega na wstawieniu naszej formuły do funkcji INDEKS i wyciągnięcia całego wiersza danych wpisując w drugim argumencie funkcji INDEKS wartości zero.

=INDEKS(CZY.LICZBA(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1))*WIERSZ(ADR.POŚR("1:"&DŁ(A2)));0)

Nie wiem dokładnie dlaczego ta sztuczka działa, bo podglądając wynik wygląda on tak samo z i bez funkcji INDEKS, ale najważniejsze, że działa. Teraz jak wspomnieliśmy wkładamy naszą formułę do funkcji MAX.K, a jako argument k posłuży nam znów liczenie po kolei wierszy.

MAX.K(INDEKS(CZY.LICZBA(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1))*WIERSZ(ADR.POŚR("1:"&DŁ(A2)));0);WIERSZ(ADR.POŚR("1:"&DŁ(A2))))

Porada 296 - Jak wyciągnąć wszystkie cyfry z tekstu bez POŁĄCZ.TEKSTY i formuł tablicowych 04

Podglądając wynik zobaczymy najprawdopodobniej dużo zer.

{14;13;12;1;0;0;0;0;0;0;0;0;0;0}

Niestety będą nam te zera przeszkadzać później w funkcji FRAGMENT.TEKSTU, bo nie możemy wyciągać znaków od zerowego znaku (taki nie istnieje). Żeby się ich pozbyć po prostu dodajemy jedynkę do naszej formuły (+1). Jednak jeśli wykonamy taką operacje to przesuną się znaki, które będziemy wyciągać, dlatego dodatkowo dołożymy na początek naszego tekstu zero (0&A2)

=FRAGMENT.TEKSTU(0&A2;MAX.K(INDEKS(CZY.LICZBA(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1))*WIERSZ(ADR.POŚR("1:"&DŁ(A2)));0);WIERSZ(ADR.POŚR("1:"&DŁ(A2))))+1;1)

Porada 296 - Jak wyciągnąć wszystkie cyfry z tekstu bez POŁĄCZ.TEKSTY i formuł tablicowych 05

Wynikiem tej formuły będą wyciągane cyfry od końca tekstu (od prawej strony) oraz dużo początkowych dodanych zer:

{"0";"0";"1";"5";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0"}

Musimy pamiętać, że to znowu są liczby, które Excel traktuje jako tekst. Musimy te wartości przemnożyć przez liczby w zależności od ich pozycji. Dokładnie pierwszą wartość potrzebujemy przemnożyć przez 1, drugą przez 10, trzecią przez 100 itd. Są to kolejne potęgi 10, więc wystarczy, żebyśmy 10 podnieśli do potęgi pozycji cyfry na liście. Czyli znów korzystamy z listy wierszy:

10^WIERSZ(ADR.POŚR("1:"&DŁ(A2)))/10

Tylko potęgowanie musimy podzielić przez 10, żebyśmy zaczęli od 1, a nie od 10.

{1;10;100;1000;10000;100000;1000000;10000000;100000000;1000000000;10000000000;100000000000;1000000000000;10000000000000}

Połączona formuła wygląda tak:

=FRAGMENT.TEKSTU(0&A2;MAX.K(INDEKS(CZY.LICZBA(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1))*WIERSZ(ADR.POŚR("1:"&DŁ(A2)));0);WIERSZ(ADR.POŚR("1:"&DŁ(A2))))+1;1)*10^WIERSZ(ADR.POŚR("1:"&DŁ(A2)))/10

A w wyniku daje odpowiednią tablicę liczb:

{0;0;100;5000;0;0;0;0;0;0;0;0;0;0}

Którą wystarczy zsumować za pomocą funkcji SUMA.ILOCZYNÓW, żebyśmy nie musieli zatwierdzać formuły jako tablicowej, czyli końcowa formuła wygląda tak:

=SUMA.ILOCZYNÓW(FRAGMENT.TEKSTU(0&A2;MAX.K(INDEKS(CZY.LICZBA(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1))*WIERSZ(ADR.POŚR("1:"&DŁ(A2)));0);WIERSZ(ADR.POŚR("1:"&DŁ(A2))))+1;1)*10^WIERSZ(ADR.POŚR("1:"&DŁ(A2)))/10)

Porada 296 - Jak wyciągnąć wszystkie cyfry z tekstu bez POŁĄCZ.TEKSTY i formuł tablicowych 06

Warto zwrócić uwagę na to, że ta formuła zwraca liczbę, a nie tekst składający się z cyfr i pomija wiodące zera w porównaniu do formuły z porady 295.

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