Przejdź do głównej zawartości

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ć ]

(Kliknij dowolny nagłówek w spisie treści poniżej lub po prawej stronie, aby przejść do odpowiedniego rozdziału.)

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

Ciąg tekstowy: ciąg tekstowy zawierający znaki, które chcesz wyodrębnić. Może to być odwołanie do komórki lub rzeczywisty ciąg tekstowy ujęty w podwójne cudzysłowy;
Liczba_znaków: Określa liczbę znaków, które chcesz wyodrębnić.
Liczba_znaków musi być większa lub równa zero;
Jeśli określona liczba_znaków jest większa niż długość ciągu tekstowego, zwraca cały tekst;
Jeśli liczba_znaków zostanie pominięta, przyjmuje się, że wynosi 1.

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

Ciąg tekstowy: ciąg tekstowy zawierający znaki, które chcesz wyodrębnić. Może to być odwołanie do komórki lub rzeczywisty ciąg tekstowy ujęty w podwójne cudzysłowy;
Liczba_znaków: Określa liczbę znaków, które chcesz wyodrębnić.
Połączenia Liczba_znaków musi być większa lub równa zero;
Jeśli określony Liczba_znaków jest większa niż długość ciągu tekstowego, zwraca cały tekst;
Jeśli Liczba_znaków jest pominięty, przyjmuje się, że wynosi 1.

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.

2.1) Upewnij się, że przebywasz w Wyodrębnij według lokalizacji patka;
2.2) W łodzie w polu, wybrany zakres jest wyświetlany w środku, możesz zmienić na inny zakres zgodnie ze swoimi potrzebami;
2.3) W Opcje sekcja:
Jeśli chcesz wyodrębnić pierwsze N ​​znaków, wybierz Pierwszy znak N. przycisk radiowy, a następnie określ liczbę znaków, które zostaną wyodrębnione w polu tekstowym. W tym przypadku wpisuję liczbę 2;
Jeśli chcesz wyodrębnić ostatnie N znaków, wybierz Ostatni znak N. przycisk radiowy, a następnie określ liczbę znaków, które zostaną wyodrębnione w polu tekstowym. W takim przypadku chcę wyodrębnić ostatnie 2 znaki z ciągu tekstowego, więc wpisuję cyfrę 2 w polu tekstowym.
2.4) Kliknij OK. Zobacz zrzut ekranu:

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

Ciąg tekstowy: ciąg tekstowy, z którego chcesz wyodrębnić podciąg. Może to być odwołanie do komórki lub rzeczywisty ciąg tekstowy ujęty w podwójne cudzysłowy;
Ogranicznik: Pierwszy ogranicznik, który określa, który tekst zostanie wyodrębniony z komórki.

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

Ciąg tekstowy: ciąg tekstowy, z którego chcesz wyodrębnić podciąg. Może to być odwołanie do komórki lub rzeczywisty ciąg tekstowy ujęty w podwójne cudzysłowy;
Ogranicznik: Pierwszy ogranicznik, który określa, który tekst zostanie wyodrębniony z komórki.
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ć inne wyniki.

=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.

2.1) Zostań w Wyodrębnij według lokalizacji patka;
2.2) W łodzie pole, wybrany zakres jest wyświetlany w środku, możesz go zmienić według potrzeb;
2.3) W Opcje sekcja:
Aby wyodrębnić tekst przed pierwszym ogranicznikiem, wybierz przed tekstem przycisk radiowy, a następnie wpisz pierwszy ogranicznik w polu tekstowym;
Aby wyodrębnić tekst po pierwszym ograniczniku, wybierz po tekście przycisk radiowy, a następnie wprowadź pierwszy ogranicznik w polu tekstowym.
2.4) Kliknij OK.

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

Ciąg tekstowy: ciąg tekstowy, z którego chcesz wyodrębnić podciąg. Może to być odwołanie do komórki lub rzeczywisty ciąg tekstowy ujęty w podwójne cudzysłowy;
Ogranicznik: Ostatni ogranicznik, który określa, który tekst zostanie wyodrębniony z komórki.

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

