Samouczek Excela – wyodrębnij tekst lub liczbę według określonej pozycji
W wielu przypadkach może być konieczne tylko wyodrębnienie przydatnej treści ze zdania lub ciągu tekstowego w komórce, np. wyodrębnienie prowincji z adresu, wyodrębnienie adresu e-mail ze zdania, wyodrębnienie numeru rachunku kurierskiego z rozmowy, i tak dalej. Ten samouczek zawęża wyodrębnianie do określonej pozycji w komórce i zbiera różne metody, aby pomóc wyodrębnić tekst lub liczby z komórki według określonej pozycji w programie Excel.
Spis treści: [ Ukrywać ]
Wyodrębnij tekst według pozycji
W tej sekcji zebrano wspólne stanowiska, w których tekst można wyodrębnić z komórki, i przedstawiono odpowiednie metody ich obsługi krok po kroku. Możesz przeglądać więcej szczegółów.
1. Wyodrębnij liczbę znaków z lewej lub prawej strony
Aby wyodrębnić liczbę znaków z lewej lub prawej strony ciągu, możesz wypróbować jedną z poniższych metod.
1.1 Wyodrębnij pierwsze lub ostatnie N znaków za pomocą formuł
Przypuśćmy, że masz listę ciągów tekstowych w kolumnie B, jak pokazano na poniższym zrzucie ekranu, aby wyodrębnić pierwsze 2 znaki i ostatnie 2 znaki z każdego ciągu, możesz zastosować następujące formuły.
Wyodrębnij pierwsze N znaków z ciągu tekstowego
Funkcja LEWO może pomóc w łatwym wyodrębnieniu pierwszych N znaków z ciągu tekstowego w programie Excel.
Formuła ogólna
=LEFT(text_string,[num_chars])
Argumenty
Teraz możesz zastosować tę formułę, aby wyodrębnić pierwsze 2 znaki z komórek w kolumnie B.
1. Wybierz pustą komórkę, skopiuj lub wprowadź poniższą formułę i naciśnij Wchodzę klucz, aby uzyskać pierwszy wynik. Wybierz komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby zastosować formułę do innych komórek.
=LEFT(B5,2)
Teraz wyodrębniono pierwsze 2 znaki w każdej komórce z zakresu B5:B10.
Wyodrębnij ostatnie N znaków z ciągu tekstowego
Tutaj stosujemy PRAWĄ funkcję, aby wyodrębnić ostatnie N znaków z ciągu tekstowego w programie Excel.
Formuła ogólna
=RIGHT(text_string,[num_chars])
Argumenty
Wybierz pustą komórkę, skopiuj lub wprowadź poniższą formułę i naciśnij Wchodzę klucz, aby uzyskać wynik. Następnie wybierz tę komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać inne wyniki.
=RIGHT(B5,2)
1.2 Wyodrębnij pierwsze lub ostatnie N znaków za pomocą niesamowitego narzędzia
Chociaż powyższe formuły są proste, aby wyodrębnić pierwsze lub ostatnie n znaków z długiej listy ciągu tekstowego, nadal musisz przeciągnąć uchwyt autouzupełniania od góry do dołu, co może być trochę czasochłonne. Tutaj poleca Kutools dla programu Excel'S Wyodrębnij tekst narzędzie ułatwiające zbiorcze wyodrębnianie pierwszych lub ostatnich N znaków z listy ciągów tekstowych.
1. Wybierz wcześniej listę ciągów tekstowych, z której chcesz wyodrębnić tekst, i kliknij Kutools > Tekst > Wyodrębnij tekst.
2. W wyskakującym okienku Wyodrębnij tekst W oknie dialogowym należy skonfigurować w następujący sposób.
Note: Aby wynik był dynamiczny po zmianie ciągu tekstowego, możesz zaznaczyć Wstaw jako wzór pudełko.
3. W następnym wyskakującym okienku Wyodrębnij tekst w oknie dialogowym wybierz komórkę do wyświetlenia wyodrębnionych znaków, a następnie kliknij OK.
Następnie określone pierwsze lub ostatnie N znaków jest wyodrębnianych zbiorczo z wybranych komórek.
Kliknij, aby dowiedzieć się więcej o tej funkcji.
Jeśli chcesz mieć bezpłatną wersję próbną (30 dni) tego narzędzia, kliknij, aby go pobrać, a następnie przejdź do wykonania operacji zgodnie z powyższymi krokami.
2. Wyodrębnij tekst przed lub po określonym znaku/słowie
Aby wyodrębnić tekst przed lub po określonym znaku lub słowie, różne scenariusze w tej sekcji spełnią Twoje potrzeby.
2.1 Wyodrębnij tekst przed lub po pierwszym ograniczniku (znaku)
Jak pokazano na poniższym zrzucie ekranu, aby wyodrębnić tekst przed lub po pierwszym ograniczniku z każdej komórki w zakresie B4:B10, możesz zastosować jedną z poniższych metod.
2.1.1 Wyodrębnij tekst przed pierwszym ogranicznikiem za pomocą formuły
Zastosowanie formuły opartej na funkcjach LEWO i ZNAJDŹ może pomóc w wyodrębnieniu tekstu przed pierwszym ogranicznikiem z komórki. Możesz wykonać poniższe czynności, aby to zrobić.
Formuła ogólna
=LEFT(text_string,FIND("delimiter",text_string,1)-1)
Argumenty
Wybierz pustą komórkę, skopiuj lub wprowadź do niej poniższą formułę i naciśnij Wchodzę klucz, aby uzyskać pierwszy wynik. Wybierz pierwszą komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać teksty przed pierwszym ogranicznikiem innych komórek.
=LEFT(B5,FIND("-",B5,1)-1)
2.1.2 Wyodrębnij tekst po pierwszym ograniczniku za pomocą formuły
Poniższa formuła pomaga wyodrębnić tekst po pierwszym ograniczniku z komórki w programie Excel.
Formuła ogólna
=MID(text_string,FIND("delimiter",text_string)+1,LEN(text_string))
Argumenty
=MID(B5,FIND("-",B5)+1,LEN(B5))
2.1.3 Wyodrębnij tekst przed lub po pierwszym ograniczniku za pomocą niesamowitego narzędzia
Tutaj bardzo polecamy Wyodrębnij tekst użyteczność Kutools dla programu Excel. Dzięki tej funkcji możesz łatwo wyodrębnić teksty przed lub po pierwszym ograniczniku z zakresu komórek zbiorczo.
1. Wybierz zakres komórek, w których chcesz wyodrębnić tekst, a następnie kliknij Kutools > Tekst > Wyodrębnij tekst.
2. w Wyodrębnij tekst W oknie dialogowym należy skonfigurować w następujący sposób.
Uwaga: Aby wynik był dynamiczny po zmianie ciągu tekstowego, możesz zaznaczyć pole Wstaw jako formułę.
3. Potem kolejny Wyodrębnij tekst pojawi się okno dialogowe, wybierz komórkę do wyświetlenia wyników i kliknij OK.
Następnie teksty przed lub po pierwszym ograniczniku są jednocześnie wyodrębniane z wybranych komórek.
Aby dowiedzieć się więcej o tej funkcji, odwiedź: Szybko wyodrębnij określony tekst z komórek w programie Excel.
Jeśli chcesz mieć bezpłatną wersję próbną (30 dni) tego narzędzia, kliknij, aby go pobrać, a następnie przejdź do wykonania operacji zgodnie z powyższymi krokami.
2.2 Wyodrębnij tekst przed lub po ostatnim ograniczniku (znaku)
W powyższych krokach poznaliśmy metody wyodrębniania tekstu przed lub po pierwszym ograniczniku z komórki. Jak pokazano na poniższym zrzucie ekranu, w tej sekcji zostaną wyświetlone dwie formuły do wyodrębnienia tekstu przed lub po ostatnim ograniczniku z komórki. Możesz wykonać poniższe czynności, aby to zrobić.
2.2.1 Wyodrębnij tekst przed ostatnim ogranicznikiem za pomocą formuły
Aby wyodrębnić z komórki tekst przed ostatnim ogranicznikiem, możesz użyć funkcji SEARCH, LEN i SUBSTITUTE wewnątrz funkcji LEWO.
Formuła ogólna
=LEFT(text_string,SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",""))))-1)
Argumenty
Wybierz komórkę, wprowadź formułę poniżej i naciśnij Wchodzę klucz, aby uzyskać wynik. Wybierz tę komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby wyodrębnić teksty z innych ciągów tekstowych w tej samej kolumnie.
=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)
2.2.2 Wyodrębnij tekst po ostatnim ograniczniku za pomocą formuły
Po wyodrębnieniu tekstu przed ostatnim ogranicznikiem z komórki możesz zastosować poniższą formułę, aby wyodrębnić tekst po ostatnim ograniczniku, zgodnie z potrzebami.
Formuła ogólna
=RIGHT(text_string,LEN(text_string)-SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter","")))))
Argumenty
Wybierz komórkę, wprowadź formułę poniżej i naciśnij Wchodzę klucz, aby uzyskać wynik. Wybierz tę komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby wyodrębnić teksty z innych ciągów tekstowych w tej samej kolumnie.
=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))
2.3 Wyodrębnij tekst po n-tym znaku
Spójrz na poniższy przykład, istnieje lista ciągów tekstowych w zakresie B4:B10, aby wyodrębnić tekst po trzecim znaku z każdej komórki, możesz zastosować formułę opartą na funkcji MID i funkcji LEN.
Formuła ogólna
=MID(text_string,nth_char+1,LEN(text_string))
Argumenty
Wybierz pustą komórkę, skopiuj lub wprowadź do niej poniższą formułę i naciśnij Wchodzę klucz, aby uzyskać wynik. Wybierz tę komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać inne wyniki.
=MID(B5,3+1,LEN(B5))
2.4 Wyodrębnij n-te słowo z ciągu tekstowego
Przypuśćmy, że masz listę ciągów tekstowych, jak pokazano na zrzucie ekranu poniżej, i chcesz wyodrębnić tylko n-te słowo z ciągu tekstowego, ta sekcja zawiera trzy metody, aby to zrobić.
2.4.1 Wyodrębnij n-te słowo za pomocą formuły
Możesz połączyć funkcje TRIM, MID, SUBSTITUTE, REPT i LEN, aby wyodrębnić n-te słowo z ciągu tekstowego w komórce.
Formuła ogólna
=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))
Argumenty
W tym przypadku zakres B5:B10 zawiera ciągi tekstowe, D5:D10 zawiera liczby reprezentujące n-te słowo, zastosujmy tę formułę, aby wyodrębnić n-te słowo z ciągu tekstowego.
Wybierz pustą komórkę, skopiuj lub wprowadź do niej poniższą formułę i naciśnij Wchodzę klucz, aby uzyskać pierwszy wynik. Wybierz tę komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać n-te słowo z innych komórek.
=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))
Uwaga: Możesz bezpośrednio wpisać n-tą liczbę w formule w następujący sposób.
=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))
2.4.2 Wyodrębnij n-te słowo za pomocą funkcji zdefiniowanej przez użytkownika
Oprócz powyższej formuły możesz również zastosować funkcję zdefiniowaną przez użytkownika, aby wyodrębnić n-te słowo z komórki 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ł, a następnie skopiuj VBA poniżej do okna Kod.
Kod VBA: Wyodrębnij n-te słowo z ciągu tekstowego w komórce
Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
FindWord = ""
Else
FindWord = arr(Position - 1)
End If
End Function
3. wciśnij inny + Q klucze do zamknięcia Microsoft Visual Basic for Applications okno.
4. Wróć do arkusza roboczego zawierającego ciągi tekstowe, z których chcesz wyodrębnić n-te słowo. Wybierz pustą komórkę, skopiuj lub wprowadź do niej poniższą formułę i naciśnij Wchodzę klucz, aby uzyskać n-te słowo.
=FindWord(B5,D5)
Or
=FindWord(B5,2)
Uwaga: we wzorze D5 jest komórką zawierającą liczbę reprezentującą n-te słowo. Alternatywnie możesz bezpośrednio zastąpić odwołanie do komórki liczbą.
5. Wybierz komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby wyodrębnić n-te słowo z ciągów tekstowych innych komórek.
2.4.3 Wyodrębnij n-te słowo za pomocą niesamowitego narzędzia
Jeśli nie chcesz ręcznie stosować formuły lub funkcji zdefiniowanej przez użytkownika podanej powyżej, tutaj zaleca Kutools dla programu Excel Wyodrębnij n-te słowo z komórki pożytek. Dzięki tej funkcji możesz łatwo wyodrębnić n-te słowo z ciągu tekstowego w komórce za pomocą kilku kliknięć.
1. Wybierz komórkę do umieszczenia wyniku i kliknij Kutools > Pomocnik formuły > Tekst > Wyciąg n-te słowo w komórce. Zobacz zrzut ekranu:
2. w Pomocnik formuł W oknie dialogowym należy skonfigurować w następujący sposób.
3. Następnie n-te (drugie) słowo jest wyodrębniane z ciągu tekstowego w komórce B5 i można zobaczyć, że formuła jest tworzona w tym samym czasie. Wybierz tę komórkę wynikową i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać n-te słowo z innych ciągów tekstowych.
Kliknij, aby dowiedzieć się więcej o tej funkcji.
Jeśli chcesz mieć bezpłatną wersję próbną (30 dni) tego narzędzia, kliknij, aby go pobrać, a następnie przejdź do wykonania operacji zgodnie z powyższymi krokami.
2.5 Wyodrębnij tekst przed lub po n-tym wystąpieniu ogranicznika
Przypuśćmy, że masz listę ciągów tekstowych, jak pokazano na zrzucie ekranu poniżej. Aby wyodrębnić tekst przed lub po drugim wystąpieniu spacji, ta sekcja zawiera dwie formuły, które pomogą Ci to zrobić.
2.5.1 Wyodrębnij tekst przed n-tym wystąpieniem ogranicznika
Możesz użyć funkcji LEWO wraz z funkcjami SUBSTITUTE i ZNAJDŹ, aby wyodrębnić tekst przed n-tym wystąpieniem ogranicznika z komórki w programie Excel.
Formuła ogólna
=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)
Argumenty
Wybierz komórkę, skopiuj lub wprowadź poniższą formułę i naciśnij Wchodzę klucz, aby uzyskać wynik. Wybierz tę komórkę wynikową i przeciągnij ją w dół, aby uzyskać inne wyniki na liście.
=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)
Uwaga: W formule B5 to komórka zawierająca ciąg tekstowy, z którego chcesz wyodrębnić tekst; „ ” reprezentuje tutaj spację, a liczba 2 reprezentuje drugie wystąpienie spacji. Możesz je zmieniać według własnych potrzeb.
2.5.2 Wyodrębnij tekst po n-tym wystąpieniu ogranicznika
Aby wyodrębnić tekst po n-tym wystąpieniu ogranicznika, możesz zastosować funkcję PRAWO z funkcjami SUBSTITUTE, LEN i ZNAJDŹ.
Formuła ogólna
=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n), 1) + 1)
Argumenty
Teraz możesz zastosować tę formułę, aby wyodrębnić tekst po drugim wystąpieniu spacji z każdej komórki w zakresie B5:B10 w następujący sposób.
Wybierz komórkę, wprowadź formułę poniżej i naciśnij Wchodzę klucz, aby uzyskać wynik. Wybierz tę komórkę wynikową i przeciągnij ją w dół, aby uzyskać inne wyniki.
=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)
2.6 Wyodrębnij tekst przed lub po łamaniu wiersza
Załóżmy, że masz listę zamówień w kolumnie B i chcesz wyodrębnić tylko część daty i część numeru produktu z każdej komórki. Aby to zrobić, możesz użyć poniższych formuł programu Excel.
2.6.1 Wyodrębnij tekst przed pierwszym podziałem wiersza za pomocą formuły
Jak widać na powyższym zrzucie ekranu, część daty znajduje się przed pierwszym podziałem wiersza w komórce. W tej sekcji zademonstrujemy funkcję LEFT wraz z funkcją SEARCH, która pomoże Ci wyodrębnić tekst przed podziałem pierwszego wiersza w komórce.
Formuła ogólna
=LEFT(cell, SEARCH(CHAR(10), cell)-1)
Argumenty
Wybierz pustą komórkę, skopiuj lub wprowadź do niej poniższą formułę i naciśnij Wchodzę klucz, aby uzyskać wynik. Wybierz tę komórkę wynikową, a następnie przeciągnij jej uchwyt autouzupełniania w dół, aby zastosować tę formułę do innych komórek.
=LEFT(B5, SEARCH(CHAR(10), B5)-1)
Następnie możesz zobaczyć tekst przed wyodrębnieniem pierwszego podziału wiersza w każdej komórce z zakresu B5: B8, jak pokazano na zrzucie ekranu poniżej.
Uwaga: W formule CHAR(10) reprezentuje łamanie wiersza w systemie Windows.
2.6.2 Wyodrębnij tekst po ostatnim złamaniu wiersza za pomocą formuły
W poprzednim kroku rozmawialiśmy o tym, jak wyodrębnić tekst przed pierwszym podziałem wiersza w komórce. W tej części dowiesz się, jak wyodrębnić tekst po ostatnim podziale wiersza w komórce za pomocą innej formuły.
Formuła ogólna
=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(" ",200)),200))
Argumenty
Wybierz pustą komórkę, wprowadź formułę poniżej i naciśnij Wchodzę klucz, aby uzyskać wynik. Wybierz tę komórkę wynikową, a następnie przeciągnij jej uchwyt autouzupełniania w dół, aby zastosować formułę do innych komórek.
=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))
Następnie produkt nr. część każdej komórki na liście jest wyodrębniana, jak pokazano na powyższym zrzucie ekranu.
Uwaga: W formule CHAR(10) reprezentuje łamanie wiersza w systemie Windows.
2.7 Wyodrębnij tekst przed lub po słowie
W poprzednich częściach nauczyliśmy się wyodrębniać tekst przed lub po znaku lub ograniczniku. Co należy zrobić, aby wyodrębnić tekst przed lub po całym słowie? W tej sekcji przedstawimy trzy metody, które pomogą Ci wykonać to zadanie.
2.7.1 Wyodrębnij tekst przed określonym słowem za pomocą formuły
Poniższa formuła pomaga wyodrębnić tekst przed określonym słowem w komórce w programie Excel.
Formuła ogólna
=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)
Argumenty
Wybierz pustą komórkę, wprowadź formułę poniżej i naciśnij Wchodzę klucz, aby uzyskać wynik. Wybierz tę komórkę wynikową, a następnie przeciągnij jej uchwyt autouzupełniania w dół, aby zastosować tę formułę do innych komórek.
W tym przykładzie wyodrębnimy wszystkie teksty przed słowem „Excel”, więc bezpośrednio wpisujemy słowo w formule i ujmujemy je w podwójnych cudzysłowach. Możesz też odwołać się do komórki zawierającej słowo „Excel”.
=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)
Uwagi:
2.7.2 Wyodrębnij tekst po określonym słowie za pomocą formuły
Aby wyodrębnić tekst po określonym słowie, możesz zastosować następującą formułę, aby to zrobić.
Formuła ogólna
=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))
Argumenty
Wybierz komórkę, wprowadź formułę poniżej i naciśnij Wchodzę klucz, aby uzyskać wynik. Zaznacz komórkę wynikową, a następnie przeciągnij jej uchwyt autouzupełniania w dół, aby zastosować tę formułę do innych komórek.
=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))
Następnie możesz zobaczyć wszystkie teksty po wyodrębnieniu słowa „Excel” w każdej komórce, jak pokazano na zrzucie ekranu poniżej.
Uwagi:
2.7.3 Wyodrębnij tekst przed lub po określonym słowie za pomocą niesamowitego narzędzia
Jeśli uważasz, że stosowanie formuły może powodować wiele niedogodności, tutaj gorąco polecamy Wyodrębnij tekst użyteczność Kutools dla programu Excel. Ta funkcja pomaga zautomatyzować zadanie wyodrębniania w programie Excel za pomocą zaledwie kilku kliknięć.
1. kliknij Kutools > Tekst > Wyodrębnij tekst aby włączyć tę funkcję.
2. w Wyodrębnij tekst w oknie dialogowym wykonaj następujące ustawienia.
Uwagi: Jeśli chcesz tworzyć dynamiczne wyniki, sprawdź Wstaw jako wzór skrzynka. Wtedy wyniki zostaną automatycznie zaktualizowane, gdy zmienią się dane w zakresie.
3. Następnie plik Wyodrębnij tekst pojawi się okno dialogowe, musisz wybrać komórkę, aby wyświetlić wynik, a następnie kliknąć OK przycisk.
Następnie teksty przed lub po określonym słowie w każdej komórce w wybranym zakresie są natychmiast wyodrębniane.
Uwaga: Ta funkcja rozróżnia wielkość liter.
Kliknij, aby dowiedzieć się więcej o tej funkcji.
Jeśli chcesz mieć bezpłatną wersję próbną (30 dni) tego narzędzia, kliknij, aby go pobrać, a następnie przejdź do wykonania operacji zgodnie z powyższymi krokami.
3. Wyodrębnij między znakami/słowami
Jeśli chcesz wyodrębnić tekst między określonymi znakami lub słowami, wypróbuj następujące metody.
3.1 Wyodrębnij tekst między dwoma znakami
Aby wyodrębnić tekst między dwoma znakami, które mogą być tymi samymi lub różnymi znakami. W tej sekcji przedstawiono kilka metod i możesz wybrać jedną z nich zgodnie z własnymi potrzebami.
3.1.1 Wyodrębnij tekst między dwoma tymi samymi znakami za pomocą formuły
Jak pokazano na poniższym zrzucie ekranu, w kolumnie B znajduje się lista ciągów tekstowych i chcesz wyodrębnić część liczbową między znakami „/” z każdej komórki w zakresie, poniższa formuła może ci wyświadczyć przysługę.
Wybierz pustą komórkę, skopiuj lub wprowadź poniższą formułę i naciśnij Wchodzę klucz, aby uzyskać wynik. Wybierz komórkę wynikową, a następnie przeciągnij ją w dół, aby uzyskać wyniki z innych komórek na liście.
=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")
Następnie tekst między dwoma tymi samymi znakami „/” jest wyodrębniany z każdej komórki w zakresie. Zobacz zrzut ekranu:
Uwaga:
3.1.2 Wyodrębnij tekst między dwoma różnymi znakami za pomocą formuły
Po nauczeniu się, jak wyodrębniać tekst między dwoma tymi samymi znakami w komórce, tutaj zademonstrujemy formułę wyodrębniania tekstu między dwoma różnymi znakami. Jak pokazano na poniższym zrzucie ekranu, aby wyodrębnić tylko adres e-mail między „<” i „>” z każdej komórki w kolumnie B, możesz wykonać następujące czynności.
Formuła ogólna
=MID(LEFT(cel,FIND("end_char",cell)-1),FIND("start_char",cell)+1,LEN(cell))
Argumenty
Wybierz pustą komórkę, skopiuj lub wprowadź poniższą formułę i naciśnij Wchodzę klucz, aby uzyskać wynik. Wybierz tę komórkę wynikową, a następnie przeciągnij jej uchwyt autouzupełniania w dół, aby zastosować tę formułę do innych komórek.
=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))
Widać, że wyodrębniany jest tylko tekst między określonymi znakami, jak pokazano na powyższym zrzucie ekranu.
3.1.3 Wyodrębnij tekst między dwiema postaciami za pomocą niesamowitego narzędzia
Tutaj bardzo polecamy Wyodrębnij ciągi między określonym tekstem cechą Kutools dla programu Excel aby pomóc Ci łatwo wyodrębnić tekst między dwoma takimi samymi lub różnymi znakami w komórce w programie Excel.
1. Wybierz pustą komórkę, aby wyprowadzić wynik, a następnie kliknij Kutools > Pomocnik formuły > Pomocnik formuły.
2. w Pomocnik formuł w oknie dialogowym wykonaj następujące ustawienia.
3. Następnie wyodrębniany jest tylko tekst między „<” i „>” w komórce B5. Tymczasem utworzono formułę, możesz wybrać tę komórkę wynikową, a następnie przeciągnąć jej uchwyt autouzupełniania w dół, aby wyodrębnić teksty z innych komórek na tej samej liście.
Kliknij, aby dowiedzieć się więcej o tej funkcji.
Jeśli chcesz mieć bezpłatną wersję próbną (30 dni) tego narzędzia, kliknij, aby go pobrać, a następnie przejdź do wykonania operacji zgodnie z powyższymi krokami.
3.1.4 Wyodrębnij tekst między dwoma znakami (w tym znakami) według reguły
Jeśli chcesz zachować dwa znaki po wyodrębnieniu, spróbuj zastosować regułę w funkcji Wyodrębnij tekst Kutools for Excel.
1. kliknij Kutools > Tekst > Wyodrębnij tekst.
2. w Wyodrębnij tekst w oknie dialogowym wykonaj następujące ustawienia.
3. Inny Wyodrębnij tekst pojawi się okno dialogowe, wybierz komórkę do wyświetlenia wyniku, a następnie kliknij OK przycisk.
Następnie tekst między określonymi znakami (w tym znaki) jest wyodrębniany zbiorczo z każdej komórki w wybranym zakresie.
Jeśli chcesz mieć bezpłatną wersję próbną (30 dni) tego narzędzia, kliknij, aby go pobrać, a następnie przejdź do wykonania operacji zgodnie z powyższymi krokami.
3.2 Wyodrębnij tekst między dwoma słowami
Oprócz wyodrębniania tekstu między dwoma znakami może być również konieczne wyodrębnienie tekstu między dwoma słowami. Na przykład wyodrębnij wszystkie ciągi tekstowe między dwoma słowami „KTE” i „feature” z każdej komórki w kolumnie B, jak pokazano na zrzucie ekranu poniżej. Możesz wypróbować jedną z następujących metod, aby to zrobić.
3.2.1 Wyodrębnij tekst między dwoma słowami za pomocą formuły
Możesz użyć formuły opartej na funkcji MID i funkcji SZUKAJ, aby wyodrębnić wszystkie ciągi tekstowe między dwoma słowami w komórce.
Formuła ogólna
=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)
Argumenty
Wybierz pustą komórkę, skopiuj lub wprowadź poniższą formułę i naciśnij Wchodzę klucz, aby uzyskać wynik. Wybierz tę komórkę wynikową, a następnie przeciągnij jej uchwyt autouzupełniania w dół, aby zastosować tę formułę do innych komórek.
=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)
Note: We wzorze liczba 3 reprezentuje długość znaku słowa „KTE”; liczba 4 reprezentuje długość znaku słowa „KTE” plus 1.
Możesz zobaczyć, że wszystkie ciągi tekstowe między określonymi dwoma słowami są wyodrębniane z każdej komórki w kolumnie B.
3.2.2 Wyodrębnij tekst między dwoma słowami za pomocą niesamowitego narzędzia
Dla wielu użytkowników Excela formuły mogą być trudne do zapamiętania i obsługi. Tutaj, z Wyodrębnij ciągi między określonym tekstem cechą Kutools dla programu Excel, możesz łatwo wyodrębnić tekst między dwoma słowami za pomocą kilku kliknięć.
1. Wybierz komórkę do wyświetlenia wyniku, a następnie kliknij Kutools > Pomocnik formuły > Pomocnik formuły.
2. w Pomocnik formuły W oknie dialogowym należy skonfigurować w następujący sposób.
3. Następnie wyodrębniane są wszystkie ciągi tekstowe między dwoma słowami „KTE” i „funkcja” w komórce B5. Tymczasem utworzono formułę, możesz wybrać tę komórkę wynikową, a następnie przeciągnąć jej uchwyt autouzupełniania w dół, aby wyodrębnić teksty z innych komórek na tej samej liście.
Jeśli chcesz mieć bezpłatną wersję próbną (30 dni) tego narzędzia, kliknij, aby go pobrać, a następnie przejdź do wykonania operacji zgodnie z powyższymi krokami.
Wyodrębnij liczby według pozycji
W przypadku listy ciągów alfanumerycznych mogą występować trzy przypadki:
- Numer znajduje się na początku tekstu;
- Numer znajduje się na końcu tekstu;
- Numer może znajdować się w dowolnym miejscu w tekście.
W tej sekcji przedstawimy różne metody, których można użyć do wyodrębnienia liczb w każdym przypadku, o którym mowa powyżej.
1 Wyodrębnij numer z lewej strony ciągu
Ta część wprowadzi formułę, która pomoże Ci wyodrębnić tylko liczby, które pojawiają się przed tekstem w komórce.
Formuła ogólna
=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)
Argumenty
Uwaga:
Wybierz pustą komórkę, wprowadź formułę poniżej i naciśnij Ctrl + Shift + Wchodzę or Wchodzę klucz, aby uzyskać wynik. Wybierz tę komórkę wynikową, a następnie przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać liczbę innych komórek.
=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)
Uwagi:
2 Wyodrębnij numer z prawej strony ciągu
Jak pokazano na poniższym zrzucie ekranu, aby wyodrębnić tylko liczby pojawiające się po tekście w komórce, wypróbuj następującą formułę.
Formuła ogólna
=RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))
Argumenty
Uwaga:
Wybierz pustą komórkę, wprowadź formułę poniżej i naciśnij Ctrl + Shift + Wchodzę or Wchodzę klucz, aby uzyskać wynik. Wybierz tę komórkę wynikową, a następnie przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać liczbę innych komórek.
=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))
Uwagi:
3. Wyodrębnij wszystkie liczby z dowolnej pozycji w ciągu tekstowym
Powyższe metody pomagają wyodrębnić liczbę tylko z lewej lub prawej strony ciągu tekstowego. Jeśli chcesz wyodrębnić wszystkie liczby z dowolnego miejsca w ciągu tekstowym, tutaj udostępniamy trzy metody, aby to zrobić.
3.1 Wyodrębnij wszystkie liczby z dowolnego miejsca w ciągu za pomocą formuły
Możesz zastosować następującą formułę, aby wyodrębnić wszystkie liczby z dowolnego miejsca w ciągu tekstowym w programie Excel.
1. Wybierz pustą komórkę, skopiuj lub wprowadź poniższą formułę i naciśnij Wchodzę klucz, aby uzyskać wszystkie liczby z komórki B5.
=SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1)) * ROW(INDIRECT("1:"&LEN(B5))), 0), ROW(INDIRECT("1:"&LEN(B5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B5)))/10)
2. Wybierz komórkę wynikową, a następnie przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać wszystkie liczby innych komórek.
3.2 Wyodrębnij wszystkie liczby z dowolnego miejsca w ciągu za pomocą VBA
Powyższa formuła jest zbyt długa i skomplikowana dla wielu użytkowników Excela. W rzeczywistości możesz uruchomić skrypt VBA, aby zautomatyzować zadanie 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 otwarciu Microsoft Visual Basic for Applications okno, kliknij wstawka > Moduł. Następnie skopiuj poniższy VBA do okna kodu modułu.
Kod VBA: Wyodrębnij wszystkie liczby z dowolnego miejsca w ciągu tekstowym
Sub ExtrNumbersFromRange()
'Updated by Extendoffice 20220106
Dim xRg As Range
Dim xDRg As Range
Dim xRRg As Range
Dim nCellLength As Integer
Dim xNumber As Integer
Dim strNumber As String
Dim xTitleId As String
Dim xI As Integer
xTitleId = "KutoolsforExcel"
Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
If TypeName(xDRg) = "Nothing" Then Exit Sub
Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
If TypeName(xRRg) = "Nothing" Then Exit Sub
xI = 0
strNumber = ""
For Each xRg In xDRg
xI = xI + 1
nCellLength = Len(xRg)
For xNumber = 1 To nCellLength
If IsNumeric(Mid(xRg, xNumber, 1)) Then
strNumber = strNumber & Mid(xRg, xNumber, 1)
End If
Next xNumber
xRRg.Item(xI) = strNumber
strNumber = ""
Next xRg
End Sub
3. wciśnij F5 klucz do uruchomienia kodu. W otwarciu Kutools dla programu Excel w oknie dialogowym wybierz zakres komórek, z których chcesz wyodrębnić wszystkie liczby z każdej komórki, a następnie kliknij OK przycisk.
4. Potem kolejny Kutools dla programu Excel pojawi się okno dialogowe. W tym oknie dialogowym wybierz komórkę docelową i kliknij OK.
Następnie wszystkie liczby są pobierane zbiorczo z każdej komórki w wybranym zakresie.
4. Wyodrębnij liczby po określonym tekście
Jak pokazano na poniższym zrzucie ekranu, aby wyodrębnić liczby po określonym tekście „Nie.”, w tej sekcji przedstawiono dwie metody, które pomogą Ci to zrobić.
4.1 Wyodrębnij liczby po określonym tekście za pomocą wzoru
Możesz zastosować następującą formułę, aby wyodrębnić liczby po określonym tekście w komórce w programie Excel.
Wzór ogólny:
=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789",FIND("text"," "&cell&" "))),{2,3,4,5,6}))
Argumenty
Wybierz pustą komórkę, skopiuj lub wprowadź poniższą formułę i naciśnij Wchodzę klucz, aby uzyskać wynik. Wybierz tę komórkę wynikową, a następnie przeciągnij jej uchwyt autouzupełniania w dół, aby zastosować tę formułę do innych komórek.
=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("No."," "&B5&" "))),{2,3,4,5,6}))
Uwagi:
4.2 Wyodrębnij liczby po określonym tekście z funkcją zdefiniowaną przez użytkownika
Poniższa funkcja zdefiniowana przez użytkownika może również pomóc w wyodrębnieniu liczb po określonym tekście w komórce. Proszę wykonać następujące czynności.
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 Kod modułu.
Kod VBA: wyodrębnij liczby po określonym tekście w komórce
Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by Extendoffice 20220106
Dim xValue As String
Dim xRntString As String
Dim xStart As Integer
Dim xC
xValue = Rng.Text
xStart = InStr(1, xValue, Char, vbTextCompare)
If IsEmpty(xStart) Then
GetNumberAfterTheChar = ""
Exit Function
End If
If xStart < 1 Then
GetNumberAfterTheChar = ""
Exit Function
End If
xStart = xStart - 1 + Len(Char)
If xStart < 1 Then
GetNumberAfterTheChar = ""
Exit Function
End If
xValue = Mid(xValue, xStart + 1)
xRntString = ""
For xI = 1 To Len(xValue)
xC = Mid(xValue, xI, 1)
Select Case Asc(xC)
Case 48 To 57
xRntString = xRntString & xC
Case Else
Exit For
End Select
Next
GetNumberAfterTheChar = xRntString
End Function
3. wciśnij inny + Q klucze do zamknięcia Microsoft Visual Basic for Applications okno.
4. Wybierz komórkę, wprowadź poniższą formułę i naciśnij Wchodzę klucz. Wybierz tę komórkę wynikową, a następnie przeciągnij jej uchwyt autouzupełniania w dół, aby zastosować tę formułę do innych komórek.
=GetNumberAfterTheChar(B5,"No. ")
Uwagi:
Podobne artykuły:
Samouczek programu Excel: dzielenie komórek tekstu, liczb i dat (oddziel na wiele kolumn)
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.
Kliknij, aby dowiedzieć się więcej ...
Excel Dodaj tekst i numer do określonej pozycji komórki
W programie Excel dodawanie tekstów lub liczb do komórek jest bardzo powszechną pracą. Takie jak dodawanie spacji między nazwami, dodawanie prefiksu lub sufiksu do komórek, dodawanie myślników do numerów społecznościowych. W tym samouczku zawiera listę prawie wszystkich scenariuszy dodawania w programie Excel i zapewnia odpowiednie metody.
Kliknij, aby dowiedzieć się więcej ...
Excel usuwa znaki, słowa, cyfry z ciągów tekstowych
Przypuśćmy, że masz długą listę ciągów tekstowych, które zawierają znaki, cyfry lub inne specyficzne symbole. W niektórych przypadkach może być konieczne usunięcie niektórych znaków na podstawie pozycji, na przykład z prawej, lewej lub środkowej części ciągów tekstowych, lub usunięcie niektórych niechcianych znaków, cyfr z listy ciągów. Znalezienie rozwiązań jeden po drugim przysporzy Ci bólu głowy, ten samouczek zbiera wszelkiego rodzaju metody usuwania znaków, słów lub liczb w programie Excel.
Kliknij, aby dowiedzieć się więcej ...
Najlepsze narzędzia biurowe
Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas. Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...
Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę
- Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
- Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
- Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!