✅ Wyodrębnij liczby z ciągu w programie Excel (przy użyciu formuł lub VBA)

Spisie treści

Wyodrębnij liczby z ciągu w programie Excel (Formuła dla programu Excel 2016)
Wyodrębnij liczby z ciągu w programie Excel (dla programu Excel 2013/2010/2007)
Oddziel tekst i liczby w programie Excel za pomocą VBA

Obejrzyj wideo - jak wyodrębnić liczby z ciągu tekstowego w programie Excel (przy użyciu formuły i VBA)

W programie Excel nie ma wbudowanej funkcji wyodrębniania liczb z ciągu w komórce (lub odwrotnie - usuń część numeryczną i wyodrębnij część tekstową z ciągu alfanumerycznego).

Można to jednak zrobić za pomocą koktajlu funkcji Excela lub prostego kodu VBA.

Pozwól, że najpierw pokażę ci, o czym mówię.

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz wyodrębnić liczby z ciągu (jak pokazano poniżej):

Wybrana metoda będzie również zależeć od używanej wersji programu Excel:

W przypadku wersji wcześniejszych niż Excel 2016 musisz użyć nieco dłuższych formuł
W programie Excel 2016 możesz użyć nowo wprowadzonej funkcji TEXTJOIN
Metoda VBA może być używana we wszystkich wersjach Excela

Kliknij tutaj, aby pobrać przykładowy plik

Wyodrębnij liczby z ciągu w programie Excel (Formuła dla programu Excel 2016)

Ta formuła będzie działać tylko w programie Excel 2016, ponieważ korzysta z nowo wprowadzonej funkcji TEKSTJOIN.

Ponadto ta formuła może wyodrębnić liczby znajdujące się na początku, końcu lub środku ciągu tekstowego.

Zauważ, że formuła TEXTJOIN opisana w tej sekcji dałaby wszystkie znaki numeryczne razem. Na przykład, jeśli tekst to „Cena 10 biletów to 200 USD”, w wyniku otrzymasz 10200.

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz wyodrębnić liczby z ciągów w każdej komórce:

Poniżej znajduje się formuła, która da ci część numeryczną z ciągu w Excelu.

=TEKSTJOIN("",PRAWDA,JEŻELIBŁĄD((ŚRODEK(A2,ROW(POŚREDNIA("1:"&LEN(A2)))),1)*1),""))

To jest formuła tablicowa, więc musisz użyć ‘Control + Shift + Enter‘ zamiast Enter.

Jeśli w ciągu tekstowym nie ma liczb, ta formuła zwróci spację (pusty ciąg).

Jak działa ta formuła?

Pozwól, że przełamię tę formułę i spróbuję wyjaśnić, jak to działa:

ROW(INDIRECT(„1:”&LEN(A2))) - ta część formuły da ciąg liczb zaczynający się od jednego. Funkcja LEN w formule zwraca całkowitą liczbę znaków w ciągu. W przypadku „Koszt wynosi 100 USD” zwróci 19. Formuły zmienią się zatem w WIERSZ(POŚREDNIA(„1:19”). Funkcja WIERSZ zwróci wtedy serię liczb - {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
(MID(A2,ROW(INDIRECT(„1:”&LEN(A2))),1)*1) - Ta część formuły zwróci tablicę #VALUE! błędy lub liczby na podstawie ciągu. Wszystkie znaki tekstowe w ciągu stają się #ARG! błędy i wszystkie wartości liczbowe pozostają bez zmian. Dzieje się tak, ponieważ pomnożyliśmy funkcję MID przez 1.
IFERROR((MID(A2,ROW(INDIRECT(„1:”&LEN(A2))),1)*1)”,”) - Gdy używana jest funkcja IFERROR, wszystkie #VALUE! błędy i pozostaną tylko liczby. Wynik tej części będzie wyglądał tak - {„”;””;””;””;”;”;””;”;”;”;”;””;”;””; ””;””;””;1;0;0}
=TEXTJOIN(„”,TRUE,IFERROR((MID(A2,ROW(INDIRECT(„1:”&LEN(A2))),1)*1)”,”)) - Funkcja TEXTJOIN teraz po prostu łączy znaki ciągu to pozostaje (które są tylko liczbami) i ignoruje pusty ciąg.

Wskazówka dla profesjonalistów: Jeśli chcesz sprawdzić dane wyjściowe części formuły, wybierz komórkę, naciśnij F2, aby przejść do trybu edycji, wybierz część formuły, dla której chcesz uzyskać dane wyjściowe, i naciśnij F9. Natychmiast zobaczysz wynik. A następnie pamiętaj, aby nacisnąć Control + Z lub nacisnąć klawisz Escape. NIE uderzaj klawisza enter.

Pobierz przykładowy plik

Możesz również użyć tej samej logiki, aby wyodrębnić część tekstową z ciągu alfanumerycznego. Poniżej znajduje się formuła, która pobierze część tekstową z ciągu:

=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN (A2))),1),""))

Drobna zmiana w tej formule polega na tym, że funkcja JEŻELI służy do sprawdzenia, czy tablica otrzymana z funkcji MID jest błędna, czy nie. Jeśli jest to błąd, zachowuje wartość, w przeciwnym razie zastępuje ją pustą.

Następnie TEXTJOIN służy do łączenia wszystkich znaków tekstu.

