Jak podzielić liczby i tekst z ciągu w programie Excel?

Wiele razy otrzymuję mieszane dane z terenu i serwera do analizy. Dane te są zwykle brudne, mają kolumnę pomieszaną z liczbą i tekstem. Czyszcząc dane przed analizą, oddzielam liczby i tekst w osobnych kolumnach. W tym artykule powiem Ci, jak możesz to zrobić.

Scenariusz:
Więc jeden z naszych znajomych na Exceltip.com zadał to pytanie w sekcji komentarzy. „Jak oddzielić liczby znajdujące się przed tekstem i na końcu tekstu za pomocą formuły Excela. Na przykład 125EvenueStreet i LoveYou3000 itp.”

Do wyodrębniania tekstu używamy RIGHT, LEFT, MID i innych funkcji tekstowych. Musimy tylko znać liczbę tekstów do wyodrębnienia. I tutaj zrobimy to samo najpierw.
Wyodrębnij liczbę i tekst z ciągu, gdy liczba znajduje się na końcu ciągu
Dla powyższego przykładu przygotowałem ten arkusz. W komórce A2 mam ciąg. W komórce B2 chcę część tekstową, a w C2 część liczbową.

Musimy więc tylko znać pozycję, od której zaczyna się numer. Następnie użyjemy funkcji Left i innej. Aby uzyskać pozycję pierwszej liczby, używamy poniższej ogólnej formuły:
Ogólna formuła, aby uzyskać pozycję pierwszej liczby w ciągu:

=MIN(SZUKAJ({0,1,2,3,4,5,6,7,8,9};String_Ref&"0123456789")

To zwróci pozycję pierwszej liczby.
Dla powyższego przykładu napisz tę formułę w dowolnej komórce.

=MIN(SZUKAJ({0,1,2,3,4,5,6,7,8,9};A5&"0123456789"))

Wyodrębnij część tekstową

Zwróci 15, ponieważ pierwsza znaleziona liczba znajduje się na 15. pozycji w tekście. Wyjaśnię to później.

Teraz, aby uzyskać tekst, od lewej wystarczy uzyskać 15-1 znak z ciągu. Więc użyjemy
Funkcja LEWO do wyodrębniania tekstu.
Formuła do wyodrębnienia tekstu z lewej

=LEWO(A5;MIN(SZUKAJ({0,1,2,3,4,5,6,7,8,9};A5&"0123456789"))-1)

Tutaj właśnie odjęliśmy 1 od liczby zwróconej przez MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789")).
Wyciąg numer części

Teraz, aby uzyskać liczby, wystarczy pobrać znaki liczbowe z pierwszej znalezionej liczby. Więc obliczamy całkowitą długość strunowy i odejmij pozycję pierwszej znalezionej liczby i dodaj 1 do niego. Prosty. Tak, to po prostu brzmi skomplikowanie, jest proste.
Formuła do wyodrębniania liczb z prawej strony

=PRAWO(A5,DŁ(A5)-MIN(SZUKAJ({0,1,2,3,4,5,6,7,8,9};A5&"0123456789"))+1)

Tutaj właśnie otrzymaliśmy całkowitą długość ciągu za pomocą funkcji LEN, a następnie odjęliśmy pozycję pierwszej znalezionej liczby, a następnie dodaliśmy do niej 1. To daje nam całkowitą liczbę liczb. Dowiedz się więcej tutaj o wyodrębnianiu tekstu za pomocą funkcji LEWO i PRAWO programu Excel.

Tak więc część funkcji LEWA i PRAWA jest prosta. Część Tricky to MIN i SEARCH Part, która podaje nam pozycję pierwszego znalezionego numeru. Zrozummy to.
Jak to działa
Wiemy, jak działają funkcje LEFT i RIGHT. Zbadamy główną część tej formuły, która pobiera pozycję pierwszej znalezionej liczby, czyli: MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},String&"0123456789 ")
Funkcja SEARCH zwraca pozycję tekstu w ciągu. Funkcja SZUKAJ('tekst','ciąg') przyjmuje dwa argumenty, pierwszy tekst, który chcesz przeszukać, drugi ciąg, w którym chcesz przeszukać.

- Tutaj w SZUKAJU, na pozycji tekstu mamy tablicę liczb od 0 do 9. A na pozycji łańcucha mamy ciąg, który jest połączony z "0123456789" za pomocą & operator. Czemu? Powiem ci.
- Każdy element w tablicy {0,1,2,3,4,5,6,7,8,9} zostanie przeszukany w danym ciągu i zwróci swoją pozycję w postaci tablicy string o tym samym indeksie w tablicy.
- Jeśli jakakolwiek wartość nie zostanie znaleziona, spowoduje to błąd. Stąd wszystkie formuły będą skutkować błędem. Aby tego uniknąć, połączyliśmy liczby „0123456789” w tekście. Aby zawsze znajdował każdą liczbę w łańcuchu. Te liczby są w końcu więc nie sprawią żadnego problemu.
- Teraz Funkcja MIN zwraca najmniejszą wartość z tablicy zwróconej przez funkcję SZUKAJ. Ta najmniejsza wartość będzie pierwszą liczbą w ciągu. Teraz korzystając z funkcji LICZBA oraz W LEWO i W PRAWO, możemy rozdzielić części tekstowe i tekstowe.