Ciąg tekstowy: ciąg tekstowy, z którego chcesz wyodrębnić podciąg. Może to być odwołanie do komórki lub rzeczywisty ciąg tekstowy ujęty w podwójne cudzysłowy;
Ogranicznik: Ostatni ogranicznik, który określa, który tekst zostanie wyodrębniony z komórki.

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

Ciąg tekstowy: ciąg tekstowy, z którego chcesz wyodrębnić podciąg. Może to być odwołanie do komórki lub rzeczywisty ciąg tekstowy ujęty w podwójne cudzysłowy;
n-ty_znak: Liczba reprezentuje n-ty znak, po którym zostanie wyodrębniony tekst.

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

Ciąg tekstowy: Ciąg tekstowy, z którego chcesz wyodrębnić n-te słowo. Może to być odwołanie do komórki lub rzeczywisty ciąg tekstowy ujęty w podwójne cudzysłowy;
N: Liczba reprezentuje n-te słowo, które wyodrębnisz z ciągu tekstowego.

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.

2.1) W Wybierz listę formuł pudełko, plik Wyodrębnij n-te słowo z komórki opcja jest podświetlona;
2.2) W Komórka wybierz komórkę zawierającą ciąg tekstowy, z którego chcesz wyodrębnić n-te słowo;
2.3) W N-ty pole, wybierz komórkę zawierającą n-tą liczbę lub bezpośrednio wprowadź liczbę zgodnie z potrzebami;
2.4) Kliknij OK.

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

Ciąg tekstowy: Ciąg tekstowy, z którego chcesz wyodrębnić tekst. Może to być odwołanie do komórki lub rzeczywisty ciąg tekstowy ujęty w podwójne cudzysłowy;
N: Liczba reprezentuje n-te wystąpienie ogranicznika, przed którym wyodrębnisz tekst.

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

Ciąg tekstowy: Ciąg tekstowy, z którego chcesz wyodrębnić tekst. Może to być odwołanie do komórki lub rzeczywisty ciąg tekstowy ujęty w podwójne cudzysłowy;
N: Liczba reprezentuje n-te wystąpienie ogranicznika, po którym zostanie wyodrębniony tekst.

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

Komórka: Komórka, z której chcesz wyodrębnić tekst przed podziałem pierwszego wiersza.

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

Komórka: Komórka, z której chcesz wyodrębnić tekst przed podziałem pierwszego wiersza.

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

Komórka: Komórka, z której chcesz wyodrębnić tekst przed określonym słowem.
słowo: Słowo, z którego chcesz wyodrębnić cały tekst przed nim. Może to być odwołanie do komórki lub rzeczywisty ciąg tekstowy ujęty w podwójne cudzysłowy;

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:

1) W tej formule rozróżniana jest wielkość liter.
2) Jeśli wprowadzone słowo ma duplikaty w tej samej komórce, formuła wyodrębnia tekst tylko po pierwszym wystąpieniu.

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

Komórka: Komórka, z której chcesz wyodrębnić tekst po określonym słowie.
słowo: Słowo, po którym chcesz wyodrębnić cały tekst. Może to być odwołanie do komórki lub rzeczywisty ciąg tekstowy ujęty w podwójne cudzysłowy;

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:

1) W tej formule nie jest rozróżniana wielkość liter.
2) Jeśli wprowadzone słowo ma duplikaty w tej samej komórce, formuła wyodrębnia tekst tylko po pierwszym wystąpieniu.

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.

2.1) Upewnij się, że jesteś w Wyodrębnij według lokalizacji patka;
2.2) W łodzie kliknij przycisk przycisk, aby wybrać zakres komórek, w których chcesz wyodrębnić teksty;
2.3) W Opcje sekcja:
Aby wyodrębnić wszystkie teksty przed słowem, wybierz przed tekstem przycisk radiowy, a następnie wprowadź słowo w polu tekstowym;
Aby wyodrębnić wszystkie teksty po słowie, wybierz po tekście przycisk radiowy, a następnie wprowadź słowo w polu tekstowym.
2.4) Kliknij OK przycisk. Zobacz zrzut ekranu:

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:

