Funkcje tekstowe
Rozdzielanie imienia i nazwiska w Excelu może wydać się łatwe, ale gdy weźmiemy pod uwagę ile jest sposobów zapisu tych danych, to może się to okazać trochę bardziej skomplikowane – choćby ze względu na możliwość pojawienia się drugiego imienia.
Excel ma wbudowanych wiele funkcji tekstowych, mniej lub bardziej przydatnych, ale nie ma funkcji rozdzielania imienia i nazwiska. Możemy za to zbudować formułę, która sobie z tym świetnie poradzi, a która będzie korzystała z kilku zaledwie funkcji tekstowych:
- PRAWY
- LEWY
- SZUKAJ.TEKST
- FRAGMENT TEKSTU
- DŁ
Oddzielamy imię
Zazwyczaj imię i nazwisko jest wpisane w jedną komórkę z oddzielającą je spacją. Przypuśćmy, że w komórkę A2 mamy wpisane Janek Kos. by wyłuskać z tej komórki samo imię musimy zacząć od lewej strony i zatrzymać się na spacji, która oddziela imię od nazwiska. Użyjemy więc funkcji LEWY(), która przyjmyje dwa argumenty:
- adres komórki zawierającej tekst
- ilość znaków
O ile z adresem komórki nie mamy problemu, bo dobrze wiemy, gdzie są nasze dane, o tyle musimy zdefiniować liczbę znaków, ponieważ różne imiona mają różną długość. Najlepiej przy pomocy funkcji SZUKAJ.TEKST(), która przyjmuje trzy argumenty:
- szukany znak
- adres komórki zawierającej tekst
- liczba początkowa, czyli miejsce od którego należy zacząć szukać
Składając te dwie funkcje otrzymamy następującą formułę:
=LEWY(A2;SZUKAJ.TEKST(" ";A2;1))
Drugie imię
Drugie imię, jeśli w ogóle występuje, jest trudniejsze do wyjęcia, ponieważ możemy się opierać tylko na pozycji dwóch spacji, które oddzielają je od pierwszego imienia i nazwiska. Musimy posłużyć się funkcją FRAGMENT.TEKSTU(), która przyjmuje argumenty:
- adres komórki zawierającej tekst
- liczba początkowa
- ilość znaków
Liczbą początkową będzie dla nas pierwsza spacja + 1, ponieważ chcemy zacząć od pierwszej litery drugiego imienia, a nie od spacji.
=SZUKAJ.TEKST(" ";A2;1)+1
Ilość znaków otrzymamy odejmując od pozycji drugiej spacji pozycję pierwszej spacji:
=SZUKAJ.TEKST(" ";A2;SZUKAJ.TEKST(" ";A2;1)+1)-SZUKAJ.TEKST(" ";A2;1)
Czyli nasza formuła, która zwróci nam drugie imię przyjmie następujący kształt:
=FRAGMENT.TEKSTU(A2;SZUKAJ.TEKST(" ";A2;1)+1;SZUKAJ.TEKST(" ";A2;SZUKAJ.TEKST(" ";A2;1)+1)-SZUKAJ.TEKST(" ";A2;1))
Jednak, wygeneruje ona błąd #ARG!, jeśli w nazwie nie będzie drugiego imienia (nie znajdzie drugiej spacji). Aby temu zapobiec użyjemy funkcje JEŻELI.BŁĄD(), która ma tylko dwa argumenty:
- wartość, czyli naszą formułę wyszukującą drugie imię
- wartość, jeśli wystąpi błąd, czyli wartość, którą ma zwrócić, jeśli pierwszy argument generuje błąd – dla nas będzie to po prostu nic, czyli „”
=JEŻELI.BŁĄD(FRAGMENT.TEKSTU(A2;SZUKAJ.TEKST(" ";A2;1)+1;SZUKAJ.TEKST(" ";A2;SZUKAJ.TEKST(" ";A2;1)+1)-SZUKAJ.TEKST(" ";A2;1));"")
Szukanie nazwiska
W tym przypadku zadanie mamy częściowo ułatwione, bo używaliśmy już wszystkich funkcji, których będziemy potrzebować. Ogólnie musimy użyć funkcji PRAWY(), gdzie jako ilość znaków podamy ilość znaków od końca do pierwszej lub drugiej spacji:
- do drugiej spacji (jeśli są dwa imiona):
=PRAWY(A2;DŁ(A2)-SZUKAJ.TEKST(" ";A2;SZUKAJ.TEKST(" ";A2;1)+1))
- do pierwszej spacji (jeśli jedt jedno imię):
=PRAWY(A2;DŁ(A2)-SZUKAJ.TEKST(" ";A2;1))
Tu również użyjemy funkcji JEŻELI.BŁĄD(), z tego samego powodu, co przy szukaniu drugiego imienia. Musimy jednak pamiętać o kolejności formuł: musimy zacząć od tej „mniej prawdopodobnej”, czyli od wersji z dwoma imionami. Wersja z jednym imieniem nigdy nie wygeneruje nam błędu, dlatego musi znaleźć się na drugim miejscu.
Cała formuła szukająca nazwiska przyjmie taki wygląd:
=JEŻELI.BŁĄD(PRAWY(A2;DŁ(A2)-SZUKAJ.TEKST(" ";A2;SZUKAJ.TEKST(" ";A2;1)+1));PRAWY(A2;DŁ(A2)-SZUKAJ.TEKST(" ";A2;1)))
Dla podsumowania:
Imię:
=LEWY(A2;SZUKAJ.TEKST(" ";A2;1))
Drugie imię:
=JEŻELI.BŁĄD(FRAGMENT.TEKSTU(A2;SZUKAJ.TEKST(" ";A2;1)+1;SZUKAJ.TEKST(" ";A2;SZUKAJ.TEKST(" ";A2;1)+1)-SZUKAJ.TEKST(" ";A2;1));"")
Nazwisko:
=JEŻELI.BŁĄD(PRAWY(A2;DŁ(A2)-SZUKAJ.TEKST(" ";A2;SZUKAJ.TEKST(" ";A2;1)+1));PRAWY(A2;DŁ(A2)-SZUKAJ.TEKST(" ";A2;1)))
Inne możliwości
Istnieje co prawda szybsza metoda na rozdzielenie łańcuchów tekstu, w którym znajdują się spacje lub inne separatory: Tekst jako kolumny.
Narzędzie to znajduje się na Wstążce w karcie Dane w zakładce Narzędzia danych. By go użyć:
- zaznaczamy kolumnę, w której znajdują się nasze dane
- klikamy w Tekst jako kolumny
- wybieramy „Rozdzielany”
- odhaczamy „Spacja” jako Ogranicznik
- klikamy zakończ
Rozwiązanie to ma jednak wady:
- w kolumnie z pierwotnymi danymi zostaje tylko pierwsze imię – nie mamy możliwości używania pełnej nazwy
- jeśli w nazwie było tylko jedno imię, to nazwisko trafia do drugiej kolumny, jesli były dwa, to do trzeciej, więc nasze dane nie będą spójne
słowa kluczowe: lista rozwijana excel 2013, data urodzenia z pesel excel, sprawdzenie poprawności numeru pesel, hasło xls, excel vlookup po polsku, adr pośr excel, odświeżanie tabeli przestawnej, vba kody, łamacz haseł excel, hasło excel 2007
