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:
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:
- Dlugosc – długość nazwy produktu
- Znak – pojedynczy znak wyodrębniany do sprawdzenia
- 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 FunctionZauważ 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
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
Powiązane produkty:
- WEBINAR: Konwersja tekstu na liczbę odpowiadający na pytanie dlaczego w ogóle Excel czasem traktuje liczby/ daty jako tekst oraz prezentujący szereg metod na radzenie sobie z takimi sytuacjami. Dodatkowo pokazana jest też metoda na sytuację odwrotną: gdy chcemy z liczby stworzyć tekst.
Poniżej plik z gotowcem do pobrania (zawiera makra oczywiście):
I wersje wideo dla funkcji GramaturaTekst w VBA:
I dla wbudowanych funkcji tekstowych:
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ć?
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.
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
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
🙂
Do takich rzeczy najlepszy jest RegEx.
Function GramaturaTekstRegex(Produkt As String, opcja As String) As String
Dim strPattern As String: strPattern = „(.*\s)([0-9,]+)([g|kg|l|ml])(\s?.*)”
Dim regEx As New RegExp
With regEx
.IgnoreCase = False
.Pattern = strPattern
End With
If regEx.Test(Produkt) Then
If opcja = „1” Then
GramaturaTekstRegex = regEx.Replace(Produkt, „$2”)
ElseIf opcja = „0” Then
GramaturaTekstRegex = regEx.Replace(Produkt, „$3”)
Else
GramaturaTekstRegex = „zły argument: 1-gramatura, 0-jednostka”
End If
Else
GramaturaTekstRegex = „”
End If
End Function
Super! Wyrażenia regularne – geniusz 🙂
Dziękuję za kod.
Z początku nie działał (jestem laikiem) przez brak referencji vba microsoft vbscript regular expressions 5.5.
Umieszczam alternatywę, która nie wymaga dodawania ww. referencji.
Zamiast:
Dim regEx As New RegExp
Można użyć:
Dim regEx As Object
Set regEx= CreateObject(„VBScript.RegExp”)
Do moich potrzeb zmodyfikowałem też lekko pattern
(.*\D\s?)(\d+,?\d*)\s?(kg|g|l|ml)(.*)”
Zmieniłem spację przed gramaturą na opcjonalną (często ograniczenia znaków nazw zmuszają do ich skracania kosztem spacji)
Aby uniknąć błędnego wyniku Produkt2 200g (zły wynik: 2200g) dodałem \D co oznacza, że ostatni znak przed spacją nie może być cyfrą.
Tu mam problem z wynikiem gdyby nazwa była faktycznie bez spacji i kończyła się liczbą: Produkt2200g (może jakieś pomysły???)
W ostatnim ciągu pozbyłem się granicy słowa ponieważ (niestety) w bazie danych mam różne zapisy gramatur (tabs tab tablets itp).
Dziękuję 🙂
Nie wyobrażam sobie pracy z tekstem bez wyrażeń regularnych. Podstawowa wiedza może być przyswojona dosyć łatwo a korzyści z ich zastosowania przeogromne. Proponuję osobną lekcję na ten temat 🙂
Napisany przeze mnie na szybko pattern nie do końca działa. Poświęciłem więcej czasu na przetestowanie rozmaitych przypadków i ulepszona wersja powinna wyglądać tak:
(.*\s)(\d+,?\d*)\s?(kg|g|l|ml)(\b.*)
Jak to czytać? Sprawdzamy czy nasz tekst pasuje do następującego wzorca:
– dowolny początek tekstu: .* aż do spacji \s
– pierwsza grupa którą chcemy zwrócić jako gramaturę:
jedna lub więcej cyfr, opcjonalny przecinek, opcjonalne kolejne cyfry (jeśli pojawi się drugi przecinek to już nie traktujemy jako liczbę, nie zostanie to zwrócone)
– opcjonalna spacja, może być ale nie musi
– druga grupa którą chcemy zwrócić jako jednostkę:
dopuszczamy tylko jednostki wymienione w nawiasie i rozdzielone znakiem | który oznacza logiczny LUB
– po jednostce obowiązkowo granica słowa – po to aby ciąg 1mleko nie został złapany jako 1 ml
– po jednostce może być jeszcze dowolny ciąg znaków
Jeśli w tekście będzie więcej niż jedno dopasowanie to zwrócona zostanie wartość ostatnia z prawej. W tej sytuacji należy ponownie zmodyfikować wzorzec i zdecydować co ma być zwrócone.
Skuteczność tego kodu sprawdziłem na różnych nietypowych przypadkach:
Piwo2x2 perla 0,5ml mocna pusz.
Zupa kucharek barszcz biały 40gr
Napoj mirinda ananas 1,0l pet
Piwo karpackie 0,5l mocne but.zwr.
Jajka z mlecznej czekol.polonia 10,5g
Cukier 2×2 drobny 5 kg diamant
Ciastka alpino z jab.i miodem 150,1,0g
Ciastka alpino z rodzynkami 150g
szampon 2w1 40ml 1st class
Posti herbata 1kg lisciasta ziolowa mieta 40g,
Mlekpol mleko 3,2% 500ml karton12x12
Posti herbata lisciasta ziolowa pokrzywa 40g
Mlekpol 10mleko
Iga markizy 140,00g z kremem orzechowym
Hej, dziękuję za tak obszerne informacje! Zdecydowanie temat ten zasługuje na oddzielny artykuł. Widzę, że Ty wiesz na ten temat bardzo dużo, a z pewnością więcej niż ja. Może chciałbyś napsiać gościnny artykuł na moim blogu na ten temat? Co myślisz?
A jak zmodyfikować ten kod aby móc wyciągnać z nazwy jeden konkretny wyraz np. herbata?
Szczere dzięki ale nie będę Ci wchodził w kompetencje, zresztą twój blog to nie tylko artykuł ale i wideo 😉 Ale jeśli tylko będziesz chciała zrobić o tym wpis i będziesz potrzebować pomocy to chętnie pomogę. Masz mojego maila więc jakby co to pisz śmiało.
Robik, ok, rozumiem. Powiem Ci, że chętnie wejdę w temat wyrażeń regularnych. Wtedy na pewno się zgłoszę do Ciebie i o nich napiszę na blogu! A póki co, dla zainteresowanych zostanie Twój komentarz i pozwolę sobie wrzucić linka: https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops . Sama chętnie z niego skorzystam jak będę zgłębiać temat 🙂
A co jeśli chciałabym wyciągnąć konkretny wyraz z nazwy?
Np. nazwa „Napoj mirinda ananas 1,0l pet” jest w kolumnie A, ja chce wyciągnać słowo „mirinda” do kolumny L.
Oczywiście chodzi o masowe wyciągnie ściśle określonych wyrazów.
Hej,
a powiedz co jest celem takiego wyodrębniania? Bo na pewno trzeba byłoby zdefiniować te słowa, które mają być wyodrębnione, chyba że istnieje jakaś reguła, że np. słowo występuje zawsze jako drugie we wpisie. Na pewno to będzie kombinowanie…