Przeanalizujmy nasz przykład. W A5 mamy ciąg, który zawiera nazwę ulicy i numer domu. Musimy je rozdzielić na różne komórki.
Najpierw zobaczmy, jak otrzymaliśmy naszą pozycję pierwszej liczby w łańcuchu.

- MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789")): przełoży się to na MIN(SEARCH({0,1,2,3, 4,5,6,7,8,9}”,Monta270123456789”))

Teraz, jak wyjaśniłem, wyszukiwanie przeszuka każdą liczbę w tablicy {0,1,2,3,4,5,6,7,8,9} w Monta270123456789 i zwróci swoją pozycję w formie tablicowej. Zwróconą tablicą będzie {8,9,6,11,12,13,14,7,16,17}. Jak?
0 będzie wyszukiwane w ciągu. Znajduje się na 8 pozycji. Dlatego naszym pierwszym elementem jest 8. Zauważ, że nasz oryginalny tekst ma tylko 7 znaków. Zdobyć. 0 nie jest częścią Monta27.
Następny 1 zostanie przeszukany w ciągu i również nie jest częścią oryginalnego ciągu, a my otrzymamy jego pozycję 9.
Następne 2 zostaną przeszukane. Ponieważ jest to część oryginalnego ciągu, otrzymujemy jego indeks jako 6.
Podobnie każdy element znajduje się w jakimś miejscu.

- Teraz ta tablica jest przekazywana do funkcji MIN jako MIN({8,9,6,11,12,13,14,7,16,17}). MIN zwraca 6, która jest pozycją pierwszej liczby znalezionej w oryginalnym tekście.
  A historia po tym jest dość prosta. Używamy tej liczby, wyodrębnij tekst i liczby za pomocą funkcji LEWO i PRAWO.

Wyodrębnij liczbę i tekst z ciągu, gdy liczba znajduje się na początku ciągu
W powyższym przykładzie Number znajdowało się na końcu ciągu. Jak wyodrębnić numer i tekst, gdy numer jest na początku.

Przygotowałem podobną tabelę jak powyżej. Po prostu ma numer na początku.

Tutaj użyjemy innej techniki. Policzymy długość liczb (która wynosi tutaj 2) i wydobędziemy tę liczbę znaków z lewej strony ciągu.
Tak więc metoda to =LEWO (ciąg, liczba liczb)
Aby policzyć liczbę znaków, to jest Formuła.
Ogólna formuła do zliczania liczb:

