• Zapisz się na newsletter i odbierz DARMOWY EBOOK: 10 najprzydatniejszych porad excelowych

Wyodrębnianie gramatury produktów z ich nazwy, czyli liczby z tekstu

Czyli wyodrębniania liczby z tekstu za pomocą funkcji w VBA

W dzisiejszym wpisie sytuacja, której nie życzę nikomu.Sytuacja, która jednak się zdarza i to jak się okazuje często… Najlepiej zobrazuje to screen poniżej:

Formatka

I chodzi oczywiście o wyciągnięcie gramatury z nazwy produktu. Jest wiele fajnych metod na wyciąganie liczb z tekstu, jednak ta sytuacja jest wyjątkowo wredna, ponieważ te liczby są różnej długości, w różnych miejscach w tekście, są często dziesiętne, a nawet jak są całkowite, to zapisywane np. tak: 1,0 zamiast po prostu 1. Próbowałam wielu sposobów na ten przypadek, ale po wielu nieprzespanych nocach stwierdziłam, że jednak VBA będzie tutaj najlepszym wyjściem. Zawsze staram się unikać funkcji tworzonych w VBA, ponieważ są wolniejsze, muszą być przechowywane w pliku z rozszerzeniem .xlsm lub .xlsb itp. Jednak tutaj stwierdziłam, że to najlepsze wyjście.

Logika funkcji

Zadanie funkcji jest takie, że po kolei sprawdza każdy znak w nazwie produktu. Jeśli będzie on liczbą lub przecinkiem – zostanie zapamiętany w swoistym kontenerze (będzie to po prostu zmienna tekstowa). Na koniec zawartość tego kontenera zostanie wyświetlona jako wynik funkcji. Proste, prawda? Technicznie użyję do tego kilku zmiennych i oczywiście pętli (For). W VBA to kilka linijek, a z formułą trzeba byłoby się nabiedzić, zwłaszcza, że jest tyle wyjątków…

Tworzenie makra

1. Stwórz nowy plik Excela, który zapisz jako Skoroszyt programu Excel z obsługą makr .xlsm

2. Wejdź do edytora Visual Basica (Alt + F11)

3. Stwórz nowy moduł, czyli menu Insert / Module.

4. Do białego okienka po prawej strony wklej następujący kod:

Function GramaturaTekst(Produkt As String) As String
   Dim Dlugosc As Long, Znak As String, Licznik As Long
 
   Dlugosc = Len(Produkt)
 
   For Licznik = 1 To Dlugosc
      Znak = Mid(Produkt, Licznik, 1)
      If IsNumeric(Znak) Or Znak = "," Then GramaturaTekst = GramaturaTekst & Znak
   Next

End Function

Tworzysz w ten sposób funkcję o nazwie GramaturaTekst, którą od tej pory możesz wykorzystać w arkuszu, jak normalną funkcję Excela. Funkcja ta wymaga tylko jednego argumentu, czyli tekstu (nasz produkt), z którego ma wyciągnąć liczbę oznaczającą gramaturę.

Użyłam kilku zmiennych:

  1. Dlugosc – długość nazwy produktu
  2. Znak – pojedynczy znak wyodrębniany do sprawdzenia
  3. Licznik – zmienna techniczna dla pętli

Postanowiłam wyświetlać wynik funkcji, czyli liczbę, jako tekst, ze względu na to, że w nazwach produktów występują wartości typu 1,0l. Oczywiście jeśli tekst 1,0 przekonwertujemy na liczbę otrzymamy po prostu 1. Będzie to uciążliwe ze względu na późniejsze wyciąganie jednostki z tej samej nazwy produktu.

Jeśli jednak nie masz takich „kwiatków” w swoich danych, spokojnie możesz użyć takiej funkcji:

Function Gramatura(Produkt As String) As Double
 Dim Dlugosc As Long, Znak As String, Licznik As Long, Kontener As String
 
 Dlugosc = Len(Produkt)
 
 For Licznik = 1 To Dlugosc
    Znak = Mid(Produkt, Licznik, 1)
    If IsNumeric(Znak) Or Znak = "," Then Kontener = Kontener & Znak
 Next
 
 Gramatura = Kontener
End Function

Zauważ jednak, że dochodzi tutaj dodatkowa zmienna Kontener, która przechowuje w sobie wyodrębniane znaki. I sama w sobie funkcja jest typu Double, czyli zwraca liczbę, a nie tekst. Tak oczywiście tygryski lubią najbardziej :). Można oczywiście użyć wcześniejszej funkcji GramaturaTekst i już w samym Excelu przekonwertować ją na liczbę (np. =–GramaturaTekst(A1)). Obojętne, byle o tym pamiętać.

Efekt funkcji GramaturaTekst  jest następujący:

Wynik funkcji VBA

Wynik funkcji VBA

Jak widać, gramatura jest tekstem (jest wyrównana do lewej strony) i to jest tutaj ok, ponieważ będzie potrzebne do kolejnego kroku, czyli ustalania jednostki…

Ustalanie jednostki

To już zrobimy zwykłymi wbudowanymi funkcjami Excela. Damy radę! (jak Bob Budowniczy – jeden z ulubieńców moich dzieciaków :)).

Zaczniemy od funkcji FRAGMENT.TEKSTU, ponieważ potrzebujemy wyciągnąć pewien określony tekst z nazwy produktu. Oczywiście ten tekst jest różny i ciągle w innym miejscu, więc będzie sporo kombinacji.

Znowu logika jest taka, że ponieważ już znamy wartość liczbową, to możemy ustalić na której pozycji w tekście się ona znajduje (funkcją SZUKAJ.TEKST). Jednostka jest zawsze „przyklejona” do niej. Po jednostce jest zazwyczaj spacja. Zazwyczaj, ponieważ są sytuacje, gdzie jednostka jest po prostu ostatnim znakiem w tekście. I na to też trzeba będzie się zabezpieczyć (JEŻELI.BŁĄD).

W każdym razie, jak już poznamy pozycję liczby, będziemy mogli też poznać pozycję spacji zaraz za nią (znowu funkcją SZUKAJ.TEKST). Po odjęciu jednego od drugiego uzyskamy długość całej gramatury (DŁ), łącznie z jednostką. A z tego łatwo otrzymamy liczbę znaków jednostki – po prostu odejmiemy długość naszej liczby.

Formuła, która to wszystko robi jest dość długa, ale działa:

=FRAGMENT.TEKSTU(B4;SZUKAJ.TEKST(C4;B4)+DŁ(C4);JEŻELI.BŁĄD(SZUKAJ.TEKST(" ";B4;SZUKAJ.TEKST(C4;B4));DŁ(B4)+1)-SZUKAJ.TEKST(C4;B4)-DŁ(C4))

Oczywiście łatwiej byłoby, gdyby nasze jednostki zawsze były w środku tekstu – wtedy nie byłoby konieczności funkcji JEŻELI.BŁĄD, ale tak jednak nie jest 🙂

Wynik wszystkiego wygląda tak:

Wynik

Wynik

Poniżej plik z gotowcem do pobrania (zawiera makra oczywiście):

MalinowyExcel Gramatura w nazwach produktów dw.xlsx

I wersje wideo:

 

Spodobał Ci się ten artykuł?

Jeśli TAK, zapisz się na newsletter, aby otrzymywać powiadomienia
o najnowszych wpisach (i tylko powiadomienia: zero spamu).
Dodatkowo otrzymasz DARMOWY EBOOK pt.:

10 najprzydatniejszych porad excelowych


Tagi , , , , , , , , , , , , , , .Dodaj do zakładek Link.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *