Jak podzielić komórki w Excelu (rozdzielić na wiele kolumn)

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

Prostym przykładem, w którym musisz podzielić komórki w programie Excel, jest sytuacja, gdy masz pełne imiona i nazwiska i chcesz je podzielić na imię i nazwisko.

Lub otrzymujesz adres” i chcesz podzielić adres, aby oddzielnie analizować miasta lub kod PIN.

Jak podzielić komórki w Excelu

W tym samouczku dowiesz się, jak dzielić komórki w programie Excel przy użyciu następujących technik:

• Korzystanie z funkcji Tekst na kolumnę.
• Korzystanie z funkcji tekstowych programu Excel.
• Korzystanie z funkcji Flash Fill (dostępne w latach 2013 i 2016).

Zaczynajmy!

Podziel komórki w Excelu za pomocą tekstu do kolumny

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

Oto kroki, aby podzielić te imiona na imię i nazwisko:

• Zaznacz 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 „Tekst do kolumn”.
• W kreatorze Konwertuj tekst na kolumny:
  • Krok 1 z 3 kreatora tekstu do kolumn: Upewnij się, że wybrana jest opcja Rozdzielany (jest to wybór domyślny). Umożliwiłoby to oddzielenie imienia i nazwiska na podstawie określonego separatora (w tym przypadku spacja).
  • Kliknij Dalej.
  • Krok 2 z 3 Kreator tekstu do kolumn: Wybierz Spację jako ogranicznik i odznacz wszystko inne. Możesz zobaczyć, jak będzie wyglądał Twój wynik, w sekcji Podgląd danych w oknie dialogowym.
  • Kliknij Dalej.
  • Krok 3 z 3 Kreator tekstu do kolumn: 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 wybrano B2.
  • Kliknij Zakończ.

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

Notatka:

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

Podziel komórki w Excelu za pomocą funkcji tekstowych

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

Podczas gdy funkcja Tekst do kolumny daje wynik statyczny, wynik uzyskiwany z używania funkcji jest dynamiczny i zostanie automatycznie zaktualizowany po zmianie oryginalnych danych.

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

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

Wyodrębnianie imienia

Aby uzyskać imię z tej listy, użyj następującej formuły:

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

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

Ta formuła używa funkcji SEARCH, aby uzyskać pozycję znaku spacji. W przypadku Bruce'a Wayne'a znak kosmiczny znajduje się na 6 pozycji. Następnie wyodrębnia wszystkie znaki z lewej strony za pomocą funkcji LEWO.

Wyodrębnianie nazwiska

Podobnie, aby uzyskać nazwisko, użyj następującego wzoru:

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

Ta formuła używa funkcji wyszukiwania, aby znaleźć pozycję spacji za pomocą funkcji SZUKAJ. Następnie odejmuje tę liczbę od całkowitej długości nazwy (podanej przez funkcję LEN). Daje to liczbę znaków w nazwisku.

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

Notatka: Funkcje te mogą nie działać dobrze, jeśli w nazwach występują początkowe, końcowe lub podwójne spacje. Kliknij tutaj, aby dowiedzieć się, jak usunąć wiodące / końcowe / podwójne spacje w programie Excel.

Dzielenie imion, które mają imię, drugie imię i nazwisko

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

Formuła w takich przypadkach jest nieco skomplikowana.

Wyodrębnianie imienia

Aby uzyskać imię:

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

To jest 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ą.

Wydobywanie drugiego imienia

Aby uzyskać drugie imię:

=JEŻELIBŁĄD(MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)),"")

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

W przypadku braku drugiego imienia funkcja MID zwraca błąd. Aby uniknąć błędu, jest on opakowany w funkcję JEŻELI.BŁĄD.

Wyodrębnianie nazwiska

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

=JEŻELI(DŁ(A2)-DŁ(ZAMIENNIK(A2,"",""))=1,PRAWY(A2,DŁ(A2)-SZUKAJ(" ",A2)),PRAWY(A2,DŁ(A2) -SZUKAJ(" ",A2,SZUKAJ(" ",A2)+1)))

Ta formuła sprawdza, czy występuje drugie imię, czy nie (licząc liczbę znaków spacji). Jeśli jest tylko 1 znak spacji, po prostu zwraca cały tekst na prawo od znaku spacji.

Ale jeśli są 2, to wykrywa drugi znak spacji i zwraca liczbę znaków po drugiej spacji.

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

Podziel komórki w Excelu za pomocą Flash Fill

Flash Fill to nowa funkcja wprowadzona w programie Excel 2013.

To może być bardzo przydatne, gdy masz wzór i chcesz szybko wydobyć jego część.

Na przykład weźmy imię i nazwisko:

Wypełnianie Flash działa poprzez identyfikowanie wzorców i replikowanie ich we wszystkich pozostałych komórkach.

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

• W komórce B2 wprowadź imię Bruce'a Wayne'a (tj. Bruce'a).
• Po wybraniu komórki zauważysz mały kwadrat na prawym końcu zaznaczenia komórki. Kliknij go dwukrotnie. Spowoduje to wypełnienie tej samej nazwy we wszystkich komórkach.
• Gdy komórki są wypełnione, w prawym dolnym rogu zobaczysz ikonę Opcje autouzupełniania. Kliknij na to.
• Wybierz z listy opcję Flash Fill.
• Jak tylko wybierzesz opcję Flash Fill, zauważysz, że wszystkie komórki aktualizują się i wyświetlają teraz imię dla każdej nazwy.

Jak działa Flash Fill?

Flash Fill wyszukuje wzorce w zestawie danych i replikuje wzorzec.

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

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

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

Szczerze mówiąc, to wciąż dobre przybliżenie trendu. Jednak nie o to mi chodziło.

Ale nadal jest wystarczająco dobrym narzędziem, aby trzymać go 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 wydobyć miasto.

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

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

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

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

W tym samouczku omówiłem trzy różne sposoby dzielenia komórek w programie Excel na wiele kolumn (przy użyciu tekstu do kolumn, formuł i wypełniania Flash)

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