jak podzielić ciąg tekstowy w programie Excel za pomocą przecinków, spacji, znaku lub maski
samouczek wyjaśnia, jak podzielić komórki w programie Excel za pomocą formuł i funkcji Podziel tekst. Dowiesz się, jak oddzielić tekst przecinkiem, spacją lub dowolnym innym ogranicznikiem oraz jak podzielić ciągi znaków na tekst i liczby.
dzielenie tekstu z jednej komórki na kilka komórek jest zadaniem, z którym wszyscy użytkownicy programu Excel mają do czynienia od czasu do czasu., W jednym z naszych wcześniejszych artykułów omówiliśmy, jak dzielić komórki w programie Excel za pomocą funkcji Tekst na kolumnę, wypełniania Flash i dzielenia nazw. Dzisiaj przyjrzymy się dogłębnie, w jaki sposób można podzielić ciągi za pomocą formuł i funkcji Podziel tekst.,
- Jak podzielić komórki w programie Excel za pomocą formuł
- podziel ciąg przez przecinek, dwukropek, ukośnik, myślnik lub inny ogranicznik
- podziel ciąg przez podział linii
- formuły do dzielenia tekstu i liczb
- Jak oddzielić komórki za pomocą funkcji Podziel tekst
- podziel komórkę za pomocą ciągu
- podziel komórkę za pomocą maski (wzór)
jak podzielić tekst w programie Excel za pomocą formuł
aby podzielić ciąg w programie Excel, zwykle używa się lewej, prawej lub środkowej funkcji w połączeniu z Znajdź lub szukaj., Na pierwszy rzut oka niektóre formuły mogą wyglądać na złożone, ale logika jest w rzeczywistości dość prosta, a poniższe przykłady dadzą ci kilka wskazówek.
podziel ciąg przez przecinek, dwukropek, ukośnik, myślnik lub inny ogranicznik
podczas dzielenia komórek w programie Excel kluczem jest zlokalizowanie pozycji ogranicznika w ciągu tekstowym. W zależności od zadania Można to zrobić za pomocą wyszukiwania bez rozróżniania wielkości liter lub wyszukiwania bez rozróżniania wielkości liter. Gdy masz pozycję ogranicznika, użyj funkcji prawo, lewo lub MID, aby wyodrębnić odpowiednią część ciągu tekstowego., Dla lepszego zrozumienia, rozważmy następujący przykład.
przypuśćmy, że masz listę SKU wzorca elementu-kolor-rozmiar i chcesz podzielić kolumnę na 3 oddzielne kolumny:
Jak podzielić ciąg po podziale linii w programie Excel
aby podzielić tekst za pomocą spacji, użyj formuł podobnych do tych pokazanych w poprzednim przykładzie. Jedyną różnicą jest to, że będziesz potrzebował funkcji CHAR, aby podać znak podziału linii, ponieważ nie możesz wpisać go bezpośrednio w formule.,
przypuśćmy, że komórki, które chcesz podzielić, wyglądają podobnie do tego:
weź formuły z poprzedniego przykładu i zamień myślnik („-„) na char(10), gdzie 10 jest kodem ASCII dla kanału linii.
i tak wygląda wynik:
Jak podzielić tekst i liczby w Excelu
Po pierwsze, nie ma uniwersalnego rozwiązania, które działałoby dla wszystkich ciągów alfanumerycznych. Jaką formułę użyć zależy od konkretnego wzoru ciągu. Poniżej znajdziesz formuły dla dwóch typowych scenariuszy.,
podziel ciąg znaków wzorca „tekst + liczba”
przypuśćmy, że masz kolumnę łańcuchów z tekstem i liczbami połączonymi, gdzie liczba zawsze podąża za tekstem. Chcesz złamać oryginalne ciągi znaków tak, aby tekst i liczby pojawiały się w oddzielnych komórkach, jak to:
wynik można osiągnąć na dwa różne sposoby.,
Metoda 1: Policz cyfry i wyodrębnij tyle znaków
najprostszym sposobem na podzielenie ciągu tekstowego, w którym liczba pojawia się po tekście jest to:
aby wyodrębnić liczby, przeszukujesz ciąg dla każdej możliwej liczby od 0 do 9, uzyskasz liczbę całkowitą i zwrócisz tyle znaków z końca łańcucha.,
z oryginalnym ciągiem w A2, formuła wygląda następująco:
=RIGHT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))
aby wyodrębnić tekst, obliczysz, ile znaków tekstowych zawiera ciąg, odejmując liczbę wyodrębnionych cyfr (C2) od całkowitej długości oryginalnego ciągu w A2. Następnie użyj funkcji LEFT, aby zwrócić tyle znaków z początku łańcucha.,
=LEFT(A2,LEN(A2)-LEN(C2))
gdzie A2 jest oryginalnym ciągiem, a C2 jest wyodrębnioną liczbą, Jak pokazano na zrzucie ekranu:
Metoda 2: Znajdź pozycję pierwszej cyfry w ciągu
alternatywnym rozwiązaniem byłoby użycie następującego wzoru aby określić pozycję pierwszej cyfry w łańcuchu:
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
szczegółowe wyjaśnienie logiki formuły można znaleźć tutaj.,
Po znalezieniu pozycji pierwszej cyfry można podzielić tekst i liczby za pomocą bardzo prostych formuł lewej i prawej.
aby wyodrębnić tekst:
=LEFT(A2, B2-1)
aby wyodrębnić numer:
=RIGHT(A2, LEN(A2)-B2+1)
gdzie A2 jest oryginalnym ciągiem, a B2 jest pozycją pierwszej liczby., pierwsza cyfra, możesz osadzić formułę MIN w lewej i prawej funkcji:
formuła do wyodrębniania tekstu:=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)
formuła do wyodrębniania liczb:
=RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)
podziel ciąg wzorca „liczba + tekst”
Jeśli dzielisz komórki gdzie tekst pojawia się po numerze, możesz wyodrębnić liczby za pomocą następującej formuły:
=LEFT(A2, SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, ""))))
formuła jest podobna do tej omówionej w poprzednim przykładzie, z tym wyjątkiem, że używasz lewej funkcji zamiast prawej, aby uzyskać numer z lewej strony łańcucha.,
gdy masz już cyfry, wyodrębnij tekst, odejmując liczbę cyfr od całkowitej długości oryginalnego ciągu:
=RIGHT(A2,LEN(A2)-LEN(B2))
gdzie A2 jest oryginalnym ciągiem, a B2 jest wyodrębnioną liczbą, Jak pokazano na zrzucie ekranu poniżej:
w ten sposób można podzielić ciągi w programie Excel za pomocą różnych kombinacji różnych funkcji., Jak widać, formuły nie są oczywiste, więc możesz pobrać przykładowy skoroszyt Excel Split Cells, aby zbadać je bliżej.
Jeśli odkrywanie tajemnych zwrotów formuł programu Excel nie jest Twoim ulubionym zajęciem, może Ci się spodobać wizualna metoda dzielenia komórek w programie Excel, która została pokazana w następnej części tego samouczka.,
jak podzielić komórki w programie Excel za pomocą funkcji Podziel tekst
alternatywnym sposobem dzielenia kolumny w programie Excel jest użycie funkcji Podziel tekst dołączonej do naszego pakietu Ultimate dla programu Excel, która zapewnia następujące opcje:
- podziel komórkę przez znak
- podziel komórkę przez ciąg
- podziel komórkę przez maskę (wzór)
aby wszystko było jaśniejsze, przyjrzyjmy się bliżej każdej opcji, po kolei.
Podziel komórki według znaków
wybierz tę opcję, gdy chcesz podzielić zawartość komórki przy każdym wystąpieniu określonego znaku.,
w tym przykładzie Przyjmijmy ciągi wzorca Element-kolor-rozmiar, którego użyliśmy w pierwszej części tego samouczka. Jak zapewne pamiętasz, podzieliliśmy je na 3 różne kolumny, używając 3 różnych formuł. I oto jak możesz osiągnąć ten sam wynik w 2 szybkich krokach:
- zakładając, że masz zainstalowany pakiet Ultimate, wybierz komórki do podziału i kliknij ikonę Podziel tekst na Karcie Danych Ablebits.,
- panel podzielonego tekstu otworzy się po prawej stronie okna programu Excel i wykonasz następujące czynności:
- rozwiń grupę podziel według znaków i wybierz jeden z predefiniowanych ograniczników lub wpisz dowolny inny znak w polu niestandardowym.
- Wybierz podział komórek na kolumny lub wiersze.
- przejrzyj wynik w sekcji Podgląd i kliknij przycisk podziel.
gotowe! Zadanie, które wymagało 3 formuł i 5 różnych funkcji, zajmuje teraz tylko kilka sekund i kliknięcie przycisku.
Podziel komórki za pomocą ciągu znaków
Ta opcja umożliwia dzielenie łańcuchów za pomocą dowolnej kombinacji znaków jako ogranicznika. Technicznie rzecz biorąc, łańcuch znaków dzieli się na części, używając jednego lub kilku różnych podłańcuchów jako granic każdej części.,
na przykład, aby podzielić zdanie za pomocą spójników „and” I „or”, rozwiń grupę Split by strings i wprowadź ciągi ograniczników, po jednym w wierszu:
w rezultacie fraza źródłowa jest oddzielana przy każdym wystąpieniu każdego ogranicznika:
a tu kolejny, prawdziwy przykład., Przypuśćmy, że zaimportowałeś kolumnę dat z zewnętrznego źródła, która wygląda następująco:
5.1.2016 12:20
5.2.2016 14:50
ten format nie jest konwencjonalny dla programu Excel i dlatego żadna z funkcji daty Nie rozpoznałaby żadnego z elementów daty lub czasu. Aby podzielić dzień, miesiąc, rok, godziny i minuty na oddzielne komórki, wprowadź następujące znaki w polu Podziel przez ciągi znaków:
- kropka (.,) aby oddzielić dzień, miesiąc i rok
- dwukropek (:) aby oddzielić godziny i minuty
- Spacja aby oddzielić datę i czas
naciśnij przycisk podziel, a natychmiast otrzymasz wynik:
Podziel komórki za pomocą maski (wzorzec)
oddzielenie komórki za pomocą maski oznacza dzielenie ciągu znaków na podstawie wzorca.
Ta opcja jest bardzo przydatna, gdy trzeba podzielić listę jednorodnych łańcuchów na niektóre elementy lub podłańcuchy., Komplikacja polega na tym, że tekst źródłowy nie może być dzielony przy każdym wystąpieniu danego ogranicznika, tylko przy określonym wystąpieniu. Poniższy przykład ułatwi zrozumienie.
Załóżmy, że masz listę łańcuchów wyciągniętych z jakiegoś pliku dziennika:
chcesz mieć datę i godzinę, jeśli w ogóle, kod błędu i szczegóły wyjątku w 3 oddzielnych kolumnach., Nie można używać spacji jako ogranicznika, ponieważ istnieją spacje między datą i czasem, które powinny pojawić się w jednej kolumnie, oraz spacje w tekście wyjątku, które również powinny pojawić się w jednej kolumnie.
rozwiązaniem jest podzielenie ciągu znaków przez następującą maskę: * ERROR: * Exception: *
Gdzie gwiazdka ( * ) reprezentuje dowolną liczbę znaków.
dwukropki (:) są zawarte w ogranicznikach, ponieważ nie chcemy, aby pojawiały się w wynikowych komórkach.,
a teraz rozwiń sekcję Podziel przez maskę w Panelu Podziel tekst, wpisz maskę w polu Wprowadź ograniczniki i kliknij podziel:
wynik będzie wyglądał podobnie do tego:
dużą zaletą tej metody jest elastyczność.,jeśli chcesz, aby pojawiały się w różnych kolumnach, użyj tej maski:
* *ERROR:*Exception:*
w tłumaczeniu na angielski Maska instruuje dodatek, aby podzielił oryginalne ciągi znaków na 4 części:
- wszystkie znaki przed pierwszą spacją znajdującą się w łańcuchu (date)
- znaki między pierwszą spacją a słowem ERROR: (time)
- tekst między ERROR: a Exception: (kod błędu)
- wszystko, co pojawia się po wyjątku: (tekst wyjątku)
mam nadzieję, że spodobał ci się ten szybki i prosty sposób dzielenia ciągów w Excelu., Jeśli chcesz spróbować, wersja testowa jest dostępna do pobrania poniżej. Dziękuję za przeczytanie i mam nadzieję, że do zobaczenia na naszym blogu w przyszłym tygodniu!
dostępne pliki do pobrania
Excel dzielone formuły komórek (.plik xlsx)
Ultimate Suite 14-dniowa w pełni funkcjonalna wersja (.,plik zip)
- Jak podzielić komórki w Excelu
- jak rozdzielić nazwy w Excelu: podziel imię i nazwisko na różne kolumny
- Jak podzielić datę i czas w Excelu
- jak scalić dwie kolumny w Excelu bez utraty danych