Oddziel imię i nazwisko w programie Excel (podziel nazwy za pomocą formuł)

Obejrzyj wideo - dzielenie imion w programie Excel (na imię, drugie imię i nazwisko)

Excel to niesamowite narzędzie, jeśli chodzi o krojenie i kostkowanie danych tekstowych.

Istnieje wiele przydatnych formuł i funkcji, których możesz użyć do pracy z danymi tekstowymi w programie Excel.

Jednym z bardzo częstych pytań dotyczących manipulowania danymi tekstowymi jest: „Jak oddzielić imię i nazwisko (lub imię, drugie i nazwisko) w programie Excel?“.

Istnieje kilka prostych sposobów dzielenia nazw w programie Excel. Wybrana metoda będzie zależeć od struktury danych i od tego, czy chcesz, aby wynik był statyczny czy dynamiczny.

Zacznijmy więc i zobaczmy różne sposoby dzielenia nazw w programie Excel.

Podziel nazwy za pomocą tekstu na kolumny

Funkcja tekstu do kolumn w programie Excel umożliwia szybkie dzielenie wartości tekstowych na oddzielne komórki w wierszu.

Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej, i chcesz oddzielić imię i nazwisko oraz umieścić je w osobnych komórkach.

Poniżej znajdują się kroki, aby oddzielić imię i nazwisko za pomocą tekstu na kolumny:

1. Wybierz wszystkie nazwiska w kolumnie (w tym przykładzie A2:A10)
2. Kliknij zakładkę „Dane”
3. W grupie „Narzędzia danych” kliknij opcję „Tekst do kolumn”.
4. Wprowadź następujące zmiany w Kreatorze konwersji tekstu na kolumnę:
   1. Krok 1 z 3: Wybierz Rozdzielone (pozwala to na użycie spacji jako separatora) i kliknij Dalej
   2. Krok 2 z 3: Wybierz opcję Spacja i kliknij Dalej
   3. Krok 3 z 3: Ustaw B2 jako komórkę docelową (w przeciwnym razie zastąpi istniejące dane)
5. Kliknij Zakończ

Powyższe kroki natychmiast podzieliłyby imiona na imię i nazwisko (z imionami w kolumnie B i nazwiskiem w kolumnie C).

Uwaga: Jeśli w komórkach znajdują się już jakieś dane (te, w których oczekiwany jest wynik tekstu do kolumn), program Excel wyświetli ostrzeżenie informujące, że w komórkach są już jakieś dane. Możesz wybrać zastąpienie danych lub anulowanie tekstu w kolumnach i ręczne usunięcie go w pierwszej kolejności.

Po zakończeniu możesz usunąć pełne dane imienia, jeśli chcesz.

Kilka rzeczy, które należy wiedzieć podczas używania tekstu do kolumn do oddzielania imienia i nazwiska w programie Excel:

1. Wynik tego jest statyczny. Oznacza to, że jeśli masz nowe dane lub oryginalne dane zawierają pewne zmiany, musisz zrobić to od nowa, aby podzielić nazwy.
2. Jeśli chcesz tylko imię lub tylko nazwisko, możesz pominąć niepotrzebne kolumny w kroku 3 okna dialogowego „Kreator tekstu do kolumny”. Aby to zrobić, wybierz w podglądzie kolumnę, którą chcesz pominąć, a następnie wybierz opcję „Nie importuj kolumny (pomiń)”.
3. Jeśli nie określisz komórki docelowej, tekst do kolumny zastąpi bieżącą kolumnę

Opcja Tekst do kolumn najlepiej nadaje się, gdy masz spójne dane (na przykład wszystkie imiona mają tylko imię i nazwisko lub wszystkie imiona mają imię, drugie imię i nazwisko).

W tym przykładzie pokazałem, jak oddzielić nazwy, które mają spację jako ogranicznik. Jeśli ogranicznik jest przecinkiem lub kombinacją przecinka i spacji, nadal możesz wykonać te same czynności. W takim przypadku możesz określić ogranicznik w kroku 2 z 3 kreatora. Istnieje już opcja użycia przecinka jako ogranicznika lub możesz wybrać opcję „Inne” i określić również niestandardowy ogranicznik.

Chociaż tekst na kolumny to szybki i wydajny sposób dzielenia nazw, jest odpowiedni tylko wtedy, gdy chcesz, aby dane wyjściowe były wynikiem statycznym. Jeśli masz zestaw danych, który może się rozszerzać lub zmieniać, lepiej użyć formuł do oddzielenia nazw.

Oddziel imię, drugie imię i nazwisko za pomocą formuł

Formuły umożliwiają krojenie i krojenie danych tekstowych oraz wyodrębnianie tego, co chcesz.

W tej sekcji podzielę się różnymi formułami, których możesz użyć do oddzielenia danych nazw (na podstawie struktury danych).

Poniżej znajdują się trzy formuły, których możesz użyć do oddzielenia imienia, drugiego imienia i nazwiska (wyjaśnione szczegółowo w kolejnych sekcjach).

Formuła do uzyskania imienia:

=LEWO(A2,SZUKAJ("",A2)-1)

Wzór na drugie imię:

=MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",SUBSTITUTE(A2" ","@",1))-SEARCH(" ",A2))

Wzór na nazwisko:

=PRAWY(A15,DŁ(A15)-SZUKAJ("@",ZAMIANA(A15;"","@",DŁ(A15)-DŁ(ZAMIANA(A15;"","")))))

Zdobądź imię

Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcesz szybko oddzielić imię w jednej komórce i nazwisko w jednej komórce.

Poniższa formuła da Ci imię:

=LEWO(A2,SZUKAJ("",A2)-1)

Powyższa formuła wykorzystuje funkcję SEARCH, aby uzyskać pozycję znaku spacji między imieniem i nazwiskiem. Funkcja LEWO używa następnie numeru pozycji spacji, aby wyodrębnić cały tekst przed nią.

Jest to dość proste zastosowanie wyodrębniania części wartości tekstowej. Ponieważ wszystko, co musimy zrobić, to zidentyfikować pozycję pierwszego znaku spacji, nie ma znaczenia, czy imię ma drugie imię, czy nie. Powyższa formuła będzie działać dobrze.

Teraz przejdźmy trochę bardziej zaawansowani z każdym przykładem.

Zdobądź nazwisko

Załóżmy, że masz ten sam zbiór danych i tym razem musisz uzyskać nazwisko.

Poniższa formuła wyodrębni nazwisko z powyższego zbioru danych:

=PRAWO(A2,DŁ(A2)-SZUKAJ("",A2))

Znowu dość proste.

Tym razem najpierw znajdujemy pozycję znaku spacji, która jest następnie używana do określenia liczby znaków, które pozostały po spacji (która byłaby nazwiskiem).

Osiąga się to poprzez odjęcie wartości pozycji znaku spacji od całkowitej liczby znaków w nazwie.

Numer ten jest następnie używany w funkcji PRAWO, aby pobrać wszystkie te znaki z prawej strony nazwy.

Chociaż ta formuła działa świetnie, gdy jest tylko imię i nazwisko, nie zadziała, jeśli masz również drugie imię. Dzieje się tak, ponieważ uwzględniliśmy tylko jeden znak spacji (między imieniem i nazwiskiem). Posiadanie drugiego imienia dodaje więcej znaków spacji do nazwy.

Aby pobrać nazwisko, gdy masz również drugie imię, użyj poniższej formuły:

=PRAWY(A15,DŁ(A15)-SZUKAJ("@",ZAMIANA(A15;"","@",DŁ(A15)-DŁ(ZAMIANA(A15;"","")))))

Teraz zaczęło to być trochę skomplikowane… prawda?

Pozwól, że wyjaśnię, jak to działa.

Powyższa formuła najpierw znajduje całkowitą liczbę znaków spacji w nazwie. Odbywa się to poprzez pobranie długości nazwy ze znakiem spacji i bez, a następnie odjęcie tej bez spacji od tej ze spacją. Daje to całkowitą liczbę znaków spacji.

Funkcja SUBSTITUTE jest następnie używana do zamiany ostatniego znaku spacji na symbol „@” (można użyć dowolnego symbolu – coś, co raczej nie występuje jako część nazwy).

Gdy symbol @ zostanie zastąpiony w miejsce ostatniego znaku spacji, możesz łatwo znaleźć pozycję tego symbolu @. Odbywa się to za pomocą funkcji SZUKAJ.

Teraz wszystko, co musisz zrobić, to wyodrębnić wszystkie znaki na prawo od tego symbolu @. Odbywa się to za pomocą funkcji PRAWO.

Zdobądź drugie imię

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz wyodrębnić drugie imię.

Zrobi to następująca formuła:

=MID(A2,SEARCH(" ",A2)+1,SEARCH("",SUBSTITUTE(A2,"","@",1))-SEARCH(" ",A2))

Powyższa formuła wykorzystuje funkcję MID, która pozwala określić pozycję początkową i liczbę znaków do wyodrębnienia z tej pozycji.

Pozycję początkową można łatwo znaleźć za pomocą funkcji SZUKAJ.

Najtrudniejsze jest znalezienie liczby znaków do wyodrębnienia po tej pozycji początkowej. Aby to uzyskać, musisz określić, ile znaków jest tam od pozycji początkowej do ostatniego znaku spacji.

Można to zrobić za pomocą funkcji SUBSTITUTE i zastąpić ostatni znak spacji symbolem „@”. Gdy to zrobisz, możesz łatwo użyć funkcji SEARCH, aby znaleźć pozycję tego ostatniego znaku spacji.

Teraz, gdy masz już pozycję początkową i pozycję ostatniego miejsca, możesz łatwo pobrać drugie imię za pomocą funkcji MID.

Jedną z zalet używania formuły do ​​oddzielania nazw jest to, że wynik jest dynamiczny. Tak więc na wypadek, gdyby Twój zestaw danych się rozszerzył i dodano do niego więcej nazw lub jeśli niektóre nazwy zmienią się w oryginalnym zestawie danych, nie musisz się martwić o dane wynikowe.

Oddziel nazwy za pomocą funkcji Znajdź i zamień

Uwielbiam elastyczność, jaką daje „Znajdź i zamień” - ponieważ możesz w nim używać symboli wieloznacznych.

Pozwólcie, że najpierw wyjaśnię, co to jest znak wieloznaczny.

Symbol wieloznaczny to coś, czego można użyć zamiast dowolnego tekstu. Na przykład możesz użyć symbolu gwiazdki (*), który będzie reprezentował dowolną liczbę znaków w programie Excel. Aby dać ci przykład, jeśli chcę znaleźć wszystkie nazwy zaczynające się od alfabetu A, mogę użyć A* w funkcji Znajdź i zamień. Spowoduje to znalezienie i zaznaczenie wszystkich komórek, których nazwa zaczyna się od A.

Jeśli nadal nie masz jasności, nie martw się. Czytaj dalej, a kilka następnych przykładów wyjaśni, jakie są symbole wieloznaczne i jak ich używać do szybkiego oddzielania nazw (lub dowolnych wartości tekstowych w programie Excel).

We wszystkich przykładach omówionych poniżej upewnij się, że utworzyłeś kopię zapasową zestawu danych. Znajdź i zamień zmienia dane, na których jest używany. Najlepiej najpierw skopiować i wkleić dane, a następnie użyć funkcji Znajdź i zamień na skopiowanym zestawie danych.

Zdobądź imię

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz uzyskać tylko imię.

Poniżej znajdują się kroki, aby to zrobić:

1. Skopiuj dane nazwy w kolumnie A i wklej je w kolumnie B.
2. Po wybraniu danych w kolumnie B kliknij kartę Strona główna
3. W grupie Edytowanie kliknij Znajdź i wybierz.
4. Kliknij Zamień. Spowoduje to otwarcie okna dialogowego „Znajdź i zamień”.
5. W oknie dialogowym „Znajdź i zamień” wprowadź następujące informacje
   1. Znajdź co: * (spacja, po której następuje symbol gwiazdki)
   2. Zamień na: zostaw to puste