1) B5 to komórka, z której chcesz wyodrębnić tekst między dwoma takimi samymi znakami;
2) "/” to dwa takie same znaki, które chcesz wyodrębnić między nimi.
Musisz zmienić te zmienne na podstawie własnych danych.

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

Komórka: Komórka, z której chcesz wyodrębnić tekst między dwoma różnymi znakami;
Koniec_znaku: Znak końcowy dwóch różnych znaków;
Początek_znak: Znak początkowy dwóch różnych znaków.

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.

2.1) Sprawdź FILTRY pole, a następnie wprowadź słowo "Wyciąg" do pola tekstowego;
2.2) W Wybierz listę formuł kliknij przycisk Wyodrębnij ciągi między określonym tekstem opcja;
2.3) W Wprowadzanie argumentów sekcja:
W Komórka pole, wybierz komórkę, w której chcesz wyodrębnić tekst (tutaj wybieram komórkę B5);
W Rozpocznij znak (i) wpisz początek dwóch różnych znaków;
W Koniec char(s), wprowadź końcowy znak dwóch różnych znaków.
2.4) Kliknij Ok. Zobacz zrzut ekranu:

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.

2.1) Kliknij Wyodrębnij według reguły patka;
2.2) W łodzie sekcja, kliknij przycisk, aby wybrać zakres komórek, w których chcesz wyodrębnić tekst między znakami;
2.3) W Tekst pole, wprowadź <*>;
Porady: "<"I">” to znaki, które chcesz wyodrębnić między nimi, * to symbol wieloznaczny reprezentujący dowolną liczbę znaków. Możesz wprowadzić warunek według własnych potrzeb.
2.4) Kliknij Dodaj przycisk, aby dodać warunek do Opis reguły skrzynka na listy;
2.5) Kliknij Ok przycisk. Zobacz zrzut ekranu:

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

Komórka: komórka, z której chcesz wyodrębnić wszystkie ciągi tekstowe między dwoma słowami;
Słowo_początkowe: słowo początkowe dwóch słów, z których chcesz wyodrębnić wszystkie ciągi tekstowe;
Długość1: długość znaku słowa początkowego.
Słowo końcowe: słowo końcowe z dwóch słów, z których chcesz wyodrębnić wszystkie ciągi tekstowe przed nim.
Długość2: długość znaku początkowego słowa plus 1.

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.

2.1) Sprawdź FILTRY pole, a następnie wprowadź słowo "Wyciąg" do pola tekstowego;
2.2) W Wybierz listę formuł kliknij przycisk Wyodrębnij ciągi między określonym tekstem opcja;
2.3) W Wprowadzanie argumentów sekcja:
W Komórka pole, wybierz komórkę, w której chcesz wyodrębnić tekst (tutaj wybieram komórkę B5);
W Rozpocznij znak (i) wpisz słowo początkowe dwóch słów, z których chcesz wyodrębnić wszystkie ciągi tekstowe;
W Znaki końcowe wpisz końcowe słowo dwóch słów, z których chcesz wyodrębnić wszystkie ciągi tekstowe przed nim.
2.4) Kliknij Ok. Zobacz zrzut ekranu:

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:

  1. Numer znajduje się na początku tekstu;
  2. Numer znajduje się na końcu tekstu;
  3. 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

Komórka: Komórka, z której chcesz wyodrębnić liczbę po lewej stronie ciągu tekstowego.

Uwaga:

1) Jeśli korzystasz z programu Excel 2019 lub wcześniejszego, musisz nacisnąć Ctrl + Shift + Wchodzę klawisze, aby potwierdzić tę formułę tablicową.
2) Jeśli korzystasz z programu Excel 365 lub Excel 2021, potwierdź tę formułę za pomocą Wchodzę klawisz.

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:

1) Jeśli komórka zawiera tylko liczby, zostanie wyodrębniona cała liczba.
2) Ta formuła wyodrębnia tylko liczby z lewej strony ciągu tekstowego. Jeśli liczby występują w środku lub na końcu ciągu tekstowego, zostaną zignorowane.

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

