Mogą wystąpić sytuacje, w których trzeba będzie podzielić komórki w programie Excel. Może to mieć miejsce, gdy pobierasz dane z bazy danych, kopiujesz je z Internetu lub otrzymujesz od kolegi.

Prosty przykład, w którym musisz podzielić komórki w programie Excel, to pełne nazwy i chcesz podzielić je na imię i nazwisko.

Albo otrzymasz adres ”i chcesz podzielić adres, aby móc oddzielnie analizować miasta lub kod PIN.

Jak dzielić komórki w programie Excel

Z tego samouczka dowiesz się, jak dzielić komórek w programie Excel przy użyciu następujących technik:

• Korzystanie z funkcji Tekst na kolumnę.
• Korzystanie z funkcji tekstowych programu Excel.
• Korzystanie z wypełniania flash (dostępne w 2013 i 2016).

Zaczynamy!

Podziel komórki w Excelu używając tekstu na kolumny

Poniżej mam listę nazwisk niektórych moich ulubionych postaci fikcyjnych i chcę podzielić te imiona na osobne komórki .:

Oto kroki, aby wyświetlić t te imiona na imię i nazwisko:

• Wybierz komórki, w których masz tekst, który chcesz podzielić (w tym przypadku A2: A7).
• Kliknij kartę Dane
• W grupie „Narzędzia danych” kliknij opcję „Tekst do kolumn”.
• W Kreator konwersji tekstu na kolumny:
  • Krok 1 z 3 Kreatora tekstu na kolumny: Upewnij się, że wybrana jest opcja Rozdzielany (jest to opcja domyślna). Umożliwiłoby to oddzielenie imienia i nazwiska na podstawie określonego separatora (w tym przypadku spacji).
  • Kliknij Dalej.
  • Krok 2 z 3 Kreator zamiany tekstu na kolumny: Wybierz spację jako separator i odznacz wszystko inne. Możesz zobaczyć, jak wyglądałby wynik w sekcji Podgląd danych w oknie dialogowym.
  • Kliknij Dalej.
  • Krok 3 z 3 Kreator tekstu na kolumny: W tym kroku możesz określić format danych i miejsce, w którym chcesz uzyskać wynik. Zachowam format danych jako ogólny, ponieważ mam dane tekstowe do podzielenia. Domyślnym miejscem docelowym jest A2 i jeśli będziesz kontynuować, zastąpi on oryginalny zestaw danych. Jeśli chcesz zachować oryginalne dane w stanie nienaruszonym, wybierz inną komórkę jako miejsce docelowe. W tym przypadku wybrana jest opcja B2.
  • Kliknij Zakończ.

Spowoduje to natychmiastowe podzielenie tekstu komórki na dwie różne kolumny.

Uwaga:

• Funkcja Tekst na kolumnę dzieli zawartość komórek na podstawie separatora. Chociaż działa to dobrze, jeśli chcesz oddzielić imię i nazwisko, w przypadku imienia, drugiego imienia i nazwiska podzieli to na trzy części.
• Wynik uzyskany dzięki użyciu Funkcja Tekst na kolumnę jest statyczna. Oznacza to, że jeśli nastąpią jakiekolwiek zmiany w oryginalnych danych, będziesz musiał powtórzyć proces, aby uzyskać zaktualizowane wyniki.

Podziel komórki w programie Excel za pomocą funkcji tekstowych

Funkcje Excel Text są świetne, gdy chcesz pokroić i pokroić ciągi tekstowe.

Chociaż funkcja Text to Column daje wynik statyczny, wynik, który uzyskujesz dzięki użyciu funkcji, jest dynamiczny i automatycznie aktualizuje się, gdy zmienić oryginalne dane.

Dzielenie nazw, które mają imię i nazwisko

Załóżmy, że masz te same dane, co pokazano poniżej:

Wyodrębnianie imienia

Aby uzyskać imię z tej listy, użyj następującego wzoru:

=LEFT(A2,SEARCH(" ",A2)-1)

Ta formuła wykryłaby pierwszy znak spacji, a następnie zwróciłaby cały tekst przed tym znakiem:

Ta formuła wykorzystuje funkcję SZUKAJ, aby uzyskać pozycję znaku spacji. W przypadku Bruce’a Wayne’a spacja znajduje się na 6 pozycji. Następnie wyodrębnia wszystkie znaki po lewej stronie za pomocą funkcji LEFT.

Wyodrębnianie nazwiska

Podobnie, aby uzyskać nazwisko, użyj następującej formuły:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

Ta formuła wykorzystuje funkcję wyszukiwania do znalezienia pozycji spacji za pomocą funkcja SZUKAJ. Następnie odejmuje tę liczbę od całkowitej długości nazwy (określonej przez funkcję DŁ). To daje liczbę znaków w nazwisku.

To nazwisko jest następnie wyodrębniane za pomocą funkcji PRAWO.

Uwaga: Te funkcje mogą nie działać dobrze, jeśli masz początkowe, końcowe lub podwójne spacje w nazwach. Kliknij tutaj, aby dowiedzieć się, jak usunąć początkowe / końcowe / podwójne spacje w programie Excel.

Dzielenie nazw zawierających imię, drugie imię i nazwisko

Mogą wystąpić przypadki, gdy uzyskać kombinację imion, w przypadku których niektóre imiona mają również drugie imię.

