Najważniejsze funkcje tekstowe Excela przydają się w każdym dziale firmy, to jest w: księgowości, dziale finansów, administracyjnym, logistyki, sprzedaży, planowania czy marketingu. Przy pomocy funkcji tekstowych Excela możesz wyciągać fragment danych z komórki (np. imię i nazwisko z adresu mailowego czy fragment numer ID, odpowiadający za wskazanie kategorii produktu) czy zmieniać sposób prezentacji danych (napisać owe dane z wielkiej litery). Funkcje tekstowe zawsze w swoim rezultacie będą zwracać tekst, nawet jeśli na pierwszy rzut oka dane będą wyglądać jak liczby.
Jak skonwertować rezultaty najważniejszych funkcji tekstowych Excela na liczby?
W kolejnych akapitach wyjaśnię Ci działanie funkcji LEWY, ale narazie skup się na samej kownersji. Poniżej mamy numery pesel. Gdy wyciągniemy dwa znaki od lewej, nie będą one traktowane jako wartości liczbowe. Zwróć uwagę, że dane są domyslnie wyrównane do lewej. Może to stwarzać problem, gdy użylibyśmy tych danych do wyszukiwania czy w funkcjach logicznych.
[zrzut ekranu, gdzie wyciagasz z numeru PESEL 2 znaki od lewej i komórka jest wyrównana do lewej]
Jak zatem skonwertować takie dane na liczby?
Przemnóż lub podziel je przy użyciu jedynki. Możesz też dodać dwa minusy przed fomrułą. To spowoduje, że jej rezultatem od razu będzie liczba. Zwróć uwagę – dane są wyrównane do prawej i będzie można na nich wykonywać dalsze operacje matematyczne.
[dodaj zrzut gdzie jest formuła z — przed i rezultat jest wyrównany do prawej]
Najważniejsze funkcje tekstowe Excela to znaczy jakie?
Funkcja tekstowa LEWY (ang. LEFT)
składa się z 2 części.
W pierwszej częście formuły wybieramy komóek, z której chcemy wyciągnąc określoną liczbę znaków od lewej, którą to liczbę wskazujemy w drugiej części formuły.
I tak gdy chcemy z ID wyciągnąć pierwsza dwa znaki od lewej, użyjemy następującej składni:
=LEWY(A1;2)
=LEFT(A1;2)
[Obraz, gdzie w 1 kolumnie jest list ID, a obok zaznaczasz komóke, w której jest fukcja LEWY, wyciągająca dwa znaki od lewej)]
Funkcja tekstowa PRAWY (ang. RIGHT)
działa analogicznie do funkcji LEWY.
Różnia polega na tym, że znaki są wyciągane od prawej strony komórki.
[h3]Funkcja tekstowa FRAGMENT.TEKSTU (ang. MID)
składa się z trzech części. Pierwsza to tekst, na którym chcemy pracować. W drugiej części formuły tekstowej FRAGMENT.TEKSTU wskazujemy od któego znaku ma zostać wyciągnięty owy fragment. Część trzecia pozwala nam wskakzać ile znaków chcemy wyciągnąć.
Przykładem jest wyciągnięcie nazwiska z ciągu jan.kowalski:
=FRAGEMNT.TEKSTU(A1;5;8)
=MID(A1;5;5)
[Obraz – wstaw funkcję fragment.tekstu, gdzie wskazujesz do jan.kowalski w komórce obok, i masz składnie fragment.tekstu(komorka;5;8)]
5 w formule oznacza piąty znak – literę k, która jest pierwszą literą imienia. 8 oznacza liczbę znaków do wyciągnięcia. W tym wypadku jest to liczba liter w naziwsku Kowalski. Pewnie zastanawiasz się, czy zawsze musisz liczyć pozycje kropki i liczbę znaków w nazwisku? Co się stanie, gdy lista nazwisk będzie znacznie dłuższa? Do tego świetnie sprawdzi się kolejna funkcja tekstowa 🙂
Funkcja tekstowa ZNAJDŹ (ang. FIND)
składa się z trzech części. Jest wrażliwa na wielkość liter. W pierwszej części tej formuły wskazujemy szukany znak. Będzie to tekst, a więc musimy go wpisać w cudzysłowie. Pamiętaj, że wszystkie ciągi tekstowe, wpisane w formuły Excela, muszą być otoczone właśnie cudzysłowiem. Druga część formuły tekstowej ZNAJDŹ to miejsce na wskazanie komórki, w której szukamy pozycji znaku, wpisanego w pierwszą cześć formuły. W trzeciej pozycji wybieramy znak, od którego chcemy szukać pozycji. Ostatni argument jest opcjonalny, a więc nie musisz go podawać. Wtedy funkcja zacznie szukać od początku. Wybór miejsca startu przydaje się, gdy chcemy poznać pozycję na przykład drugiej kropki w komórce. Trzymając się przykładu z jan.kowalski, aby znaleźć pozycję kropki, trzeba użyć funkcji:
[wstaw to jako cytat]
=ZNAJDŹ(„.”;A1)
=FIND(„.”;A1)
[obraz powyższych funkcji w akcji]
Funkcja ZNAJDŹ świetnie sprawdza się, jako pomocnicza w zagnieżdżeniach z innymi funkcjami tekstowymi Excela, jak np. LEWY, PRAWY czy FRAGMENT.TEKSTU. Już pewnie widzisz jak dobrze sprawdzi się w przykładnie z poprzedniego akapitu:
=FRAGEMNT.TEKSTU(A1;ZNAJDŹ(„.”;A1);8)
=MID(A1;FIND(„.”;A1);8)
Zamiast wpisywać na stałe 5 jako pozycja kropki, możemy ją teraz dynamicznie znaleźć przy użyciu funkcji tekstowej FIND. Być może zapytasz jak policzyć liczbę znaków nazwiska, bo narazie widnieje tam na stałe 8? Można to zrobić na klika sposób, ale dla nas najprostszym będzie teraz wpisanie w funkcję tekstową FRAGMENT.TEKSTU liczbę na tyle dużą, aby liczba liter nazwiska wszystkich osób z listy się w niej mieściła np. 100. Formuła zwróci tylko tyle znaków, ile znajdzie 🙂
Funkcja tekstowa SZUKAJ.TEKST (ang. SEARCH)
działa niemal bliźniaczo do ZNAJDŹ. Te dwie funkcje tekstowe są bardzo do siebie podobne. Główną różnicą między tymi funkcjami jest fakt, że funkcja SZUKAJ.TEKST nierozróżnia wielkości liter, a więc gdy będziemy przy jej pomocy w imieniu Anna szukali „a” otrzymamy 1 jako informację zwrotną. Gdybyśmy to samo zrobili przy użyciu funkcji ZNAJDŹ, funkcja podałaby pozycję 4, bo dla niej wielkość litery ma znaczenie.
[jako cytaty]
=SZUKAJ.TEKST(„a”;A1)
=SEARCH(„a”;A1)
[obraz do powyższego]
Funkcja tekstowa DŁ (ang. LEN)
Ta funkcja pozwala zwrócić liczbę znaków w komórce. Pamiętaj, że funkcja traktuje wszystkie spacje i znaki zejścia do kolejnej linii w obrębie komórki, jako znak. Funkcja świetnie sprawdza się do weryfikacji, czy w komórce znajduje się odpowiednia liczba znaków. Numery, takie jak PESEL, NIP czy często ID faktur, zamówień, produktów bądź pracowników zawierają ściśle określoną liczbę znaków. Dzięki funkcji tekstowej DŁ łatwiej jest zweryfikować, czy są poprawne. Można to zrobić posiłkując się dodatkową kolumną albo używając formatowania warunkowego (o tym innym razem).
[jako cytat]
=DŁ(A1)
=LEN(A1)
[obraz listy 10 numerów pesel z 1 wadliwym, a obok funkcja dł, które je sprawdza)
Funkcja tekstowa USUŃ.ZBĘDNE.ODSTĘPY (ang. TRIM)
składa się z jednej cześci, w której wskazujemy komórkę, na której wartości chcemy pracować. Funkcja usuwa wszystkie spacje sprzed i zza słowa w komórce oraz pozostawia jedną spację pomiędzy słowami i cyframi, jeśli było ich tam więcej. Świetnie sprawdza się do czyszczenia danych, gdy system generuje w raporcie zbyt dużo spacji.
[cytat]
=USUŃ.ZBĘDNE.ODSTĘPY(a1)
=TRIM(a1)
[obraz – jakies dane, ktore maja za duzo spacji i obok korekta przy pomocy formuly. Ustaw się na komórce z formułą, żeby była ją widac]
Funkcja tekstowa PODSTAW (ang. SUBSTITUTE)
składa się aż z 4 części i podobnie jak funkcja ZNAJDŹ, uwzględnia wielkość znaków. Składowymi funkcji są kolejno:
komórka z wartościami, na których będziemy pracować,
tekst, który w tej komórce chcemy zamienić, oczywiście w cudzysłowie
tekst, na który ma zostać dokonana zmiana (też w cudzysłowie)
liczba wystąpień znaku do zmiany w tekście – to element opcjonalny, a więc jeśli chcemy zmienić wszystkie znaki w wybranym teśkcie, możemy go pominąć.
Załóżmy, że chcemy zamienić kropki na przecinki w liczbach:
[jako cytat]
=PODSTAW(A1;”.”;”,”)
=SUBSTITUTE(A1;”.”;”,”)
[obraz, gdzie mamy lcizebniki z ., a obok skonwerotwane na te z ,]
Funkcja tekstowa ZŁĄCZ.TEKSTY (ang. CONCATENATE)
pozwala łączyć ze sobą ciągi znaków z komórek. Świetnie sprawdzi się do łączenia imion i nazwisk:
[jako cytat]
=ZŁĄCZ.TEKSTY(A2;” „;B2)
=CONCATENATE(A2;” „;B2)
[obraz w kolumnie A imiona, w B nazwiska, w C formuła – z 5 przykładów]
Zwróć uwagę, że użyłem w formule również spacji z cudzysłowiach. Jest to tekst, a więc musi się w nich znajdować. Użyłem spacji, ponieważ to ona rodzieli imię od nazwiska.
Alternatywą dla funkcji tekstowej ZŁĄCZ.TEKSTY jest symbol &, zwany symbolem konkatenacji.
Powyższą funkcje możemy też zapisać drugim sposbem:
[cytat]
=A2&” „&B2
[obraz tej opcji funkcji]
Łączenie działa również na formuły. Jeśli potrzebujesz stworzyć w jednej komórce działające wyrażeni „Total: 1500”, możesz to osiągnąć, łącząc tekst z funkcją:
[cytat]
=”Total: „&SUMA(B2:B4)
=”Total: „&SUM(B2:B4)
[obraz powyższego]
Oczywiście to bardzo prosty przykład użycia funkcji tekstowych w połączenia z innymi. Możesz korzystać z takich połączeń przy użyciu dowolnych funkcji Excela.
Funkcja tekstowa POŁĄCZ.TEKSTY
została wprowadzona do Excela w wersji 2019 i jest ulepszoną wersją ZŁĄCZ.TEKSTY. Składa się z 3 części:
– ogranicznika, który będzie łącznikiem ciągów z komórek,
– informacji, co zrobić z pustymi komórkami, gdzie FAŁSZ (0) to uwzględnienie pustych komórek, a PRAWDA (1) to ich zignorowanie. Ignorowanie to pominięcie pustych komórek w tym, co łączy formuła.
– zakres komórek, z których dane mają ulec połączeniu.
Gdybyśmy użyli funkcji tekstowej POŁĄCZ.TEKSTY do tej samej operacji, co w przypadku funkcji ZŁĄCZ.TEKSTY wyszłoby:
[CYTAT]
=POŁĄCZ.TEKSTY(” „;0;A2:B2)
=TEXTJOIN(” „;0;A2:B2)
[OBRAZ DO POWYŻSZEGO]
W przypadku dwóch komórek nie jest to aż tak spektakluarne, ale pomyśl, że potrzebujesz połączyć ze sobą dane z 10 kolumn tak, aby były oddzielone średnikiem, bo tego wymaga od Ciebie system ERP. Aby zaimportować do niego dane, musisz je wkleić właśnie w tym formacie. Stworzenie funkcji tekstowej POŁĄCZ.TEKSTY załatwi sprawę w mniej niż minutę. Możesz taki sam rezultat osiągnąć przy użyciu & czy ZŁĄCZ.TEKSTY, ale trzeba się znacznie więcej naklikać (musisz zaznaczać po komórce i nie zapomnieć o żadnym cudzysłowie czy separatorze). Jak widzisz są funkcje tekstowe Excela, które mimo podobieństwa, działają jednak nieco inaczej
Funkcja tekstowa TEKST
składa się z dwóch części. W pierwszej z nich podajesz wartość albo zaznaczasz komórkę z wartością, a w drugiej wskazujesz format, w jakim ma zostać zwrócony rezultat. Pomyślisz: ale po co mam formatować dane formułą, skoro mogę to zrobić z paska narzędzi nad obszarem pracy Excela?
Jeśli połączysz ze sobą datę i tekst otrzymasz zdanie, w którym zobaczysz zamiast daty, duży liczebnik.
[obraz zdania w stylu „Zamówienie zostało wysałnie w dniu: „&Adres_komorki_z_data), a w rezultacie zdanie z liczbą 44 tys+]
Jak naprawić ten stan rzeczy? Na utworzonej wcześniej formułę załóż TEKST i użyj formatowania „DD.MM.RRRR” (w ang. wersji zamiast RRRR użyj YYYY). D reprezentuje dzień, M miesiąc, a R lub Y rok.
=TEKST(„Zamówienie zostało wysałne w dniu: „&A1;”DD.MM.RRRR”)
=TEXT(„Zamówienie zostało wysałne w dniu: „&A1;”DD.MM.YYYY”)
[obraz do powyższego]
Podsumowanie
To nie wszystkie funkcje tekstowe, jakie są dostępne w Excelu, ale to te, które każdy, pracujący w tym programie powienien znać. Zachęcam Cię do przećwiczenia ich i przejścia jednej po drugiej. Nauka formuł tekstowych pozwala lepiej zrozumieć zagnieżdżanie (umieszczanie jednej funkcji w drugiej), co bardzo przydaje się w codziennej pracy z Excelem.
Jeśli chcesz poznać więcej funkcji tekstowych (ale i wszystkich funkcji Excela) i sprawdzić wiedzę w akcji, zobacz ofertę tego kursu Excela (kliknij).
Powodzenia w Twojej karierze!
Zielone pozdrowienia,
Michał Kowalczyk
