fbpx

Jak podzielić tekst w kolumnie w Excelu?

Pracując w Excelu często zdarza się potrzeba rozbicia jakichś danych zapisane w komórkach na mniejsze partie, aby móc sprawnie i wygodnie analizować dane. Czasem potrzebujemy wyodrębnić sobie jakąś część zawartości np. kilka końcowych czy początkowych cyfr z numeru identyfikacyjnego, innym razem chcielibyśmy po prostu rozdzielić sobie dane w komórce zawierającej np. imię i nazwisko na dwie osobne kolumny.

Metod podziału danych w kolumnie jest wiele, możemy wyróżnić co najmniej trzy: za pomocą funkcji, narzędzi danych oraz dodatku PowerQuery. W niniejszym artykule przedstawimy trzy przykłady, pierwszy wykorzystujący funkcje Excela oraz kolejne dwie korzystające z narzędzi danych.

Podział za pomocą funkcji

W naszym przykładzie chcielibyśmy rozdzielić sobie dane w kolumnie zawierającej imię i nazwisko na dwie różne kolumny, tak aby imię było w jednej, a nazwisko w drugiej kolumnie. Do tego celu wykorzystamy funkcje Excela z kategorii Tekstowych.

Samo zastosowanie funkcji musi być dosyć uniwersalne, to znaczy chcemy napisać jedną funkcję, a potem przeciągnąć ją na wszystkie pozostałe wiersze i wyniki muszą być poprawne, niezależnie od tego ile znaków zawiera poszczególne imię i nazwisko.

Do „wyjęcia” imienia z komórki potrzebujemy funkcji, która pobierze nam znaki od początku komórki do spacji, czyli znaku rozdzielającego oba wyrazy. Wykorzystamy więc funkcje LEWY, która pobierze nam znaki od lewej strony komórki oraz ZNAJDŹ, która określi nam gdzie w naszym tekście znajduje się spacja. Budowa naszej funkcji wygląda następująco:

  • LEWY
    • Tekst – wskazujemy komórkę, z której chcemy pobierać dane, czyli A2,
    • [Liczba_znaków] – tutaj podajemy zagnieżdżoną funkcję ZNAJDŹ i od jej wyniku odejmujemy 1, ponieważ funkcja ZNAJDŹ zwraca w wyniku numer miejsca, na którym stoi spacja, a my chcemy wziąć tyle znaków ile jest do miejsca przed spacją.
  • ZNAJDŹ
    • Szukany_tekst – czyli nasza spacja wpisana w cudzysłowie,
    • W_tekście – wskazujemy komórkę, w której szukamy spacji,
    • [Liczba_początkowa] – argument określający od którego znaku chcemy szukać naszego szukanego tekstu – u nas argument został pominięty.

Finalna funkcja wygląda następująco: =LEWY(A2;ZNAJDŹ(” „;A2)-1)

Podział tekstu na kolumny

Teraz pozostało już tylko „wyjęcie” nazwiska, czyli drugiego wyrazu. Do tego celu skorzystamy z funkcji FRAGMENT.TEKSTU oraz ponownie ZNAJDŹ. Budowa naszych funkcji wygląda następująco:

  • FRAGMENT.TEKSTU
    • Tekst – wskazujemy komórkę, z której chcemy pobrać dane, czyli A2,
    • Liczba_początkowa – tutaj podajemy zagnieżdżoną funkcję ZNAJDŹ i do jej wyniku dodajemy 1, ponieważ funkcja ZNAJDŹ zwraca w wyniku numer miejsca, na którym stoi spacja, a my chcemy rozpocząć pobieranie znaków od miejsca za spacją,
    • Liczba_znaków – określamy jak dużo znaków chcemy pobrać. Wystarczy podać liczbę większą niż długość najdłuższego nazwiska. U nas wpisano 20.
  • ZNAJDŹ – funkcja uzupełniona identycznie jak w poprzednim przypadku.

Finalna funkcja wygląda następująco: =FRAGMENT.TEKSTU(A2;ZNAJDŹ(” „;A2)+1;20)

Podział tekstu na kolumny

Podział za pomocą narzędzia Tekst jako kolumny

W drugim przykładzie chcemy wyciągnąć dane z kolumny zawierającej numer faktury oraz datę w taki sposób, aby do nowych kolumn zwrócić tylko same wartości składające się na datę. Wykonanie tego zadania za pomocą funkcji byłoby mocno rozbudowane, natomiast my skorzystamy z narzędzia Tekst jako kolumny, które w prosty sposób pozwoli nam zrealizować to czego oczekujemy.

Samo narzędzie znajduje się w sekcji Narzędzia danych, na karcie Dane. Za jego pomocą podzielimy dane z jednej na kilka kolumn, z ewentualnym pominięciem wybranych danych. Zanim uruchomimy narzędzie to analizowane dane trzeba zaznaczyć. Po wykonaniu tej czynności klikamy w przycisk Tekst jako kolumny. Otworzy nam się okno narzędzia, które składa się z trzech kroków.

Krok 1 z 3:

W pierwszym kroku możemy przeczytać, że nasze dane zawierają separator – jest to prawda – chodzi o znak ukośnika (/). Natomiast wciąż mamy możliwość określenia w jaki sposób nasze dane wyglądają. Czy typ pliku to:

  • Rozdzielany – czyli zawiera konkretny separator dzielący dane na części,
  • Stała szerokość – gdzie możemy określić co który znak (np. co 5) powinniśmy dzielić dane.

W naszym przypadku oczywiście zostawiamy domyślną opcję, czyli typ Rozdzielany i klikamy Dalej.