Wzór w takich przypadkach jest nieco skomplikowany.

Wyodrębnianie imienia

Aby uzyskać imię:

=LEFT(A2,SEARCH(" ",A2)-1)

Jest to ta sama formuła, której używaliśmy, gdy nie było drugiego imienia. Po prostu szuka pierwszego znaku spacji i zwraca wszystkie znaki przed spacją.

Wyodrębnianie drugiego imienia

Aby uzyskać drugie imię:

=IFERROR(MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)),"")

Funkcja MID zaczyna się od pierwszego znaku spacji i wyodrębnia drugie imię, używając różnicy pozycji pierwszego i drugiego znaku spacji.

W przypadku brak drugiego imienia, funkcja MID zwraca błąd. Aby uniknąć błędu, jest zawinięty w funkcję IFERROR.

Wyodrębnianie nazwiska

Aby uzyskać nazwisko, użyj poniższej formuły:

=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,RIGHT(A2,LEN(A2)-SEARCH(" ",A2)),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2)+1)))

Ta formuła sprawdza, czy istnieje drugie imię, czy nie (przez zliczenie spacji). Jeśli jest tylko 1 znak spacji, po prostu zwraca cały tekst po prawej stronie znaku spacji.

Ale jeśli są 2, wówczas znajduje drugi znak spacji i zwraca liczbę znaków po druga spacja.

Uwaga: Ta formuła działa dobrze, gdy masz imiona, które zawierają tylko pierwsze imię i nazwisko lub imię, drugie imię i nazwisko. Jeśli jednak masz mieszankę, w której masz sufiksy lub zwroty grzecznościowe, będziesz musiał dalej modyfikować formuły.

Podziel komórki w Excelu za pomocą funkcji Flash Fill

Flash Fill to nowa funkcja wprowadzona w programie Excel 2013.

Może być naprawdę przydatna, gdy masz wzorzec i chcesz szybko wyodrębnić jego część.

Na przykład weźmy imię i dane nazwiska:

Wypełnianie flashowe działa poprzez identyfikację wzorców i replikację go dla wszystkich innych komórek.

Oto jak możesz wyodrębnić imię z listy za pomocą funkcji Flash Fill:

• W komórce B2 wprowadź imię Bruce’a Wayne’a (tj. Bruce).
• Po zaznaczeniu komórki zauważysz mały kwadrat na prawym końcu zaznaczenia komórek. Kliknij go dwukrotnie. Spowoduje to wypełnienie tej samej nazwy we wszystkich komórkach.
• Gdy komórki zostaną wypełnione, w prawym dolnym rogu zobaczysz ikonę Opcje autouzupełniania. Kliknij go.
• Wybierz wypełnienie błyskawiczne z listy.
• Jak zaraz po wybraniu opcji Wypełnianie błyskawiczne zauważysz, że wszystkie komórki aktualizują się i wyświetlają teraz imię każdej nazwy.

Jak działa wypełnianie błyskawiczne?

Flash Fill wyszukuje wzorce w zbiorze danych i powiela wzorzec.

Flash Fill to zaskakująco inteligentna funkcja, która działa zgodnie z oczekiwaniami w większości przypadków. Ale w niektórych przypadkach też zawodzi.

Na przykład, jeśli mam listę nazwisk, która zawiera kombinację imion, przy czym niektóre mają drugie imię, a inne nie.

Jeśli w takim przypadku wyodrębnię drugie imię, funkcja Flash Fill błędnie zwróci nazwisko w przypadku braku imienia.

Szczerze mówiąc, to wciąż dobre przybliżenie trendu. Jednak nie tego chciałem.

Ale nadal jest to wystarczająco dobre narzędzie, które można przechowywać w swoim arsenale i używać, gdy zajdzie taka potrzeba.

Oto kolejny przykład, w którym Flash Fill działa znakomicie.

Mam zestaw adresów, z których chcę szybko wyodrębnić miasto.

Aby szybko pobrać miasto, wprowadź nazwę miasta dla pierwszego adresu (w tym przykładzie wprowadź Londyn w komórce B2) i użyj funkcji autouzupełniania, aby wypełnić wszystkie komórki. Teraz użyj funkcji Flash Fill i natychmiast podasz nazwę miasta z każdego adresu.

Podobnie możesz podzielić adres i wyodrębnić dowolną część adresu.

Pamiętaj, że wymagałoby, aby adres był jednorodnym zestawem danych z tym samym ogranicznikiem (w tym przypadku przecinkiem).

Jeśli spróbujesz użyć funkcji Flash Fill, gdy nie ma żadnego wzoru, wyświetli się błąd, jak pokazano poniżej:

W tym samouczku omówiłem trzy różne sposoby dzielenia komórek w Excelu na wiele kolumn (używając tekstu do Kolumny, formuły i wypełnianie Flash)

Mamy nadzieję, że ten samouczek programu Excel okazał się przydatny.

Spodobają Ci się również następujące samouczki programu Excel:

• Jak do szybkiego łączenia komórek w programie Excel.
• Jak wyodrębnić podłańcuch w programie Excel za pomocą formuł.
• Jak liczyć komórki zawierające ciągi tekstowe.
• Wyodrębnij nazwy użytkowników z Identyfikatory e-mail w programie Excel.
• Jak podzielić wiele wierszy w komórce na oddzielne komórki / kolumny.