Komórka: Komórka, z której chcesz wyodrębnić liczbę z początku ciągu tekstowego.

Uwaga:

1) Jeśli korzystasz z programu Excel 2019 lub wcześniejszego, musisz nacisnąć Ctrl + Shift + Wchodzę klawisze, aby potwierdzić tę formułę tablicową.
2) Jeśli używasz Excel 365 lub Excel 2021, po prostu potwierdź tę formułę klawiszem Enter.

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:

1) Jeśli komórka zawiera tylko liczby, zostanie wyodrębniona cała liczba.
2) Ta formuła wyodrębnia tylko liczby z prawej strony ciągu tekstowego. Jeśli liczby istnieją w środku lub na początku ciągu tekstowego, zostaną zignorowane.

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

Komórka: Komórka, z której chcesz wyodrębnić liczby po określonym tekście;
Tekst: Tekst, po którym chcesz wyodrębnić liczby.

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:

1) Jeśli komórka nie zawiera określonego tekstu, formuła zwróci #N/A.
2) W formule rozróżniana jest wielkość liter.

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:

1) W tej formule B5 to komórka, w której chcesz wyodrębnić liczbę, a „Nie. ” to konkretny tekst, po którym chcesz wyodrębnić numer. Możesz je zmienić według potrzeb.
2) Jeżeli istnieje rozgranicznik między konkretnym tekstem a liczbą, należy dodać rozgranicznik na końcu tekstu. W tym przypadku dodałem spację po tekście „Nie.”, który ostatecznie wyświetla się jako „Nie. ”.
3) W tej metodzie nie jest rozróżniana wielkość liter;
4) Jeśli komórka nie zawiera określonego tekstu, formuła zwróci pusty wynik.

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

🤖 Pomocnik AI Kutools: Zrewolucjonizuj analizę danych w oparciu o: Inteligentne wykonanie   |  Wygeneruj kod  |  Twórz niestandardowe formuły  |  Analizuj dane i generuj wykresy  |  Wywołaj funkcje Kutools...
Popularne funkcje: Znajdź, wyróżnij lub zidentyfikuj duplikaty   |  Usuń puste wiersze   |  Łącz kolumny lub komórki bez utraty danych   |   Okrągły bez wzoru ...
Super wyszukiwanie: Wiele kryteriów VLookup    Wiele wartości VLookup  |   Przeglądanie pionowe na wielu arkuszach   |   Wyszukiwanie rozmyte ....
Zaawansowana lista rozwijana: Szybko twórz listę rozwijaną   |  Zależna lista rozwijana   |  Lista rozwijana wielokrotnego wyboru ....
Menedżer kolumn: Dodaj określoną liczbę kolumn  |  Przesuń kolumny  |  Przełącz stan widoczności ukrytych kolumn  |  Porównaj zakresy i kolumny ...
Polecane funkcje: Fokus siatki   |  Widok projektu   |   Duży pasek formuły    Menedżer skoroszytów i arkuszy   |  Biblioteka zasobów (Automatyczny tekst)   |  Selektor dat   |  Połącz arkusze   |  Szyfruj/odszyfruj komórki    Wysyłaj e-maile według listy   |  Super filtr   |   Specjalny filtr (filtruj pogrubienie/kursywa/przekreślenie...) ...
15 najlepszych zestawów narzędzi12 Tekst Tools (Dodaj tekst, Usuń znaki, ...)   |   50 + Wykres rodzaje (Wykres Gantta, ...)   |   40+ Praktyczne Wzory (Oblicz wiek na podstawie urodzin, ...)   |   19 Wprowadzenie Tools (Wstaw kod QR, Wstaw obraz ze ścieżki, ...)   |   12 Konwersja Tools (Liczby na słowa, Przeliczanie walut, ...)   |   7 Połącz i podziel Tools (Zaawansowane wiersze łączenia, Podział komórki, ...)   |   ... i więcej

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...

Opis


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!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations