• Zapisz się do newslettera, aby otrzymywać powiadomienia o nowościach na blogu
    Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Wyodrębnianie gramatury produktów z ich nazwy, czyli liczba 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 dla funkcji GramaturaTekst w VBA:

I dla wbudowanych funkcji tekstowych:

 

Spodobał Ci się ten artykuł?

Jeśli TAK, zapisz się na newsletter, aby otrzymywać powiadomienia
o o nowościach na blogu.

Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

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

4 odpowiedzi na „Wyodrębnianie gramatury produktów z ich nazwy, czyli liczba z tekstu

  1. Wojtek mówi:

    Przydatna sprawa, ale co jeśli w nazwie produktu znajdą się jeszcze jakieś inne liczby niż te , które dotyczą wagi/pojemności? Np. Szampon 2w1 200ml. Albo co jeśli pomiędzy wagą a określeniem jednostki jest spacja : szampon 200 ml ?
    Da się to jakoś prosto zmodyfikować?

    • Malina mówi:

      Hmmm… myślę, że najprościej byłoby wypisać sobie takie wyjątki jak np. 2w1 czy 3w1 i je pomijać (czyli jak pętla je napotka – idzie do kolejnego znaku, a najlepiej do +2 kolejnego). Z takim słownikiem byłoby łatwiej. Dla spacji po liczbie można też przejechać najpierw narzędziem “zamień”, tak aby zamienić ” ml ” –> “ml ” itp.

      • Mateusz mówi:

        Dziękuje bardzo za bardzo użyteczny kod.
        Potrzebuje pomocy żeby go troszkę udoskonalić.

        W sytuacji gdy w wersie jest kilka liczb, jak zrobić, żeby je wszystkie wyszczególniło i oddzieliło spacją?

        z góry dziękuję za pomoc.

        PS. jestem początkującym w VBA

        • Malina mówi:

          Hej,
          tutaj bym zastosowała już inny kod:

          Function GramaturaTekstKilka(Produkt As String) As String
          Dim Dlugosc As Long, Licznik As Long, Znak As String
          Dim Slowa() As String, Elementow As Long

          Dlugosc = Len(Produkt)
          Slowa = Split(Produkt, ” “)
          Elementow = UBound(Slowa)
          ‘UBound zwraca najwyższy indeks

          For Licznik = 0 To Elementow
          Znak = Mid(Slowa(Licznik), 1, 1)
          If IsNumeric(Znak) Then GramaturaTekstKilka = GramaturaTekstKilka & ” ” & Slowa(Licznik)
          Next

          GramaturaTekstKilka = Trim(GramaturaTekstKilka)

          End Function

          🙂

Dodaj komentarz

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