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 wielką literą). 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 na razie skup się na samej konwersji. 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ą domyślnie wyrównane do lewej. Może to stwarzać problem, gdy użylibyśmy tych danych do wyszukiwania czy w funkcjach logicznych.
Jak zatem skonwertować takie dane na liczby?
Przemnóż lub podziel je przy użyciu jedynki. Możesz też dodać dwa minusy przed formułą. 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.
Najważniejsze funkcje tekstowe Excela to znaczy jakie?
Funkcje tekstowe: LEWY (ang. LEFT)
składa się z 2 części.
W pierwszej części formuły wybieramy komórkę, z której chcemy wyciągnąć 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ąć pierwsze dwa znaki od lewej, użyjemy następującej składni:
=LEWY(A1;2)
=LEFT(A1;2)
Funkcje tekstowe: PRAWY (ang. RIGHT)
Działa analogicznie do funkcji LEWY.
Różnica polega na tym, że znaki są wyciągane od prawej strony komórki.
Funkcje tekstowe: 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órego znaku ma zostać wyciągnięty ów fragment. Część trzecia pozwala nam wskazać, ile znaków chcemy wyciągnąć.
Przykładem jest wyciągnięcie nazwiska z ciągu jan.kowalski:
=FRAGEMNT.TEKSTU(B2;5;8)
=MID(B2;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 nazwisku 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 🙂
Funkcje tekstowe: 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łowem. Druga część formuły tekstowej ZNAJDŹ to miejsce na wskazanie komórki, w której szukamy pozycji znaku, wpisanego w pierwszą część 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:
=ZNAJDŹ(„.”;B2)
=FIND(„.”;B2)
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Ź(„.”;B2);8)
=MID(A1;FIND(„.”;B2);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 na razie widnieje tam na stałe 8? Można to zrobić na kilka sposobów, 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 🙂
Funkcje tekstowe: 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 nie rozróż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.
=SZUKAJ.TEKST(„a”;B2)
=SEARCH(„a”;B2)
Funkcje tekstowe: 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).
=DŁ(A1)
=LEN(A1)
Funkcje tekstowe: USUŃ.ZBĘDNE.ODSTĘPY (ang. TRIM)
Składa się z jednej częś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.
=USUŃ.ZBĘDNE.ODSTĘPY(A2)
=TRIM(A2)
Funkcje tekstowe: 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 tekście, możemy go pominąć.
Załóżmy, że chcemy zamienić kropki na przecinki w liczbach:
=PODSTAW(A1;”.”;”,”)
=SUBSTITUTE(A1;”.”;”,”)
Funkcje tekstowe: ZŁĄCZ.TEKSTY (ang. CONCATENATE)
Pozwala łączyć ze sobą ciągi znaków z komórek. Świetnie sprawdzi się do łączenia imion i nazwisk:
=ZŁĄCZ.TEKSTY(A2;” „;B2)
=CONCATENATE(A2;” „;B2)
Zwróć uwagę, że użyłem w formule również spacji z cudzysłowem. Jest to tekst, a więc musi się w nich znajdować. Użyłem spacji, ponieważ to ona rozdzieli imię od nazwiska.
Alternatywą dla funkcji tekstowej ZŁĄCZ.TEKSTY jest symbol &, zwany symbolem konkatenacji.
Powyższą funkcję możemy też zapisać drugim sposobem:
=A2&” „&B2
Łą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ą:
=”Total: „&SUMA(B2:B4)
=”Total: „&SUM(B2:B4)
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 (ang. TEXTJOIN)
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:
=POŁĄCZ.TEKSTY(” „;0;A2:B2)
=TEXTJOIN(” „;0;A2:B2)
W przypadku dwóch komórek nie jest to aż tak spektakularne, 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 (ang. TEXT)
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.
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. Tutaj znajdziesz pełny opis formatowania data w Microsoft Excel.
=”Zamówienie zostało wysałne w dniu: „&TEKST(A1;”DD.MM.RRRR”)
=”Zamówienie zostało wysałne w dniu: „&TEXT(A1;”DD.MM.YYYY”)
Podsumowanie
To nie wszystkie funkcje tekstowe, jakie są dostępne w Excelu, ale to te, które każdy pracujący w tym programie powinien 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
