Rozdzielanie imion i nazwisk na dwie kolumny – część pierwsza

Rozdzielanie imion i nazwisk na dwie kolumny to częsta operacja wykonywana gdy w danych źródłowych zostały podane razem. Z tego wpisu dowiesz się jak przy pomocy funkcji tekstowych LEWY, PRAWY, DŁ oraz ZNAJDŹ rozdzielić na dwie kolumny imiona i nazwiska (lub inne wartości) wpisane w jedną kolumnę.

Załóżmy, że pracujemy z tabelą w Excelu, która zawiera listę osób. Ich imiona i nazwiska zostały wpisane w tę samą kolumnę, a potrzebujemy je rozdzielić na osobne kolumny. Rozdzielanie imion i nazwisk na dwie kolumny w sposób ręczny to żmudna praca, w tym artykule prezentuję jak szybko i dokładnie zrobić rozdzielanie imion i nazwisk przy pomocy funkcji wbudowanych w Excelu.

Najłatwiejszm i najszybszym rozwiązaniem jest skorzystanie z narzędzia TEKST JAKO KOLUMNY. Ale niestety nie zawsze możemy użyć tego rozwiązania - przyjmijmy, że tak jest w tym przypadku , bo wiele osób na naszej liście ma po dwa imiona i wieloczłonowe nazwiska. Dlatego będziemy potrzebowali rozdzielić na osobne kolumny wartości rozdzielone pierwszą spacją korzystając z funkcji tekstowych.

Żeby to zrobić będzie trzeba użyć formuły z zagnieżdżonymi funkcjami LEWY, PRAWY, ZNAJDŹ. Aby przykład był zrozumiały warto poznać zasadę działania i składnię poszczególnych funkcji.

LEWY

Funkcja lewy zwraca ciąg znaków długości określonej w formule rozpoczynając od pierwszego znaku w ciągu licząc od lewej strony.

składnia funkcji lewy

=LEWY([odwołanie do komórki]; [liczba znaków])

na przykład

=LEWY(A1;5)

taka funkcja pobierze z komórki A1 pięć pierwszych znaków licząc od lewej strony ciągu znaków w komórce A1.

PRAWY

Funkcja prawy zwraca ciąg znaków długości określonej w formule rozpoczynając od pierwszego znaku w ciągu licząc od prawej strony (czyli ostatniego znaku ciągu).

składnia funkcji prawy

=PRAWY([odwołanie do komórki]; [liczba znaków])

na przykład

=PRAWY(A1;5)

taka funkcja pobierze z komórki A1 pięć ostatnich znaków licząc od lewej strony ciągu znaków w komórce A1.

Funkcja DŁ (długość) zwraca liczbę określającą długość ciągu znaków stanowiącego wartość przechowywaną w komórce do której funkcja DŁ. Innymi słowy dzięki niej możemy na przykład sprawdzić długość wyrazu lub sprawdzić czy nr konta ma 26 znaków.

składnia funkcji DŁ

składnia funkcji DŁ jest bardzo prosta

=DŁ([odwołanie do komórki])

na przykład

=DŁ(A5)

taka funkcja zwraca wartość określającą długość ciągu znaków przechowywanego w komórce A5.

Ważne jest aby pamiętać, że funkcja uwzględnia też spacje.

ZNAJDŹ

Funkcja znajdź podaje pozycję, na której znajduje się określony tekst we wskazanym ciągu znaków licząc od wskazanego. W naszym przykładzie posłużymy się nią, aby wyszukać spację rozdzielającą imię (imiona) i nazwisko podać liczbę określającą jej pozycję w ciągu znaków, która będzie wykorzystana w innej funkcjach.

składnia funkcji znajdź

=ZNAJDŹ([wyszukiwany tekst];[odwołanie do zakresu];[liczba początkowa])

na przykład

=ZNAJDŹ(” „;A5;1)

taka funkcja poda pozycję od lewej, na której znajduje się spacja w ciągu znaków przechowywanym w komórce A5 uwzględniając przy tym pierwszy znak

Pobranie imienia z komórki A5

=LEWY(A5;(DŁ(A5)-ZNAJDŹ(” „;A5;(ZNAJDŹ(” „;A5;1)))))

Objaśnienie: aby pobrać z komórki A5 imię musimy użyć funkcji zagnieżdżonych. Najbardziej zagnieżdżona jest funkcja znajdż wyróżniona na czerwono. Wyszukuje  ona pierwszą spację w ciągu znaków z komórki A5 i zwraca jej pozycję. Wynik działania tej formuły stanowi argument funkcji znajdź oznaczonej na fiolotewo i wskazuje od którego znaku ta funkcja powinna zacząć wyszukiwać spacji. Wynik „fiolotowej” części formuły określa pozycję drugiej spacji w ciągu znaków, odejmujemy go od wyniku funkcji DŁ, która oblicza długość całego ciągu. Wynik tego obliczenia określa ile znaków jest przed drugą spacją i określa, drugi argument funkcji lewy wskazując ile znaków od lewej ma pobrać nasza formuła. W ten sposób otrzymujemy gotową formułę – „inteligentą ” funkcję lewy, która sama dopasuje liczbę znaków do pobrania.

Warto pamiętać o ważnych założeniach:
1. Imiona i nazwiska rodzielone są pojedyńczymi spacjami
2. Nie ma nadmiarowych spacji na początku tekstu
3. Osoby z listy mają maksymalnie dwa imiona
4. Osoby z listy mają pojedyńcze nazwiska, a nazwiska wieloczłonowe nie są rozdzielone spacjami.

Jeśli nie jesteśmy pewni, że nie występują na liście osób niechciane spacje lub formuła podana powyżej zwraca błędne wyniki to możemy się posłużyć kolejną funkcją tekstową: USUŃ.ZBĘDNE.OSTĘPY. Funkcja ta oczyszcza tekst ze zbędnych spacji usuwając wszystkie spacje na początku i na końcu ciągu oraz usuwa nadmiarowe spacje wewnątrz ciągu (w efekcie otrzymujemy ciąg rozdzielony pojedyńczymi spacjami, bez spacji na jego początku i na  jego końcu).

Moglibyśmy tej funkcji użyć w powyższej formule, należałoby ją wówczas zagnieździć w części wyróżnionej na czerwono. Żeby jednak nie komplikować sobie życia wielokrotnymi nawiasami możemy po prostu najpierw przygotować dane do obróbki poprzez usunięcie zbędnych odstępów w dodatkowej kolumnie obok właściwych danych i dopiero z tej dodatkowej kolumny pobierać dane do właściwej formuły. Względnie można też osiągnąć cel kopiując wartości (wklej specjalnie) zwrócone przez funkcję USUŃ.ZBĘDNE ODSTĘPY do właściwej kolumny z imieniem i nazwiskiem, skasować kolumnę pomocniczą.

składnia funkcji USUŃ.ZBĘDNE.ODSTĘPY

funkcja USUŃ.ZBĘDNE.ODSTĘPY jest funkcją jednoargumentową, który stanowi odwołanie do tekstu, który ma być oczyszczony z nadmiarowych spacji.

 =USUŃ.ZBĘDNE.ODSTĘPY([odwołanie do zakresu źródłowego])

na przykład

=USUŃ.ZBĘDNE.ODSTĘPY(A5) pobiera tekst z komórki A5.

Dziękuję z uwagę, w następnej części kontynacja odcinka poświęcona pobraniu nazwisk z wspomnianej tutaj listy nazwisk.

Share and Enjoy

  • Facebook
  • LinkedIn
  • Add to favorites
  • Email
  • RSS