Podział tekstu na kolumny

Krok 2 z 3:

W drugim kroku możemy przede wszystkim wybrać ogranicznik, czyli nasz separator. Domyślnie zaznaczony jest Tabulator, natomiast w naszym przypadku jest to ukośnik. Aby ustawić wszystko poprawnie musimy odznaczyć pole wyboru przy pozycji Tabulator, a zaznaczyć przy pozycji Inny i ręcznie do okienka tekstu wpisać znak ukośnika (/). W tym momencie widzimy podgląd na samym dole okna, który wyświetla teraz podział na kolumny. Klikamy Dalej.

Podział tekstu na kolumny

Krok 3 z 3:

W kroku trzecim możemy ustawić format danych w każdej kolumnie osobno. Jest to przydatna opcja, jeśli mamy dane, które muszą być przechowane jako tekst lub są datą zgodną z formatem Excela i chcemy mieć pewność, że zostanie ona poprawnie przez program wykryta. My w tym kroku wykonamy dwie rzeczy, pierwsza to pominięcie kolumny pierwszej i ostatniej, ponieważ zawierają one tekst „FV” oraz numer faktury, a te dane nas aktualnie nie interesują.

Aby pominąć kolumnę należy na nią kliknąć w obszarze podglądu celem jej zaznaczenia, a następnie wybrać format danych jako Nie importuj kolumny (pomiń).

Druga istotna rzecz to miejsce docelowe, czyli wskazanie Excelowi w których komórkach ma zwrócić nowe, podzielone dane. Standardowo kreator zaproponuje nam wklejenie danych w tym samym miejscu, od którego zaznaczaliśmy dane. Pozostawienie takiego wyboru spowoduje, że Excel zastąpi naszą kolumnę z danymi oraz kolejne, które są z prawej strony, więc nie zalecamy takiego rozwiązania. W przykładzie mamy przygotowane miejsce na nowe, rozdzielone dane, są to kolumny C, D, E, wystarczy więc, że jako miejsce docelowe podamy adres komórki C2. Rozpoczynając od tej komórki dane zostaną wklejone do dołu i w prawą stronę.

Podział tekstu na kolumny

Klikamy Zakończ.

Może okazać się, że Excel wyświetli nam komunikat o istniejących danych (jak poniżej).

Podział tekstu na kolumny

Klikamy OK akceptując ostrzeżenie. Samo wyświetlenie się komunikatu jest związane z ustawieniem obramowania na komórkach w kolumnach C, D, E – w związku z tym Excel wykrywa w docelowym miejscu „jakieś dane”, czyli po prostu format komórek.

Finalny wygląd danych poniżej:

Podział tekstu na kolumny

Narzędzie Wypełnianie błyskawiczne

Ostatnią metodą jest skorzystanie z narzędzia Wypełniania błyskawicznego, choć nie każdy takie narzędzie może wykorzystać. W Excelu jest ono dostępne dopiero od wersji 2013, a jego działanie opiera się na wykryciu wzorca wypełniania i zastosowanie go na kolejnych wierszach.

Aby skorzystać z narzędzia musimy do pustej komórki wpisać wartość jaka powinna pokazać się jako wynik, natomiast w komórce poniżej musimy uruchomić narzędzie poprzez przycisk Wypełnianie błyskawiczne znajdujący się na karcie Dane w sekcji Narzędzia danych lub skrót klawiszowy Ctrl + E.

Cała praca narzędzia polega na wykryciu pewnego wzorca wypełniania przeszukując dane w pobliżu aktywnej komórki i zastosowania go do poniższych wierszy. Działanie narzędzia obrazuje poniższa grafika.

Podział tekstu na kolumny

Który sposób najlepiej wybrać?

Wszystko zależy od tego ile mamy czasu, jak skomplikowane dane do rozdzielenia oraz czy interesuje nas jednorazowe czy dynamiczne rozdzielanie zmieniających się danych.

Najszybszą metodą jest użycie Wypełniania błyskawicznego, ale również najbardziej zawodną. Często zdarza się, że narzędzie nie jest w stanie wykryć wzorca wypełniania, w związku z czym nie wykona żadnej operacji.

Metodą działającą zawsze, ale w której musimy się przeklikać przez zestaw opcji jest wykorzystanie narzędzia Tekst jako kolumny. Nie zadziała ono tak szybko jak wspomniane poprzednio, aczkolwiek nie będzie mieć problemu z wykryciem wzorca, ponieważ to jak dane mają być rozdzielone określamy sami. Zarówno jedno jak i drugie narzędzie wykonuje pracę po uruchomieniu polecenia. Oznacza to, że jeśli dane wejściowe ulegną zmianie to dane wynikowe się nie zmienią. Aby uległy zmianie musielibyśmy przeklikać się przez narzędzia raz jeszcze.

Również zawsze działająca metoda to wykorzystanie funkcji, które dodatkowo będą działać dynamicznie, to znaczy jeśli dane wejściowe ulegną zmianie to funkcje od razu zmienią też dane wynikowe. Jest to najtrudniejsze rozwiązanie do napisania, aczkolwiek najbardziej dynamiczne.

Odpowiedź na pytanie, która metoda jest najlepsza nie jest prosta, ponieważ każdy sposób jest dobry, wszystko zależy od tego na czym nam w danej chwili zależy najbardziej.

Zobacz również:

Szkolenia:

udostępnij:
Gratulacje!
Wniosek został wysłany prawidłowo.
Dziękujemy za przesłanie zapytania. Skontaktujemy się z Tobą najszybciej jak to możliwe.