Samouczek programu Excel: dzielenie tekstu, liczby i komórek daty (rozdzielone na wiele kolumn)

Podczas korzystania z programu Excel może być konieczne podzielenie tekstu z jednej komórki na wiele komórek w niektórych celach. Ten samouczek jest podzielony na trzy części: podzielone komórki tekstowe, podzielone komórki liczbowe i podzielone komórki daty. Każda część zawiera różne przykłady, które pomogą Ci dowiedzieć się, jak poradzić sobie z zadaniem podziału w przypadku napotkania tego samego problemu.

---

1 Podziel komórki tekstowe

W tej części zebrano sytuacje, które napotkasz podczas dzielenia komórki tekstowej na wiele kolumn, i przedstawiono odpowiednie metody.

Przykład #1 Podziel komórki według przecinka, spacji lub innych ograniczników

Aby podzielić komórkę tekstową na wiele kolumn za pomocą określonego separatora, takiego jak przecinek, spacja, myślnik itd., możesz zastosować jedną z poniższych metod.

Podziel komórki według ogranicznika za pomocą funkcji Tekst na kolumny

Połączenia Tekst do kolumn funkcja, jako wbudowana w Excel, jest często używana do dzielenia komórek. Jak pokazano na poniższym zrzucie ekranu, aby podzielić komórki w kolumnie Ciągi tekstowe przecinkiem, możesz zastosować funkcję Tekst do kolumn w następujący sposób.

1. Wybierz zakres kolumn, który chcesz podzielić przecinkami, kliknij Dane > Tekst do kolumn.

2. w Kreator konwersji tekstu na kolumny - krok 1 z 3 w oknie dialogowym, zachowaj Rozgraniczone zaznaczony przycisk radiowy, a następnie kliknij Następna przycisk.

3. w Kreator konwersji tekstu na kolumny - krok 2 z 3 w oknie dialogowym określ ogranicznik zgodnie z Twoimi potrzebami (w tym przypadku sprawdzam tylko Przecinek pole wyboru), a następnie kliknij Następna przycisk.

4. W oknie dialogowym ostatniego kroku kliknij przycisk, aby wybrać komórkę do wyprowadzenia oddzielonego tekstu, a następnie kliknij koniec przycisk.

Następnie teksty z wybranego zakresu są dzielone przecinkiem i umieszczane w różnych kolumnach, jak pokazano poniżej.

Podziel komórki według ogranicznika za pomocą formuły

Możesz zastosować poniższą formułę, aby podzielić teksty w komórkach według określonego ogranicznika w programie Excel.

Formuła ogólna

=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))

Argumenty

Następnie przejdź dalej, aby zastosować tę formułę.

1. Jak pokazano na poniższym zrzucie ekranu, najpierw musisz utworzyć wiersz pomocniczy z numerami 1, 2, 3... znajdującymi się w różnych komórkach.

Note: Tutaj 1, 2, 3... reprezentują pierwszy, drugi i trzeci podciąg ciągu tekstowego.

2. Wybierz komórkę pod komórką numer 1, skopiuj lub wprowadź do niej poniższą formułę i naciśnij Wchodzę klucz, aby uzyskać pierwszy podciąg ciągu tekstowego. Wybierz komórkę wynikową, przeciągnij ją Uchwyt autouzupełniania w prawo iw dół, aby uzyskać inne podciągi. Zobacz zrzut ekranu:

=TRIM(MID(SUBSTITUTE($B5,",",REPT(" ",LEN($B5))),(D$4-1)*LEN($B5)+1,LEN($B5)))

Note: We wzorze „,” to ogranicznik używany do dzielenia ciągu tekstowego w B5. Możesz je zmieniać według własnych potrzeb.

Podziel komórki według ogranicznika za pomocą niesamowitego narzędzia

Tutaj polecam Kutools dla programu Excel'S Podział komórki funkcja ułatwiająca łatwe dzielenie komórek na osobne kolumny lub wiersze według określonego ogranicznika w programie Excel.

1. Po zainstalowaniu Kutools dla programu Excel, wybierz zakres, w którym chcesz podzielić ciągi tekstowe, a następnie kliknij Kutools > Połącz i podziel > Podział komórki.

2. w Podział komórki W oknie dialogowym należy skonfigurować w następujący sposób.

3. W następnym Podział komórki w oknie dialogowym wybierz komórkę do wyprowadzenia podciągów, a następnie kliknij OK.

Następnie podciągi są dzielone na różne kolumny lub wiersze na podstawie danych określonych w kroku 2 powyżej.

Podziel na kolumny:

Podziel na rzędy:

Przykład #2 Podziel komórki według określonej długości

Aby podzielić ciąg tekstowy według określonej długości, możesz skorzystać z następujących metod.

Podziel komórki według określonej długości za pomocą funkcji Tekst na kolumny

Połączenia Tekst do kolumn funkcja zapewnia Naprawiono szerokość opcja, która pomoże Ci podzielić ciągi tekstowe w wybranych komórkach według określonej długości w programie Excel.

Jak pokazano na poniższym zrzucie ekranu, aby podzielić teksty w zakresie B5:B9 na kolumny co 3 znaki, możesz wykonać następujące czynności.

1. Wybierz komórki, w których chcesz podzielić ciągi tekstowe.

2. kliknij Dane > Tekst do kolumn.

3. w Kreator konwersji tekstu na kolumny — krok 1 z 3 w oknie dialogowym wybierz opcję Naprawiono szerokość kliknij przycisk i kliknij Następna.