Ostrożność: Chociaż ta formuła działa świetnie, wykorzystuje funkcję lotną (funkcja ADR.POŚR). Oznacza to, że jeśli użyjesz tego z ogromnym zbiorem danych, uzyskanie wyników może zająć trochę czasu. Najlepiej utworzyć kopię zapasową przed użyciem tej formuły w programie Excel.

Wyodrębnij liczby z ciągu w programie Excel (dla programu Excel 2013/2010/2007)

Jeśli masz Excel 2013. 2010. lub 2007, nie możesz użyć formuły TEXTJOIN, więc będziesz musiał użyć skomplikowanej formuły, aby to zrobić.

Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcesz wyodrębnić wszystkie liczby z ciągu w każdej komórce.

Poniższa formuła pozwoli to zrobić:

=JEŻELI(SUMA(DŁ(A2)-DŁ(ZAMIENNIK(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2)))), 1))* WIER(ADR.POŚR("$1:$"&DŁ (A2))),0), WIER(ADR.POŚR("$1:$"&DŁ(A2))))+1,1) * 10^WIERSZ(POŚREDNIA ("$1:$"&LEN(A2)))/10),"")

W przypadku braku liczby w ciągu tekstowym, ta formuła zwróci puste (pusty ciąg).

Chociaż jest to formuła tablicowa, ty nie potrzebuję aby użyć „Control-Shift-Enter”. Dla tej formuły działa prosty enter.

Podziękowania dla tej formuły trafiają do niesamowitego forum Mr. Excel.

Ponownie, ta formuła wyodrębni wszystkie liczby w ciągu bez względu na pozycję. Na przykład, jeśli tekst to „Cena 10 biletów to 200 USD”, w wyniku otrzymasz 10200.

Oddziel tekst i liczby w programie Excel za pomocą VBA

Jeśli oddzielanie tekstu i liczb (lub wyodrębnianie liczb z tekstu) jest czymś, co musisz często robić, możesz również użyć metody VBA.

Wystarczy użyć prostego kodu VBA, aby utworzyć niestandardową funkcję zdefiniowaną przez użytkownika (UDF) w programie Excel, a następnie zamiast używać długich i skomplikowanych formuł, użyj tej formuły VBA.

Pokażę ci, jak utworzyć dwie formuły w VBA - jedną do wyodrębniania liczb, a drugą do wyodrębniania tekstu z ciągu.

Wyodrębnij liczby z ciągu w programie Excel (za pomocą VBA)

W tej części pokażę, jak utworzyć funkcję niestandardową, aby uzyskać tylko część numeryczną z ciągu.

Poniżej znajduje się kod VBA, którego użyjemy do stworzenia tej funkcji niestandardowej:

Funkcja GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Wynik Koniec funkcji

Oto kroki, aby utworzyć tę funkcję, a następnie użyć jej w arkuszu:

Przejdź do zakładki Deweloper.
Kliknij Visual Basic (możesz również użyć skrótu klawiaturowego ALT + F11)
W otwartym zapleczu edytora VB kliknij prawym przyciskiem myszy dowolny obiekt skoroszytu.
Przejdź do Wstaw i kliknij Moduł. Spowoduje to wstawienie obiektu modułu dla skoroszytu.
W oknie Kod modułu skopiuj i wklej wspomniany powyżej kod VBA.
Zamknij edytor VB.

Teraz będziesz mógł korzystać z funkcji GetText w arkuszu. Ponieważ wykonaliśmy całą ciężką pracę w samym kodzie, wszystko, co musisz zrobić, to użyć formuły =GetNumeric(A2).

To natychmiast da ci tylko liczbową część ciągu.

Zauważ, że skoro skoroszyt zawiera teraz kod VBA, musisz go zapisać z rozszerzeniem .xls lub .xlsm.

Pobierz przykładowy plik

Jeśli musisz często używać tej formuły, możesz ją również zapisać w swoim skoroszycie makr osobistych. Umożliwi to użycie tej niestandardowej formuły w dowolnym skoroszycie programu Excel, z którym pracujesz.

Wyodrębnij tekst z ciągu w programie Excel (za pomocą VBA)

W tej części pokażę, jak utworzyć funkcję niestandardową, aby uzyskać tylko część tekstową z ciągu.

Poniżej znajduje się kod VBA, którego użyjemy do stworzenia tej funkcji niestandardowej:

Funkcja GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength Jeśli nie (IsNumeric(Mid(CellRef, i, 1))) Then Result = Wynik & Mid(CellRef, i, 1 ) Dalej i GetText = Funkcja zakończenia wyniku

Oto kroki, aby utworzyć tę funkcję, a następnie użyć jej w arkuszu:

Przejdź do zakładki Deweloper.
Kliknij Visual Basic (możesz również użyć skrótu klawiaturowego ALT + F11)
W otwartym zapleczu edytora VB kliknij prawym przyciskiem myszy dowolny obiekt skoroszytu.
Przejdź do Wstaw i kliknij Moduł. Spowoduje to wstawienie obiektu modułu dla skoroszytu.
- Jeśli masz już moduł, kliknij go dwukrotnie (nie musisz wstawiać nowego, jeśli już go masz).
W oknie Kod modułu skopiuj i wklej wspomniany powyżej kod VBA.
Zamknij edytor VB.

Teraz będziesz mógł użyć funkcji GetNumeric w arkuszu. Ponieważ wykonaliśmy całą ciężką pracę w samym kodzie, wszystko, co musisz zrobić, to użyć formuły =GetText(A2).

To natychmiast da ci tylko numeryczna część ciągu.