6. Kliknij Zamień wszystko.

Powyższe kroki dadzą ci imię i usuną wszystko po imieniu.

Działa to nawet wtedy, gdy masz imiona, które mają drugie imię.

Pro Porada: Skrót klawiaturowy do otwierania okna dialogowego Znajdź i zamień to Kontrola + H (przytrzymaj klawisz sterujący, a następnie naciśnij klawisz H).

Zdobądź nazwisko

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz uzyskać tylko nazwisko.

Poniżej znajdują się kroki, aby to zrobić:

1. Skopiuj dane nazwy w kolumnie A i wklej je w kolumnie B.
2. Po wybraniu danych w kolumnie B kliknij kartę Strona główna
3. W grupie Edytowanie kliknij Znajdź i wybierz.
4. Kliknij Zamień. Spowoduje to otwarcie okna dialogowego „Znajdź i zamień”.
5. W oknie dialogowym „Znajdź i zamień” wprowadź następujące informacje
   1. Znajdź co: * (symbol gwiazdki, po którym następuje spacja)
   2. Zamień na: zostaw to puste
6. Kliknij Zamień wszystko.

Powyższe kroki dadzą ci nazwisko i usuną wszystko przed imieniem.

Działa to nawet wtedy, gdy masz imiona, które mają drugie imię.

Usuń drugie imię

Jeśli chcesz pozbyć się tylko drugiego imienia i mieć tylko imię i nazwisko, możesz to zrobić za pomocą funkcji Znajdź i zamień.

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz usunąć z nich drugie imię.

Poniżej znajdują się kroki, aby to zrobić:

1. Skopiuj dane nazwy w kolumnie A i wklej je w kolumnie B.
2. Po wybraniu danych w kolumnie B kliknij kartę Strona główna
3. W grupie Edytowanie kliknij Znajdź i wybierz.
4. Kliknij Zamień. Spowoduje to otwarcie okna dialogowego „Znajdź i zamień”.
5. W oknie dialogowym „Znajdź i zamień” wprowadź następujące informacje
   1. Znajdź co: * (spacja, po której następuje symbol gwiazdki, a następnie znak spacji)
   2. Zastąp: (wstaw tutaj spację)
6. Kliknij Zamień wszystko.

Powyższe kroki usunęłyby drugie imię z pełnego imienia i nazwiska. W przypadku, gdy niektóre imiona nie mają drugiego imienia, nie zostaną zmienione.

Oddziel nazwy przy użyciu wypełniania Flash

Wypełnianie Flash zostało wprowadzone w programie Excel 2013 i bardzo ułatwia modyfikowanie lub czyszczenie zestawu danych tekstowych.

A jeśli chodzi o oddzielanie danych nazw, to jest to w alei Flash Fill.

Najważniejszą rzeczą, jaką należy wiedzieć podczas korzystania z funkcji Flash Fill, jest to, że potrzebny jest wzór, który można zidentyfikować za pomocą funkcji Flash Fill. Gdy zidentyfikuje wzorzec, z łatwością pomoże ci podzielić nazwy w programie Excel (więcej jasności uzyskasz, gdy przejdziesz przez kilka przykładów poniżej).

Uzyskaj imię lub nazwisko z pełnego imienia i nazwiska

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz uzyskać tylko imię.

1. W sąsiedniej komórce wpisz ręcznie imię z pełnego imienia i nazwiska. W tym przykładzie wpisałbym Rick.
2. W drugiej komórce wpisz ręcznie imię z nazwy sąsiedniej komórki. Podczas pisania zobaczysz, że funkcja Flash Fill automatycznie wyświetla listę imion (w kolorze szarym).
3. Gdy zobaczysz imiona w kolorze szarym, szybko przejrzyj je, aby upewnić się, że wyświetla właściwe imiona. Jeśli są prawidłowe, naciśnij klawisz Enter, a Flash Fill automatycznie wypełni pozostałe komórki imieniem.

Flash Fill wymaga, abyś nadał mu wzór, którym może podążać, podając zmodyfikowane dane. W naszym przykładzie po wpisaniu imienia w pierwszej komórce funkcja Flash Fill nie może rozpoznać wzorca.

Ale gdy tylko zaczniesz wpisywać imię w drugiej komórce, funkcja Flash Fill zrozumie wzorzec i wyświetli pewną sugestię. Jeśli sugestia jest poprawna, po prostu naciśnij klawisz Enter.

A jeśli nie jest poprawny, możesz spróbować wprowadzić ręcznie w kilku kolejnych komórkach i sprawdzić, czy Flash Fill jest w stanie rozpoznać wzór, czy nie.

Czasami możesz nie widzieć szarego wzoru (jak pokazano w kroku 2 powyżej). W takim przypadku wykonaj poniższe czynności, aby uzyskać wynik Flash Fill:

1. Wpisz tekst ręcznie w dwóch komórkach.
2. Wybierz obie te komórki
3. Umieść kursor w prawej dolnej części zaznaczenia. Zauważysz, że kursor zmienia się w ikonę plusa
4. Kliknij go dwukrotnie (lewy klawisz myszy). To wypełni wszystkie komórki. W tym momencie wyniki są prawdopodobnie nieprawidłowe, a nie takie, jakich się spodziewałeś.
5. W prawym dolnym rogu wynikowych danych zobaczysz małą ikonę automatycznego wypełniania. Kliknij tę ikonę automatycznego wypełniania
6. Kliknij Wypełnienie Flash

Powyższe kroki dadzą wynik z Flash Fill (na podstawie wydedukowanego przez niego wzorca).

Możesz także użyć funkcji Flash Fill, aby uzyskać nazwisko lub drugie imię. W pierwszych dwóch komórkach wpisz nazwisko (lub drugie imię), a flash fill będzie w stanie zrozumieć wzór

Zmień nazwę za pomocą Flash Fill

Flash Fill to sprytne narzędzie, które potrafi również rozszyfrować nieco skomplikowany wzór

Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej, i chcesz zmienić kolejność nazwy z Rick Novak na Novak, Rick (gdzie najpierw pojawia się nazwisko, a następnie przecinek, a następnie imię).

Poniżej znajdują się kroki, aby to zrobić:

1. W sąsiedniej komórce wpisz ręcznie Novak, Rick
2. W drugiej komórce ręcznie wpisz Connor, Susan. Podczas pisania zobaczysz, że Flash Fill pokazuje listę nazw w tym samym formacie (na szaro).
3. Gdy zobaczysz imiona w kolorze szarym, szybko przejrzyj je, aby upewnić się, że wyświetla właściwe nazwy. Jeśli są prawidłowe, naciśnij klawisz Enter, a funkcja Flash Fill automatycznie wypełni pozostałe komórki nazwami w tym samym formacie.

Usuń drugie imię (lub po prostu zdobądź drugie imię)

Możesz również użyć funkcji Flash Fill, aby pozbyć się drugiego imienia lub uzyskać tylko drugie imię.

Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej, i chcesz uzyskać tylko imię i nazwisko, a nie drugie imię.

Poniżej znajdują się kroki, aby to zrobić:

1. W sąsiedniej komórce ręcznie wpisz Rick Novak
2. W drugiej komórce ręcznie wpisz Susan Connor. Podczas pisania zobaczysz, że Flash Fill pokazuje listę nazw w tym samym formacie (na szaro).
3. Gdy zobaczysz imiona w kolorze szarym, szybko przejrzyj je, aby upewnić się, że wyświetla właściwe imiona. Jeśli są prawidłowe, naciśnij klawisz Enter, a funkcja Flash Fill automatycznie wypełni pozostałe komórki nazwami bez drugiego imienia.

Podobnie, jeśli chcesz uzyskać tylko drugie imię, wpisz drugie imię w pierwszych dwóch komórkach i użyj funkcji Flash Fill, aby uzyskać drugie imię ze wszystkich pozostałych imion.

W przykładach pokazanych w tym samouczku używane są nazwy podczas manipulowania danymi tekstowymi. Możesz użyć tych samych pojęć, aby pracować również z innymi formatami danych (takimi jak adresy, nazwy produktów itp.)