4. Następnie Krok 2 3 pojawi się okno dialogowe. w Podgląd danych sekcji, kliknij żądaną pozycję na osi, aby utworzyć linię przerwania (linię ze strzałką). Po utworzeniu wszystkich linii przerwania, kliknij Następna przycisk, aby przejść dalej.

W tym przypadku tworzę linie łamania dla każdych 3 znaków w ciągu tekstowym.

5. W ostatnim kroku kreatora wybierz komórkę do wyprowadzenia rozdzielonych tekstów, a następnie kliknij koniec przycisk.

Teraz ciągi tekstowe w wybranych komórkach są dzielone co 3 znaki, jak pokazano na zrzucie ekranu poniżej.

Podziel komórki według określonej długości za pomocą niesamowitego narzędzia

Aby podzielić komórki według określonej długości w programie Excel, możesz zastosować Podział komórki cechą Kutools dla programu Excel aby zrobić to łatwo.

1. Wybierz komórki ciągu tekstowego, które chcesz podzielić według określonej długości, kliknij Kutools > Połącz i podziel > Podział komórki.

2. w Podział komórki W oknie dialogowym należy skonfigurować w następujący sposób.

3. W następnym Podział komórki w oknie dialogowym wybierz komórkę, w której chcesz umieścić dzielące się teksty, a następnie kliknij OK.

Następnie ciągi tekstowe w wybranych komórkach są dzielone według określonej długości i umieszczane w różnych kolumnach.

Przykład #3 Podziel komórki według określonego słowa

Jak pokazano na poniższym zrzucie ekranu, aby podzielić ciągi tekstowe w zakresie B5:B9 przez całe słowo „sprzedaż”, możesz zastosować formuły podane w tej sekcji.

Pobierz podciąg przed określonym słowem w komórce

Zastosowanie formuły opartej na funkcjach LEWY i ZNAJDŹ może pomóc w uzyskaniu podciągu przed określonym słowem w ciągu tekstowym.

Formuła ogólna

=LEFT(A1,FIND("certain_word", A1)-1)

Argumenty

1. Wybierz pustą komórkę, skopiuj lub wprowadź do niej poniższą formułę i naciśnij Wchodzę klucz, aby uzyskać podciąg przed określonym słowem. Wybierz tę komórkę wynikową, a następnie przeciągnij jej Uchwyt autouzupełniania aby zastosować formułę do innych komórek.

=LEFT(B5,FIND("sales",B5)-1)

Pobierz podciąg po słowie w komórce

Po uzyskaniu podciągu przed słowem należy zastosować poniższą formułę, aby uzyskać podciąg po nim.

Formuła ogólna

=TRIM(MID(A1,SEARCH("certain_word",A1)+LEN("certain_word"),255))

Argumenty

1. Wybierz pustą komórkę obok pierwszej komórki wynikowej (D5).

2. Skopiuj lub wprowadź do niego poniższą formułę i naciśnij Wchodzę klucz. Wybierz tę komórkę wynikową, przeciągnij jej Uchwyt autouzupełniania aby otrzymać inne wyniki.

=TRIM(MID(B5,SEARCH("sales",B5)+LEN("sales"),255))

Teraz podzieliłeś ciągi tekstowe na różne kolumny o całe słowo.

Przykład #4 Podziel komórki według podziału wiersza

W tej sekcji przedstawiono różne metody ułatwiające dzielenie komórek tekstowych według podziału wiersza w programie Excel.

Podziel komórki według podziału wiersza za pomocą funkcji Tekst na kolumny

Funkcję Tekst do kolumn można zastosować do dzielenia komórek według podziału wiersza w programie Excel. Możesz zrobić w następujący sposób.

1. Wybierz zakres komórek, w których chcesz podzielić teksty według podziału wiersza.

2. kliknij Dane > Tekst do kolumn.

3. w Kreator konwersji tekstu na kolumny - krok 1 z 3 w oknie dialogowym wybierz Rozgraniczone przycisk radiowy, a następnie kliknij Następna;

4. w Krok 2 3 okno dialogowe, odznacz wszystkie istniejące Ograniczniki zaznaczenia, sprawdź Inne pole wyboru, a następnie naciśnij Ctrl + J skrót. Widać, że w polu tekstowym wyświetlana jest tylko mała kropka, a następnie w Podgląd danych pole, teksty są dzielone podziałami wierszy. Kliknij Następna przycisk.

5. W ostatnim kroku kreatora wybierz komórkę docelową, aby wyprowadzić oddzielone teksty, a następnie kliknij koniec przycisk.

Następnie teksty w wybranych komórkach są dzielone na różne kolumny przez podziały wierszy.

Podziel komórki według podziału linii za pomocą formuły

Poniższe formuły mogą również pomóc w dzieleniu komórek według podziału wiersza w programie Excel.

Korzystając z tego samego przykładu co powyżej, po podzieleniu otrzymasz trzy podciągi w różnych kolumnach.

Pobierz podciąg przed pierwszym podziałem wiersza

Po pierwsze, możemy zastosować formułę opartą na LEWA oraz SZUKAJ funkcje do dzielenia podciągu przed złamaniem pierwszego wiersza w komórce.

Formuła ogólna

=LEFT(cell, SEARCH(CHAR(10),cell,1)-1)

Argumenty

1. Wybierz pustą komórkę (w tym przypadku D5), skopiuj lub wprowadź do niej poniższą formułę i naciśnij Wchodzę klucz. Wybierz komórkę wynikową i przeciągnij ją Uchwyt autouzupełniania w dół, aby uzyskać podciągi przed pierwszym podziałem wiersza innych komórek.

=LEWO(B5; SZUKAJ(ZNAK(10);B5,1;1)-XNUMX)

