0
0 Produkty w koszyku

No products in the cart.

Kwota słownie liczba słownie — odcinek #21

Chyba każdy program księgowy ma taką funkcjonalności, że potrafi zamienić liczbową reprezentację kwoty na jej reprezentację słowną, czyli przykładowo:

123, 45zł -> sto dwadzieścia trzy złote i czterdzieści pięć groszy

Jak to zrobić w Excelu? Potrzeba odpowiedni kod. Niestety nie jest znany mi jego autor, bo na żadnej stronie, gdzie go widziałem nikt nie wiedział kto go napisał. Mi on się bardzo podoba, bo jak się go odpowiednio podzieli jest naprawdę prosty do zrozumienia.
Ja żeby lepiej go zrozumieć dołożyłem do niego jeszcze część odpowiedzialną za przekształcenie miliardów.

Kod składa się z dwóch funkcji, które z komentarzami zamieszczam poniżej:

Function Słownie(x As Variant) As String 'dla liczb od -999 999 999 999.99 do 999 999 999 999.99
'Sprawdzamy czy podana kwota jest mniejsza od zera jeśli jest to dopisujemy do kwoty słownie słowo minus
If x < 0 Then w = w & "minus "

'Konwertujemy liczbę na zapis, który będzie pokazywał zawsze miliardy miliony, tysiące i jednostki
'nawet jeśli będą to same zera
x = Format(Abs(x), "000 000 000 000.00")
'części odpowiedzialne za miliardy, miliony itd. przypisujemy do oddzielnych zmiennych 3 cyfrowych
'wiodące zera również są w nich brane pod uwagę, a do groszy musimy dodać jedna zero na początku
'żeby później działało odpowiednio z funckją trzy
mld = Left(x, 3): m = Mid(x, 5, 3): t = Mid(x, 9, 3): j = Mid(x, 13, 3): g = "0" & Right(x, 2)

'Sprawdzamy miliardy i w zależności od ich ilości nadajemy inne końcówki
'analogicznie robimy z milionami, tysiącami, jednościami i groszami
Select Case mld
Case 0
Case 1
w = "jeden miliard "
Case Else
'za pomocą funkcji trzy sprawdzamy jaka jest liczba miliardów, a późniejszych częściach milionów itd.
w = w & trzy(mld)
'dodajemy odpowiedni "końcówek" na podstawie połączeniu odpowiednich warunków
If Mid(mld, 2, 1) <> 1 And (Right(mld, 1) = 2 Or Right(mld, 1) = 3 Or Right(mld, 1) = 4) Then w = w & "miliardy " Else w = w & "miliardów "
End Select

'Sprawdzanie milionów analogicznie jak miliardów
Select Case m
Case 0
Case 1
w = "jeden milion "
Case Else
w = w & trzy(m)
If Mid(m, 2, 1) <> 1 And (Right(m, 1) = 2 Or Right(m, 1) = 3 Or Right(m, 1) = 4) Then w = w & "miliony " Else w = w & "milionów "
End Select

'Sprawdzanie tysięcy analogicznie jak miliardów
Select Case t
Case 0
Case 1
w = w & "jeden tysiąc "
Case Else
w = w & trzy(t)
If Mid(t, 2, 1) <> 1 And (Right(t, 1) = 2 Or Right(t, 1) = 3 Or Right(t, 1) = 4) Then w = w & "tysiące " Else w = w & "tysięcy "
End Select

'Sprawdzanie jedności analogicznie jak miliardów
Select Case j
Case 0
If mld = 0 And m = 0 And t = 0 Then w = w & "zero złotych " Else w = w & "złotych "
Case 1
If mld = 0 And m = 0 And t = 0 Then w = w & "jeden złoty " Else w = w & "jeden złotych "
Case Else
w = w & trzy(j)
If Mid(j, 2, 1) <> 1 And (Right(j, 1) = 2 Or Right(j, 1) = 3 Or Right(j, 1) = 4) Then w = w & "złote " Else w = w & "złotych "
End Select

'sprawdzanie groszy analogicznie jak miliardów
Select Case g
Case 0
w = w '& "zero groszy"
Case 1
w = w & "jeden grosz"
Case Else
w = w & trzy(g)
If Mid(g, 2, 1) <> 1 And (Right(g, 1) = 2 Or Right(g, 1) = 3 Or Right(g, 1) = 4) Then w = w & "grosze" Else w = w & "groszy"
End Select

Słownie = w
End Function