= SUMA(DŁ(ciąg)-DŁ(PODSTAW(ciąg;{"0","1","2","3","4","5","6","7","8" ,"9"},"))

Tutaj,

- - Funkcja SUBSTITUTE zamieni każdy znaleziony numer na „” (puste). Jeśli liczba zostanie znaleziona podstawiona ti i nowy łańcuch zostanie dodany do tablicy, w przeciwnym razie oryginalny łańcuch zostanie dodany do tablicy. W ten sposób otrzymamy tablicę 10 ciągów.
  - Teraz funkcja LEN zwróci długość znaków w tablicy tych ciągów.
  - Następnie od długości oryginalnych napisów odejmiemy długość każdego napisu zwróconego przez funkcję SUBSTITUTE. To ponownie zwróci tablicę.
  - Teraz SUM doda wszystkie te liczby. Jest to liczba liczb w ciągu.

Wyodrębnij część numeru z ciągu

Skoro znamy długość liczb w strunowy, podstawimy tę funkcję w LEWO.
Ponieważ mamy nasz ciąg A11, nasz:

Formuła do wyodrębniania liczb z LEWEJ

=LEWO(A11;SUMA(DŁ(A11)-DŁ(ZAMIENNIK(A11;{"0","1","1","2","3","4","5","6","7" ,"8","9"},""))))

Wyodrębnij część tekstową z ciągu

Ponieważ znamy liczbę liczb, możemy odjąć ją od całkowitej długości ciągu, aby uzyskać alfabet liczb w ciągu, a następnie użyć funkcji right, aby wyodrębnić tę liczbę znaków z prawej strony ciągu.

Formuła do wyodrębnienia tekstu z PRAWEGO

=PRAWY(A11;DŁ(A2)-SUMA(DŁ(A11)-DŁ(ZAMIENNIK(A11;{"0","1","2","3","4","5","6 ","7","8","9"},""))))

Jak to działa
Główną częścią obu formuł jest SUM(LEN(A11)-LEN(SUBSTITUTE(A11,{"0","1","2","3","4","5","6"," 7","8","9"},""))) obliczające pierwsze wystąpienie liczby. Dopiero po znalezieniu tego jesteśmy w stanie podzielić tekst i liczbę za pomocą funkcji LEWO. Więc zrozummy to.

- - ZAMIENNIK(A11,{"0","1","2","3","4","5","6","7","8","9"},""): Ta część zwraca tablicę ciągów w A11 po zastąpieniu tych liczb wartością nic/pustą („”). Do 27Monta zwróci {"27Monta","27Monta","7Monta","27Monta","27Monta","27Monta","27Monta","2Monta","27Monta","27Monta"}.
  - DŁ(ZAMIENNIK(A11;{"0","1","2","3","4","5","6","7","8","9"};"" )): Teraz część SUBSTITUTE jest otoczona funkcją LEN. Ta zwracana długość tekstów w tablicy zwracana przez funkcję SUBSTITUTE. W rezultacie będziemy mieli {7,7,6,7,7,7,7,7,6,7,7}.
  - DŁ(A11)-DŁ(ZAMIENNIK(A11,{"0","1","2","3","4","5","6","7","8","9 "},")): Tutaj odejmujemy każdą liczbę zwróconą przez powyższą część od długości rzeczywistego ciągu. Długość oryginalnego tekstu wynosi 7. Stąd będziemy mieli {7-7,7-7,7-6,….}. W końcu będziemy mieli {0,0,1,0,0,0,0,1,0,0}.
  - SUMA(DŁ(A11)-DŁ(ZAMIENNIK(A11;{"0","1","2","3","4","5","6","7","8"; "9"},"")): Tutaj użyliśmy SUMA do zsumowania tablicy zwróconej przez powyższą część funkcji. To da 2. Co jest liczbą liczb w łańcuchu.

Teraz za pomocą tego możemy wyodrębnić teksty i liczbę i podzielić je na różne komórki. Ta metoda będzie działać zarówno z tekstem typu, gdy liczba jest na początku, jak i kiedy jest na końcu. Wystarczy odpowiednio użyć funkcji LEWO i PRAWO.
Użyj funkcji SplitNumText, aby podzielić liczby i teksty z ciągu
Powyższe metody to są nieco złożone i nie są przydatne, gdy tekst i liczby są mieszane. Aby podzielić tekst i liczby, użyj tej funkcji zdefiniowanej przez użytkownika.

Składnia:

=SplitNumText(ciąg, op)

Strunowy: Ciąg, który chcesz podzielić.
Operacja: to jest logiczne. Przekaż 0 lub fałszywe aby uzyskać część tekstową. W przypadku części liczbowej należy przekazać prawda lub dowolna liczba większa niż 0.
Na przykład, jeśli ciąg znajduje się w A20, to
Formuła do wyodrębniania liczb z ciągu znaków to:

=Podziel.liczbatekst(A20;1)

I
Formuła wyodrębniania tekstu z ciągu znaków to:

=Podziel.liczbatekst(A20,0)

Skopiuj poniższy kod w module VBA, aby powyższa formuła działała.

Function SplitNumText(str As String, op As Boolean) num = "" txt = "" For i = 1 To Len(str) If IsNumeric(Mid(str, i, 1)) Then num = num & Mid(str, i , 1) Else txt = txt & Mid(str, i, 1) End If Next i If op = True Then SplitNumText = numer Else SplitNumText = txt End If End Function

Ten kod po prostu sprawdza każdy znak w ciągu, czy jest to liczba, czy nie. Jeśli jest to liczba, to jest przechowywana w zmiennej num, w przeciwnym razie w zmiennej txt. Jeśli użytkownik zgłosi true dla op, zwracana jest liczba, w przeciwnym razie zwracany jest txt.

Moim zdaniem jest to najlepszy sposób na oddzielenie liczby i tekstu od ciągu.
Możesz pobrać skoroszyt tutaj, jeśli chcesz.

Więc tak, to są sposoby na dzielenie tekstu i liczb w różnych komórkach. Daj mi znać, jeśli masz jakiekolwiek wątpliwości lub lepsze rozwiązanie w sekcji komentarzy poniżej. Zawsze fajnie jest wchodzić w interakcje z facetami.

Kliknij poniższy link, aby pobrać działający plik:

Podziel numer i tekst z komórki

Popularne artykuły:
50 skrótów Excela, aby zwiększyć wydajność
Funkcja WYSZUKAJ.PIONOWO w programie Excel
LICZ.JEŻELI w Excelu 2016
Jak korzystać z funkcji SUMIF w programie Excel?