Pobierz podłańcuch między pierwszym i drugim podziałem wiersza

Aby uzyskać podciąg między podziałami pierwszego i drugiego wiersza w komórce, poniższa formuła może wyświadczyć ci przysługę.

Formuła ogólna

=MID(cell,SEARCH(CHAR(10),cell)+1,SEARCH(CHAR(10),cell,SEARCH(CHAR(10),cell)+1)-SEARCH(CHAR(10),cell)-1)

Argumenty

1. Wybierz komórkę (E5) obok D5, skopiuj lub wprowadź do niej poniższą formułę i naciśnij Wchodzę klucz. Wybierz komórkę wynikową i przeciągnij ją Uchwyt autouzupełniania w dół, aby uzyskać podciągi między pierwszym i drugim podziałem wiersza innych komórek.

=MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1 )

Pobierz podciąg po drugim łamaniu wiersza

Pierwszym krokiem jest uzyskanie podciągu po drugiej linii podziału za pomocą poniższej formuły.

Formuła ogólna

=RIGHT(cell,LEN(cell) - SEARCH(CHAR(10),cell, SEARCH(CHAR(10), cell) + 1))

Argumenty

1. Wybierz komórkę (w tym przypadku F5), skopiuj lub wprowadź do niej poniższą formułę i naciśnij Wchodzę klucz. Wybierz komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać podciągi po drugiej linii podziału innych komórek.

=PRAWO(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) + 1))

Podziel komórki według podziału wiersza za pomocą VBA

Ta sekcja zawiera kod VBA, który ułatwia dzielenie tekstów w wybranych komórkach według podziału wiersza w programie Excel.

1. wciśnij inny + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.

2. w Microsoft Visual Basic for Applications okno, kliknij wstawka > Moduł. Następnie skopiuj poniższy VBA do okna kodu.

Kod VBA: podziel komórki według podziału wiersza w programie Excel

Sub ExtendOffice_SplitRangeTex()
'Updated by Extendoffice 20211116
Dim xStr() As String
Dim xRg As Range
Dim xCell As Range
Dim xI As Integer
Set xRg = Application.InputBox("Please select the range of cells where you want to split by line break:", "Kutools for Excel", "", , , , , 8)
If xRg Is Nothing Then Exit Sub
For xI = 1 To xRg.Count
    Set xCell = xRg.Item(xI)
    xStr = VBA.Split(xCell.Value, vbLf)
    xCell.Resize(1, UBound(xStr) + 1).Offset(0, 1) = xStr
Next
End Sub

3. wciśnij F5 klucz do uruchomienia kodu. Następnie Kutools dla programu Excel pojawi się okno dialogowe, musisz wybrać zakres komórek, w których chcesz podzielić według podziału wiersza, a na koniec kliknij OK przycisk.

Następnie teksty w wybranych komórkach są dzielone na różne kolumny naraz przez podziały wierszy.

Podziel komórki według łamania linii za pomocą niesamowitego narzędzia

Tutaj wprowadź przydatne narzędzie - Podział komórki użyteczność Kutools dla programu Excel. Za pomocą tego narzędzia możesz zbiorczo dzielić teksty w wybranych komórkach według podziału wiersza za pomocą zaledwie kilku kliknięć.

1. Wybierz zakres komórek, w których chcesz podzielić teksty według podziału wiersza.

2. kliknij Kutools > Połącz i podziel > Podział komórki aby włączyć tę funkcję.

3. w Podział komórki W oknie dialogowym należy wprowadzić następujące ustawienia.

4. W następnym wyskakującym okienku Podział komórki w oknie dialogowym wybierz pustą komórkę, aby umieścić oddzielone teksty, i kliknij OK.

Następnie teksty w wybranych komórkach są dzielone na różne kolumny według podziałów wierszy, jak pokazano na zrzucie ekranu poniżej.

Przykład #5 Podziel komórki tylko według pierwszego lub ostatniego ogranicznika

Czasami może być konieczne podzielenie komórek na dwie części, które odnoszą się tylko do pierwszego lub ostatniego ogranicznika, możesz wypróbować poniższe formuły.

Podziel komórki według pierwszego ogranicznika za pomocą formuł

Jak pokazano na poniższym zrzucie ekranu, aby podzielić każdą komórkę w zakresie B5:B9 na dwie części za pomocą pierwszej spacji, musisz zastosować dwie formuły.

Podziel podciąg przed pierwszą spacją

Aby podzielić podciąg przed pierwszą spacją, możesz użyć formuły opartej na funkcji LEWO i funkcjach ZNAJDŹ.

Formuła ogólna

=LEFT(cell,FIND("delimiter",cell)-1)

Argumenty

1. Wybierz komórkę (w tym przypadku D5), aby wyprowadzić podciąg, skopiuj lub wprowadź do niej poniższą formułę i naciśnij Wchodzę klucz. Wybierz komórkę wynikową, a następnie przeciągnij jej Uchwyt autouzupełniania w dół, aby uzyskać podciąg innych komórek.

=LEFT(B5,FIND(" ",B5)-1)

Podziel podciąg po pierwszej spacji

Następnie zastosuj poniższą formułę, aby uzyskać podciąg po pierwszej spacji w komórce.

Formuła ogólna

=RIGHT(cell,LEN(cell)-FIND("delimiter",cell))

Argumenty

1. Skopiuj lub wprowadź poniższą formułę do komórki E5 i naciśnij Wchodzę klucz, aby uzyskać wynik. Wybierz tę komórkę wynikową i przeciągnij jej Uchwyt autouzupełniania w dół, aby uzyskać podciąg po ostatniej spacji innych komórek.

=RIGHT(B5,LEN(B5)-FIND(" ",B5))

Note: W formułach możesz zmienić odwołanie do komórki na ciąg tekstowy i ogranicznik zgodnie z własnymi potrzebami.

Podziel komórki według ostatniego ogranicznika za pomocą formuł

Aby podzielić teksty w zakresie komórek (B5: B9) na dwie części według ostatniego miejsca, jak pokazano na zrzucie ekranu, dwie formuły przedstawione w tej sekcji mogą pomóc w wykonaniu tego.

Pobierz tekst na lewo od ostatniego ogranicznika

Aby uzyskać tekst po lewej stronie ostatniego ogranicznika w komórce, możesz zastosować poniższą formułę.

Formuła ogólna

=LEFT(B5,SEARCH("^",SUBSTITUTE(cell,"delimiter","^",LEN(cell)-LEN(SUBSTITUTE(cell,"delimiter",""))))-1)

Argumenty

1. Wybierz komórkę, aby umieścić lewy podciąg, skopiuj lub wprowadź do niej poniższą formułę i naciśnij Wchodzę klucz. Wybierz komórkę wynikową i przeciągnij ją Uchwyt autouzupełniania aby zastosować formułę do innych komórek.

=LEFT(B5,SEARCH("^",SUBSTITUTE(B5," ","^",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))-1)

Pobierz tekst na prawo od ostatniego ogranicznika

Teraz musimy w tym przypadku umieścić tekst na prawo od ostatniej spacji.

Formuła ogólna

=TRIM(RIGHT(SUBSTITUTE(cell,"delimiter",REPT("delimiter",LEN(cell))),LEN(cell)))

Argumenty

1. Wybierz komórkę, aby umieścić odpowiedni podciąg, skopiuj lub wprowadź do niej poniższą formułę i naciśnij Wchodzę klucz. Wybierz komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby zastosować formułę do innych komórek.

=TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))),LEN(B5)))

Note: W formułach możesz zmienić odwołanie do komórki na ciąg tekstowy i ogranicznik zgodnie z własnymi potrzebami.

Przykład #6 Podziel komórki wielką literą

W tej sekcji wprowadzono funkcję zdefiniowaną przez użytkownika do dzielenia słów w komórkach według wielkiej litery.

1. wciśnij inny + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.

2. w Microsoft Visual Basic for Applications okno, kliknij wstawka > Moduł, a następnie skopiuj poniższy kod VBA do okna Code.

Kod VBA: Podziel komórki wielką literą w Excelu

Function GetName(s As String, Num As Long) As String
'Updated by Extendoffice 20211116
  With CreateObject("VBSCript.RegExp")
    .Global = True
    .Pattern = "[A-Z][a-z]+|[A-Z]"
    GetName = .Execute(s).Item(Num - 1)
  End With
End Function

3. wciśnij inny + Q klucze do zamknięcia Microsoft Visual Basic for Applications okno.

4. Wybierz komórkę (w tym przypadku D5), aby wyprowadzić słowo na lewo od pierwszej wielkiej litery, wprowadź do niej poniższą formułę i naciśnij Wchodzę klucz. Wybierz tę komórkę wynikową i przeciągnij jej Uchwyt autouzupełniania zaraz potem w dół, aby uzyskać inne słowa.

=IFERROR(GetName($B5,COLUMNS($D:D)),"")

Note: W kodzie $B5 to komórka, którą podzielisz, $D:D to kolumna komórki wynikowej. Zmień je zgodnie z własnymi danymi.

Przykład #7 Podziel nazwy w komórkach

Przypuśćmy, że masz arkusz roboczy zawierający kolumnę pełnych nazwisk i chcesz podzielić pełne imię i nazwisko na oddzielne kolumny, takie jak oddzielenie imienia i nazwiska od pełnego imienia i nazwiska lub oddzielenie pierwszego, drugiego imienia lub nazwiska od pełnego imienia i nazwiska. Ta sekcja zawiera szczegółowe kroki, które pomogą Ci rozwiązać te problemy.

Podziel imię i nazwisko na imię i nazwisko

Jak pokazano na poniższym zrzucie ekranu, imię, drugie imię i nazwisko są oddzielone pojedynczą spacją, aby oddzielić tylko imię i nazwisko od pełnego imienia i umieścić je w różnych kolumnach, możesz zastosować jeden z następujące metody.

1) Podziel pełne imię i nazwisko na imię i nazwisko za pomocą wzorów

Musisz zastosować dwie formuły osobno, aby podzielić pełne imię i nazwisko na imię i nazwisko.

Oddziel imię od pełnego imienia

Możesz zastosować formułę opartą na funkcjach LEWO i SZUKAJ, aby oddzielić imię od pełnego imienia i nazwiska.

Formuła ogólna

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

1. Wybierz komórkę, aby wyświetlić imię.

2. Skopiuj lub wprowadź do niego poniższą formułę i naciśnij Wchodzę klucz. Wybierz tę komórkę wynikową i przeciągnij jej Uchwyt autouzupełniania w dół, aby oddzielić imiona od innych pełnych nazwisk.

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

Teraz podzieliłeś wszystkie imiona z pełnych imion w określonym zakresie komórek, zastosuj poniższą formułę, aby podzielić nazwiska.

Oddziel nazwisko od pełnego imienia

Formuła ogólna

=RIGHT(cell, LEN(cell) - SEARCH("^", SUBSTITUTE(cell," ", "^", LEN(cell) - LEN(SUBSTITUTE(cell, " ", "")))))

1. Wybierz komórkę obok komórki z imieniem.

2. Skopiuj lub wprowadź do niego poniższą formułę i naciśnij Wchodzę klucz. Wybierz tę komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać nazwiska z innych pełnych nazw.

=RIGHT(B5, LEN(B5) - SEARCH("^", SUBSTITUTE(B5," ", "^", LEN(B5) - LEN(SUBSTITUTE(B5, " ", "")))))

Note: W formułach B5 to komórka z pełną nazwą, którą chcesz podzielić. Możesz to zmienić według potrzeb.

2) Podziel pełne imię na imię i nazwisko za pomocą niesamowitego narzędzia

Formuła jest trudna do zapamiętania dla wielu użytkowników Excela. Tutaj polecam Nazwy podzielone cechą Kutools dla programu Excel. Dzięki tej funkcji możesz łatwo podzielić pełne imię i nazwisko na imię i nazwisko za pomocą zaledwie kilku kliknięć.

1. Wybierz zakres komórek z pełną nazwą, kliknij Kutools > Połącz i podziel > Nazwy podzielone.

2. w Nazwy podzielone w oknie dialogowym, zaznacz tylko Imię oraz Nazwisko Okno w Rodzaje dzielone , a następnie kliknij OK.

Tips: Wybrany zakres jest wyświetlany w Zakres do podziału pole, możesz je zmienić w razie potrzeby.

3. Potem kolejny Nazwy podzielone pojawi się okno dialogowe, wybierz komórkę docelową i kliknij OK.

Następnie pełne imię i nazwisko w wybranych komórkach zostanie podzielone na imię i nazwisko zbiorczo, jak pokazano na zrzucie ekranu poniżej.

Podziel pełne imię na imię, drugie i nazwisko

Jak pokazano na poniższym zrzucie ekranu, znajduje się kolumna z pełnym imieniem i nazwiskiem z imieniem, drugim imieniem i nazwiskiem oddzielonymi pojedynczym odstępem. Aby podzielić pełne imię na imię, drugie imię i nazwisko i umieścić je w różnych kolumnach, pomocne mogą być poniższe metody.

1) Podziel pełne imię i nazwisko na imię, drugie imię i nazwisko z tekstem do kolumn

Wbudowana funkcja — Tekst na kolumny może pomóc w łatwym podzieleniu pełnego imienia i nazwiska na imię, drugie imię i nazwisko w programie Excel.

Możesz wykonaj czynności wymienione powyżej, aby zastosować funkcję Tekst do kolumn.

Note: W kreatorze Krok 2 z 3 zaznacz tylko Typ przestrzeni pudełko.

2) Podziel pełne imię na imię, drugie i nazwisko za pomocą formuł

Możesz także zastosować formuły, aby podzielić imię i nazwisko na imię, drugie imię i nazwisko w programie Excel.

Ogólne formuły używane do dzielenia pełnej nazwy

Podziel imię

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

Podziel drugie imię

=MID(cell, SEARCH(" ", cell) + 1, SEARCH(" ", cell, SEARCH(" ", cell)+1) - SEARCH(" ", cell)-1)

Podziel nazwisko

=RIGHT(cell,LEN(cell) - SEARCH(" ",cell, SEARCH(" ",cell,1)+1))

Następnie zastosuj formuły w różnych komórkach, aby uzyskać imię, drugie imię i nazwisko.

1. W komórce D5 wprowadź poniższą formułę i naciśnij Wchodzę klucz. Wybierz tę komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać imię innych pełnych nazw.

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

2. Wprowadź poniższą formułę w komórce E5 i naciśnij Wchodzę klucz, aby uzyskać pierwsze drugie imię. Wybierz komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać inne drugie imiona.

=MID(B5, SEARCH(" ", B5) + 1, SEARCH(" ", B5, SEARCH(" ", B5)+1) - SEARCH(" ", B5)-1)

3. Aby uzyskać nazwiska, wprowadź poniższą formułę do komórki F5 i naciśnij Wchodzę, a następnie zaznacz komórkę wynikową i przeciągnij jej uchwyt autouzupełniania na potrzebne komórki.

=RIGHT(B5,LEN(B5) - SEARCH(" ",B5, SEARCH(" ",B5,1)+1))

3) Podziel pełne imię na imię, drugie i nazwisko za pomocą niesamowitego narzędzia

Oto wprowadzenie do Kutools dla programu Excel'S Nazwy podzielone funkcja, która pozwala podzielić pełne imię na imię, drugie imię i nazwisko za pomocą kilku kliknięć.

1. Wybierz komórki z pełną nazwą, w których chcesz podzielić, a następnie kliknij Kutools > Połącz i podziel > Nazwy podzielone.

2. w Nazwy podzielone W oknie dialogowym należy skonfigurować w następujący sposób.

3. W następnym wyskakującym okienku Nazwy podzielone w oknie dialogowym wybierz komórkę docelową, aby wyświetlić oddzielone teksty, a następnie kliknij OK. Zobacz poniższe demo.

Przykład #8 Podziel tekst i liczby w komórkach

Przypuśćmy, że istnieje lista ciągów tekstowych z mieszanymi tekstami i liczbami, jak pokazano na zrzucie ekranu poniżej, aby oddzielić tekst od liczb i umieścić je w różnych kolumnach, zapewniamy cztery metody.

Podziel tekst i liczby za pomocą formuł

Za pomocą poniższych formuł możesz podzielić tekst i liczby z jednej komórki na dwie oddzielne komórki.

Formuły ogólne

Pobierz tekst z komórki

=LEFT(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789"))-1)

Pobierz numery z komórki

=RIGHT(cell,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789"))+1)

Następnie zastosuj te dwie formuły w różnych komórkach, aby uzyskać oddzielny tekst i liczby.

1. Wybierz pustą komórkę, aby umieścić tekst pierwszego ciągu tekstowego na liście, skopiuj lub wprowadź poniższą formułę i naciśnij Wchodzę klucz. Wybierz komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać tekst z innych ciągów tekstowych na liście.

=LEFT(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))-1)

2. Wybierz komórkę (E5) obok pierwszej komórki wynikowej (D5), skopiuj lub wprowadź poniższą formułę i naciśnij Wchodzę. Wybierz komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać numery innych ciągów tekstowych na liście.

=RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))+1)

Dzielenie tekstu i liczb za pomocą funkcji Flash Fill (wersja 2013 i nowsze)

Jeśli używasz programu Excel 2013 lub nowszych wersji, możesz zastosować Wypełnienie błyskiem wbudowany do dzielenia tekstu i liczb z jednej komórki na dwie kolumny.

Note: Aby funkcja Flash Fill działała, musisz zlokalizować kolumny wyników obok oryginalnej kolumny ciągu tekstowego. Na przykład, jeśli oryginalne ciągi tekstowe znajdują się w kolumnie B, oddzielony tekst i liczby powinny zostać umieszczone w kolumnie C i D. Zobacz zrzut ekranu:

1. Ręcznie wpisz tekst pierwszej komórki ciągu tekstowego (D5) w komórce C5.

2. Kontynuuj wpisywanie tekstu drugiej komórki ciągu tekstowego (D6) w komórce C6.

3. Aktywuj komórkę C7, kliknij Dane > Wypełnienie błyskiem.

Następnie teksty innych ciągów tekstowych są automatycznie wypełniane w komórkach, jak pokazano na poniższym zrzucie ekranu.

4. Powtórz kroki od 1 do 3, aby uzyskać liczby w kolumnie D.

Uwagi:

Podziel tekst i liczby za pomocą funkcji zdefiniowanej przez użytkownika

Jeśli ciągi tekstowe na liście nie są regularne, powyższe dwie metody mogą zwrócić nieprawidłowe wyniki, jak pokazano na zrzucie ekranu poniżej.

Tutaj wprowadź funkcję zdefiniowaną przez użytkownika, która pomoże Ci podzielić tekst i liczby z komórki na dwie kolumny bez względu na to, gdzie w ciągu tekstowym znajdują się liczby. Zobacz zrzut ekranu:

1. wciśnij inny + F11 klawiatura.

2. W otwarciu Microsoft Visual Basic for Applications okno, kliknij wstawka > Moduł, a następnie skopiuj poniższy VBA do okna Kod.

Kod VBA: Podziel tekst i liczby z komórki na dwie kolumny

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Update by Extendoffice 20211105
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3. wciśnij inny + Q klucze do zamknięcia Microsoft Visual Basic for Applications okno.

4. Wybierz komórkę, aby wyświetlić tekst pierwszego ciągu tekstowego, wprowadź poniższą formułę i naciśnij Wchodzę klucz. Wybierz tę komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby pobrać tekst innych ciągów tekstowych z tej samej listy.

=SplitText(B5,FALSE)

5. Wybierz komórkę obok pierwszej komórki wynikowej tekstu, aby wyprowadzić liczby, wprowadź poniższą formułę i naciśnij Wchodzę klucz. Wybierz tę komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać liczby innych ciągów tekstowych.

=SplitText(B5,TRUE)

Dziel tekst i liczby za pomocą niesamowitego narzędzia

Tutaj zalecamy najprostszy sposób na jednoczesne podzielenie tekstu i liczb z komórki na dwie kolumny. StosowaniePodział komórki cechą Kutools dla programu Excel pomaga uporać się z tym problemem za pomocą kilku kliknięć.

1. Wybierz komórki ciągu tekstowego, w których chcesz podzielić tekst i liczby na dwie kolumny.

2. kliknij Kutools > Połącz i podziel > Podział komórki.

3. w Podział komórki w oknie dialogowym, musisz wykonać następujące ustawienia.

4. W następnym Podział komórki w oknie dialogowym wybierz komórkę, aby wyprowadzić oddzielony tekst i liczby, a następnie kliknij OK przycisk.

Następnie możesz zobaczyć, że tekst i liczby w wybranych komórkach są podzielone na dwie kolumny jednocześnie, jak pokazano na zrzucie ekranu poniżej.

---

2. Podziel komórki liczbowe

W tej części opisano sposób dzielenia komórek liczbowych w dwóch sytuacjach: dzielenie liczby zawierającej więcej niż jedną cyfrę na pojedyncze cyfry oraz dzielenie liczby dziesiętnej w celu oddzielenia części całkowitej i dziesiętnej.

Przykład #1: Podziel komórki według cyfr

Jeśli chcesz podzielić liczbę z więcej niż jedną cyfrą na poszczególne cyfry w różnych kolumnach, wypróbuj jedną z poniższych metod.

Podziel liczbę w komórce na pojedyncze cyfry za pomocą formuły

Poniższy wzór może pomóc w rozbiciu liczby na poszczególne cyfry i umieszczeniu ich w różnych kolumnach.

Formuła ogólna

=MID($A1, COLUMNS($A$1:A$1), 1)

Argument

1. Wybierz pustą komórkę, aby wyprowadzić pierwszą cyfrę, wprowadź poniższą formułę i naciśnij Wchodzę klawisz.

=MID($B3, COLUMNS($B$3:B$3), 1)

2. Wybierz tę komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w prawo do komórek, aby uzyskać inne cyfry. Pozostaw zaznaczone wszystkie komórki wyników, a następnie przeciągnij uchwyt autouzupełniania w dół, aby uzyskać poszczególne cyfry innych liczb.

Note: W tej formule B3 to komórka zawierająca liczbę do podzielenia na poszczególne cyfry, którą można zmienić w razie potrzeby.

Podziel liczbę w komórce na pojedyncze cyfry za pomocą VBA

Poniższy kod VBA może również pomóc w podzieleniu liczby w komórce na pojedyncze cyfry w programie Excel. Możesz zrobić w następujący sposób.

1. wciśnij inny + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.

2. w Microsoft Visual Basic for Applications okno, kliknij wstawka > Moduł, a następnie skopiuj VBA poniżej do okna Kod.

Kod VBA: podziel numer na pojedyncze cyfry w Excelu

Sub SplitNumberIntoDigits()
'Updateby Extendoffice 2021118
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "Kutools for Excel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
 xInt = InputRng.Row

Application.ScreenUpdating = False

For Each Rng In InputRng
    xValue = Rng.Value
    xRow = (Rng.Row - xInt) + 1
    For I = 1 To VBA.Len(xValue)
        OutRng.Cells(xRow, I).Value = VBA.Mid(xValue, I, 1)
    Next
Next
Application.ScreenUpdating = True
End Sub

3. wciśnij F5 klucz do uruchomienia kodu, a następnie a Kutools dla programu Excel pojawi się okno dialogowe, musisz wybrać zakres komórek liczbowych, które chcesz podzielić, a następnie kliknąć OK przycisk.

4. Drugi Kutools dla programu Excel pojawi się okno dialogowe, musisz wybrać komórkę, aby wyświetlić poszczególne cyfry, a następnie kliknąć OK.

Note: Ten kod może również podzielić słowo na pojedyncze litery.

Następnie liczby w wybranych komórkach są rozbijane na poszczególne cyfry i umieszczane w różnych kolumnach.

Łatwo podziel liczbę na pojedyncze cyfry za pomocą niesamowitego narzędzia

Kutools dla programu Excel'S Podział komórki funkcja jest poręcznym narzędziem, które pomoże Ci łatwo podzielić liczbę na pojedyncze cyfry w programie Excel.

1. Po zainstalowaniu Kutools dla programu Excel, wybierz zakres komórek, które chcesz podzielić, kliknij Kutools > Połącz i podziel > Podział komórki.

2. w Podział komórki w oknie dialogowym wykonaj następujące ustawienia.

3. W otwarciu Podział komórki w oknie dialogowym wybierz pustą komórkę, aby wyprowadzić cyfry, a następnie kliknij OK przycisk.

Następnie liczby w wybranych komórkach są dzielone na pojedyncze cyfry jednocześnie.

Przykład #2 Podziel liczbę po przecinku

W tej sekcji omówiono kilka metod dzielenia liczby na części całkowite i dziesiętne w programie Excel.

Podziel liczbę po przecinku za pomocą formuł

Funkcję TRUNC można zastosować do dzielenia liczby w systemie dziesiętnym w programie Excel. Możesz zrobić w następujący sposób.

Formuły ogólne

Uzyskaj część całkowitą

=TRUNC(A1)

Uzyskaj część dziesiętną

=A1-TRUNC(A1)

Argument

Teraz możesz zastosować te dwie formuły, aby podzielić liczby w określonym zakresie komórek w systemie dziesiętnym w programie Excel.

1. Wybierz komórkę, w której chcesz umieścić część całkowitą pierwszej komórki liczbowej, wprowadź poniższą formułę i naciśnij Wchodzę klucz. Wybierz komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać liczby całkowite z innych komórek liczbowych.

=CAŁOŚĆ(B5)

2. Wybierz komórkę obok pierwszej komórki wynikowej, aby umieścić część dziesiętną, wprowadź poniższą formułę i naciśnij Wchodzę klucz. Wybierz tę komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać ułamki dziesiętne innych komórek liczbowych.

=B5-CAŁOŚĆ(B5)

Podziel liczbę po przecinku z tekstem na kolumny

Możesz zastosować Tekst do kolumn funkcja dzielenia liczby po przecinku w programie Excel.

1. Wybierz zakres komórek liczbowych, które podzielisz po przecinku, a następnie kliknij Dane > Tekst do kolumn.

2. w Kreator konwersji tekstu na kolumny - krok 1 z 3 w oknie dialogowym wybierz opcję Rozgraniczone przycisk opcji i kliknij click Następna przycisk.

3. w Krok 2 3 w oknie dialogowym, zaznacz tylko Inne pole wyboru, wprowadź kropkę w polu tekstowym, a następnie kliknij Następna przycisk.

4. w Krok 3 3 okno dialogowe, kliknij przycisk przycisk, aby wybrać komórkę docelową, aby wyprowadzić podzielone liczby całkowite i dziesiętne, a na koniec kliknij koniec przycisk. Zobacz zrzut ekranu:

Następnie możesz zobaczyć, że części całkowite i dziesiętne są podzielone z wybranych komórek, jak pokazano na zrzucie ekranu poniżej. Jednak część dziesiętna traci swój znak ujemny, co może być konieczne dla użytkownika.

Podziel liczbę po przecinku za pomocą funkcji Flash Fill (wersja 2013 i nowsze)

W powyższym przykładzie wprowadziliśmy użycie Wypełnienie błyskiem funkcja do dzielenia tekstu i liczb w komórkach, a tutaj wprowadzimy tę samą metodę dzielenia liczby po przecinku.

1. Podaj kilka przykładów. W tym przypadku wpisujemy część całkowitą B5 w C5, wpisujemy część całkowitą B6 w C6. Zobacz zrzut ekranu:

Note: W przypadku liczb ujemnych nie zapomnij wpisać jednocześnie znaku minus.

3. Wybierz komórki zawierające kilka przykładów w kolumnie, którą chcesz wypełnić, przejdź do kliknięcia Dane > Wypełnienie błyskiem.

Następnie liczby całkowite są wyodrębniane z określonych komórek liczbowych, jak pokazano na poniższym zrzucie ekranu.

4. Powtórz powyższe kroki, aby podzielić ułamki dziesiętne z tych samych komórek liczbowych.

---

3. Podziel komórki daty

Inną często spotykaną sytuacją jest dzielenie komórek daty. Jeśli musisz podzielić komórki daty na oddzielny dzień, miesiąc i rok lub podzielić komórki daty na osobne daty i godziny, użyj następującego rozwiązania w zależności od potrzeb.

Przykład #1 Podziel komórki daty na oddzielny dzień, miesiąc i rok

Zakładając, że masz listę dat z zakresu B5:B9 i chcesz podzielić każdą wartość daty na trzy oddzielne kolumny dla dnia, miesiąca i roku. Oto trzy metody, które pomogą Ci osiągnąć wynik podziału dat.

Podziel komórki daty na dzień, miesiąc i rok za pomocą formuł

Możesz zastosować trzy formuły na podstawie DATA funkcjonować, MIESIĄC funkcja i ROK funkcja podziału daty na oddzielny dzień, miesiąc i rok w programie Excel.

Formuły ogólne

=DATE(A1)

=MONTH(A1)

=YEAR(A1)

Argument

1. Utwórz trzy kolumny, aby umieścić osobny dzień, miesiąc i rok.

2. Wybierz pierwszą komórkę w kolumnie Dzień, wprowadź poniższą formułę i naciśnij Wchodzę klucz, aby uzyskać dzień pierwszej komórki daty (B5). Wybierz tę komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać dni z innych komórek daty.

=DAY(B5)

3. Wykonaj tę samą operację, co w kroku 1, aby zastosować następujące formuły w Miesiąc i Rok kolumna, aby uzyskać oddzielny miesiąc i rok z komórek daty.

Pobierz miesiąc daty

=MONTH(B5)

Zdobądź rok randki

=YEAR(B5)

Podziel komórki daty na dzień, miesiąc i rok za pomocą tekstu do kolumn

Możesz wykonaj powyższe kroki, aby zastosować funkcję Tekst do kolumn podzielić komórki daty na oddzielny dzień, miesiąc i rok w programie Excel.

Note: W Krok 2 3 w oknie dialogowym, zaznacz tylko Inne pole i wpisz a / symbol w polu tekstowym.

Podziel komórki daty na dzień, miesiąc i rok za pomocą niesamowitego narzędzia

Poniższe demo pokazuje, jak podzielić datę na oddzielny miesiąc, dzień i rok za pomocą Podział komórki cechą Kutools dla programu Excel.

Ta funkcja pomaga osiągnąć wynik zbiorczego podziału dat za pomocą zaledwie kilku kliknięć.

Kliknij, aby dowiedzieć się więcej o tej funkcji.

Przykład #2 Podziel datę i godzinę w komórce

Przypuśćmy, że w B5:B9 znajduje się lista dat i godzin, a chcesz podzielić daty i godziny na oddzielne kolumny, ta sekcja pokaże różne sposoby, które pomogą Ci to zrobić.

Podziel datę i godzinę w komórce za pomocą formuły

Możesz zastosować następujące formuły, aby podzielić datę i godzinę na różne kolumny w programie Excel.

1. Przygotuj dwie kolumny do umieszczenia dat i godzin.

2. Musisz ustawić komórki kolumny daty na Data format i ustaw komórki kolumny czasu na Czas Format.

3. Wybierz pierwszą komórkę w Data kolumnę, wprowadź formułę poniżej i naciśnij Wchodzę klucz do uzyskania daty B5. Wybierz tę komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać inne daty.

=INT(B5)

4. Zastosuj następującą formułę w kolumnie czasu, aby uzyskać czasy w B5:B9.

=B5-D5

Podziel datę i godzinę w komórce za pomocą funkcji Flash Fill (wersja 2013 i nowsze)

Jeśli korzystasz z programu Excel 2013 i nowszych wersji, możesz zastosować wbudowaną funkcję Flash Fill, aby podzielić datę i godzinę na różne kolumny.

1. Utwórz kolumny Data i Czas i wprowadź kilka przykładów, które chcesz uzyskać. w Data kolumnę, wpisujemy datę B5 w C5, a datę B6 w C6. w Czas kolumnę wpisujemy czas B5 w D5 i czas B6 w D6. Zobacz zrzut ekranu:

2. Wybierz kolumnę Data (w tym kilka przykładów), którą chcesz wypełnić, przejdź do kliknięcia Dane > Wypełnienie błyskiem.

3. Wybierz kolumnę Czas (w tym kilka przykładów), którą chcesz wypełnić, a następnie włącz Wypełnienie błyskiem funkcja, jak również. Następnie daty i godziny w B5:B9 są dzielone na osobne kolumny, jak pokazano na poniższym demo.

Teraz nauczyłeś się dzielić komórki w Excelu różnymi metodami w różnych sytuacjach. Będziesz mógł podjąć najlepszą decyzję, aby określić, którą metodę wybierzesz, sprowadzając się do konkretnego scenariusza.