Function trzy(x As Variant) As String
'dzielimy trzy cyfry na część odpowiedzialną za setki, dziesiątki i jedności
x3 = Val(Left(x, 1)): x2 = Val(Mid(x, 2, 1)): x1 = Val(Right(x, 1))
'Spradzamy jaka to setka
If x3 = 9 Then w = w & "dziewięćset "
If x3 = 8 Then w = w & "osiemset "
If x3 = 7 Then w = w & "siedemset "
If x3 = 6 Then w = w & "sześćset "
If x3 = 5 Then w = w & "pięćset "
If x3 = 4 Then w = w & "czterysta "
If x3 = 3 Then w = w & "trzysta "
If x3 = 2 Then w = w & "dwieście "
If x3 = 1 Then w = w & "sto "
'Sprawdzamy jaka to dziesiątka łącząc z ewentualną setką
If x2 = 9 Then w = w & "dziewięćdziesiąt "
If x2 = 8 Then w = w & "osiemdziesiąt "
If x2 = 7 Then w = w & "siedemdziesiąt "
If x2 = 6 Then w = w & "sześćdziesiąt "
If x2 = 5 Then w = w & "pięćdziesiąt "
If x2 = 4 Then w = w & "czterdzieści "
If x2 = 3 Then w = w & "trzydzieści "
If x2 = 2 Then w = w & "dwadzieścia "
'Sprawdzamy czy to nie jest nastka łącząc z ewentualną setką
If x2 = 1 Then
If x1 = 9 Then w = w & "dziewiętnaście "
If x1 = 8 Then w = w & "osiemnaście "
If x1 = 7 Then w = w & "siedemnaście "
If x1 = 6 Then w = w & "szesnaście "
If x1 = 5 Then w = w & "piętnaście "
If x1 = 4 Then w = w & "czternaście "
If x1 = 3 Then w = w & "trzynaście "
If x1 = 2 Then w = w & "dwanaście "
If x1 = 1 Then w = w & "jedenaście "
If x1 = 0 Then w = w & "dziesięć "
End If
'Sprawdzamy jaka to cyfra jedności i dołączamy do ewentualnej wcześniejszej części
If x2 <> 1 Then
If x1 = 9 Then w = w & "dziewięć "
If x1 = 8 Then w = w & "osiem "
If x1 = 7 Then w = w & "siedem "
If x1 = 6 Then w = w & "sześć "
If x1 = 5 Then w = w & "pięć "
If x1 = 4 Then w = w & "cztery "
If x1 = 3 Then w = w & "trzy "
If x1 = 2 Then w = w & "dwa "
If x1 = 1 Then w = w & "jeden "
End If
trzy = w
End Function

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

Jak wyciągnąć liczbę z początku lub końca tekstu alternatywna formuła — porada #297

Bill Szysz pod poradą 294 napisał jeszcze jedną ciekawą formułę na wyciąganie liczb z początku lub końca tekstu. Ważne jest, że formuła, którą zobaczymy wyciąga liczby, czyli nie zwróci wiodących zer. Za to jest prostą formułą w porównaniu do tych, które stosowaliśmy.

Porada 297 - Jak wyciągnąć liczbę z początku lub końca tekstu (alternatywna formuła) 01

Będziemy musieli wyciągać coraz dłuższe ciągi z naszego tekstu odpowiednio z prawej strony (od końca – funkcja PRAWY) lub z lewej strony (od początku – funkcja LEWY). Zwiększającą się długość zapewnimy sobie za pomocą funkcji WIERSZ, która będzie wskazywała na zablokowany zakres – załóżmy, że od komórki A1 do A17, ale śmiało może przedłużyć ten zakres jeśli masz dłuższe teksty. Ważne, że zakres musi zaczynać się od komórki A1 (pierwszego wiersza) i być zablokowanym, żeby się nie przesuwał. 

=PRAWY(A2;WIERSZ($A$1:$A$17))

Porada 297 - Jak wyciągnąć liczbę z początku lub końca tekstu (alternatywna formuła) 02

Śmiało możesz zaznaczyć więcej wierszy nisz masz w tekście, bo w takiej sytuacji funkcja PRAWY (i analogicznie LEWY) wyciągają cały tekst.

={"5";"35";" 35";"a 35";"ka 35";"nka 35";"enka 35";"ienka 35";"kienka 35";"ukienka 35";"Sukienka 35";"Sukienka 35";"Sukienka 35";"Sukienka 35";"Sukienka 35";"Sukienka 35";"Sukienka 35"}

Wartości zwracane przez funkcje tekstowe są traktowane jako tekst, więc musimy je przekonwertować na liczby. Z wcześniejszych porad znamy już tą sztuczkę – to podwójna negacja – dwa znaki minus. Jeśli dany tekst da się przekonwertować na liczbę to Excel to zrobi. Jeśli jest to niemożliwe, to będzie zwracał błąd:

={5;35;35;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!}

W takiej tablicy wystarczy znaleźć największą liczbę. Można to zrobić za pomocą funkcji WYSZUKAJ i szukania dużej liczby np.: 9 do 99 potęgi:

=WYSZUKAJ(9^99;–PRAWY(A2;WIERSZ($A$1:$A$17)))

Porada 297 - Jak wyciągnąć liczbę z początku lub końca tekstu (alternatywna formuła) 03

To już cała formuła ? Analogicznie przy szukaniu liczb od początku tekstu musimy zamienić tylko funkcję PRAWY na LEWY i gotowe.

=WYSZUKAJ(9^99;–LEWY(D2;WIERSZ($A$1:$A$17)))

Na koniec trzeba powiedzieć o jeszcze jednym ograniczeniu tego sposobu – jeśli Excel jest wstanie zamienić jakiś zapis na liczbę (datę) za pomocą podwójnej negacji to to zrobi. Np. tekst „23 Czekolada” powinien zwracać poprawnie 23, ale wynik formuły jest całkiem inny:

Porada 297 - Jak wyciągnąć liczbę z początku lub końca tekstu (alternatywna formuła) 04

Wynika to, że w kolejnych wyciąganych tekstach pojawia się zapis „23 Cze”, który Excel tłumaczy sobie jako 23 czerwca bieżącego roku i zamienia na odpowiednią reprezentację liczbową. Stąd taki niespodziewany wynik formuły.

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

Jak wyciągnąć wszystkie cyfry z tekstu bez POŁĄCZ.TEKSTY i nie tablicowa — porada #296

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:"&(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:"&(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:"&(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:"&(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:"&(A2)));1))*WIERSZ(ADR.POŚR("1:"&(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:"&(A2)));1))*WIERSZ(ADR.POŚR("1:"&(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:"&(A2)));1))*WIERSZ(ADR.POŚR("1:"&(A2)));0);WIERSZ(ADR.POŚR("1:"&(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:"&(A2)));1))*WIERSZ(ADR.POŚR("1:"&(A2)));0);WIERSZ(ADR.POŚR("1:"&(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:"&(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:"&(A2)));1))*WIERSZ(ADR.POŚR("1:"&(A2)));0);WIERSZ(ADR.POŚR("1:"&(A2))))+1;1)*10^WIERSZ(ADR.POŚR("1:"&(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:"&(A2)));1))*WIERSZ(ADR.POŚR("1:"&(A2)));0);WIERSZ(ADR.POŚR("1:"&(A2))))+1;1)*10^WIERSZ(ADR.POŚR("1:"&(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

Jak wyciągnąć wszystkie cyfry z tekstu POŁĄCZ TEKSTY — porada #295

Przed nami ciężkie zadanie wyciągnięcia wszystkich cyfr. Okazuje się ono dużo prostsze jeśli w Twojej wersji Excel 2016 (lub 365) masz zainstalowany już update z funkcją POŁĄCZ.TEKSTY i ZŁĄCZ.TEKST (ważne! bez Y na końcu, bo ZŁĄCZ.TEKSTY działa inaczej).

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 01

Jeśli masz te funkcję to czytaj dalej. Jeśli nie to zajrzyj do następnej porady (296).

Ż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:"&(A2))

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:"&(A2)))

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 02

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:"&(A2)));1)

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 03

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}

Na szczęście jest nam to na rękę ponieważ nam zależy tylko na liczbach, a tam gdzie pojawił się błąd nie chcemy nic wyświetlać, więc wystarczy, że wstawimy naszą formułę do funkcji JEŻELI.BŁĄD, która zamiast błędów będzie zwracała pusty ciąg tekstowy (dwa podwójne cudzysłowy)

=JEŻELI.BŁĄD(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&(A2)));1);"")

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 04

Teraz formułą zwraca tablicę cyfr i pustych ciągów znaków, 

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

którą możemy łatwo połączyć za pomocą funkcji ZŁĄCZ.TEKST (przypominam bez Y na końcu) lub POŁĄCZ.TEKSTY jeśli zależy nam, żeby rozdzielać cyfry konkretnym ogranicznikiem, a nie bezpośrednio łączyć je ze sobą, ale w większości sytuacji chcemy je bezpośrednio łączyć, więc formuła będzie wyglądać tak:

=ZŁĄCZ.TEKST(JEŻELI.BŁĄD(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&(A2)));1);""))

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 05

Musimy tylko pamiętać, że jest to formuła tablicowa i musimy ją zatwierdzić kombinacją klawiszy Ctrl + Shift + Enter.

Przykład dla funkcji POŁĄCZ.TEKSTY z przecinkiem jako ogranicznikiem i pomijaniem pustych wartości (wartość logiczna PRAWDA) wygląda tak:

=POŁĄCZ.TEKSTY(",";PRAWDA;JEŻELI.BŁĄD(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&(A2)));1);""))

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 06

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

Jak wyciągnąć liczbę z początku lub końca tekstu — porada #294

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;(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;(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.

=(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.

=(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((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((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;(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