Jak wyodrębnić liczbę lub tekst z Excela za pomocą tej funkcji

Microsoft Excel doskonale radzi sobie z liczbami i tekstem - ale jeśli używasz obu w tej samej komórce, możesz napotkać pewne trudności. Na szczęście możesz wyodrębnić liczby lub tekst z komórek, aby wydajniej pracować z danymi. Pokazujemy kilka opcji, w zależności od formatu, w jakim obecnie znajdują się Twoje dane.

Liczby przechowywane jako tekst

Jest to powszechna sytuacja i na szczęście bardzo łatwa do rozwiązania. Czasami komórki zawierające tylko liczby są niepoprawnie oznaczone jako tekst, co uniemożliwia korzystanie z nich w programie Microsoft Excel. Na poniższym obrazku widać, że komórki w kolumnie A są przechowywane jako tekst, co wskazuje zielona flaga na każdej komórce, a także pole formatu, oba z nich są podświetlone poniżej.

Konwertuj na liczbę

Aby rozwiązać problem, po prostu kliknij komórkę, kliknij pole ostrzeżenia i wybierz “Konwertuj na liczbę.” To wszystko! Można to zrobić na wielu komórkach, zaznaczając je wszystkie i klikając pole ostrzeżenia, ale prawdopodobnie nie jest to tak skuteczne, jak następująca metoda.

Tekst do kolumn

Jeśli masz wiele komórek, które wymagają naprawy, użycie tej metody może zająć monumentalną ilość czasu. Aby szybciej przejść przez ten proces, możesz użyć tekstu programu Microsoft Excel w kolumnach Jak przekonwertować pliki tekstowe w formacie Excel na arkusze kalkulacyjne Excela Jak przekonwertować pliki tekstowe w formacie Excel na arkusze kalkulacyjne Excel Użyj programu Microsoft Excel do importowania danych z pliku tekstowego do arkusza roboczego. Kreator importu tekstu pomaga importować dane z tekstu rozdzielanego i porządnie go porządkować. funkcjonować. Wybierz liczby, które chcesz przekonwertować, przejdź do Dane> Tekst do kolumn, i użyj kreatora, aby upewnić się, że liczby są poprawnie wyświetlane (w większości przypadków wystarczy kliknąć Kolejny i koniec bez bałaganu przy żadnym z ustawień).

Tekst zostanie przekonwertowany na liczby i gotowe. Pamiętaj, że działa to tylko dla jednej kolumny na raz, więc jeśli masz dużo kolumn, możesz skorzystać z tej ostatecznej metody.

Wklej specjalnie

Aby ta metoda zadziałała, musisz wprowadzić liczbę 1 w komórce (ważne jest, aby była w formacie liczbowym). Wybierz tę komórkę i skopiuj ją. Teraz wybierz wszystkie liczby, które chcesz przekonwertować na format liczb, i naciśnij Edycja> Wklej specjalnie. Wybierz “Zwielokrotniać” z sekcji Operacja i naciśnij OK.

Wszystkie wybrane liczby zostaną przekonwertowane na format ogólny, który rozpoznaje liczby. Wszelkie komórki tekstowe zostaną również przekonwertowane na format ogólny, co nie powinno powodować żadnych problemów.

Wyodrębnianie liczb lub tekstu z komórek o mieszanym formacie

Teraz dochodzimy do najtrudniejszej części: pozyskiwania liczb z komórek zawierających wiele formatów danych wejściowych. Jeśli masz numer i jednostkę (np “7 łopat,” jak mamy poniżej), napotkasz ten problem. Aby rozwiązać ten problem, przyjrzymy się kilku różnym sposobom podziału komórek na liczby i tekst, umożliwiając pracę z każdą z nich osobno. Pierwsza metoda jest nieco kłopotliwa, ale działa bardzo dobrze na małych zestawach danych.

LEWO / PRAWO i SZUKAJ

Główną funkcją, której będziemy tutaj używać, jest LEWA, która zwraca znaki z lewej strony z komórki. Jak widać w naszym zestawie danych powyżej, mamy komórki z liczbami jedno-, dwu- i trzyznakowymi, więc będziemy musieli zwrócić jeden, dwa lub trzy znaki z lewej komórki. Łącząc LEWO z funkcją WYSZUKIWANIA 4 Funkcje wyszukiwania Excel w celu efektywnego wyszukiwania arkuszy kalkulacyjnych 4 Funkcje wyszukiwania Excel w celu efektywnego wyszukiwania arkuszy kalkulacyjnych Przeszukiwanie dużego arkusza kalkulacyjnego Excel nie zawsze jest łatwe. Użyj formuł wyszukiwania, aby zaoszczędzić czas i efektywnie wyszukiwać arkusze kalkulacyjne. możemy zwrócić wszystko na lewo od miejsca. Oto funkcja:

= LEWY (A1, SZUKAJ („”, A1, 1))

Spowoduje to zwrócenie wszystkiego na lewo od miejsca. Korzystanie z uchwytu wypełnienia Jak zaoszczędzić czas w programie Excel za pomocą uchwytu wypełnienia Jak zaoszczędzić czas w programie Excel za pomocą uchwytu wypełnienia Opanowanie uchwytu wypełnienia jest pierwszym krokiem do zostania kreatorem programu Excel. aby zastosować formułę do pozostałych komórek, otrzymujemy to (możesz zobaczyć formułę na pasku funkcji u góry obrazu):

Jak widać, wszystkie liczby są teraz odizolowane, więc można nimi manipulować. Chcesz również wyizolować tekst? Możemy użyć funkcji PRAWEJ w ten sam sposób:

= PRAWO (A1, LEN (A1) -SZUKAJ („”, A1, 1))

Zwraca x znaków z prawej strony komórki, gdzie x jest całkowitą długością komórki minus liczba znaków po lewej stronie spacji.

Teraz można także manipulować tekstem. Chcesz je ponownie połączyć? Wystarczy użyć funkcji CONCATENATE dla wszystkich komórek jako danych wejściowych:

= CONCATENATE (E1, F1)

Oczywiście ta metoda działa najlepiej, jeśli masz tylko liczby i jednostki i nic więcej. Jeśli masz inne formaty komórek, być może będziesz musiał wykazać się kreatywnością dzięki formułom, aby wszystko działało poprawnie. Jeśli masz gigantyczny zestaw danych, warto wypracować formułę!

Tekst do kolumn

Funkcja Tekst do kolumn jest przydatna w przypadku kolumn zawierających tylko liczby, ale może również ułatwić życie, jeśli masz komórki o mieszanym formacie. Wybierz kolumnę, z którą chcesz pracować i naciśnij Dane> Tekst do kolumn. Następnie możesz użyć kreatora, aby wybrać separator (zwykle spacja jest najlepsza) i podzielić kolumnę tak, jak chcesz.

Jeśli masz tylko cyfry jedno- i dwucyfrowe, opcja Stała szerokość może być również przydatna, ponieważ dzieli tylko pierwsze dwa lub trzy znaki komórki (możesz utworzyć liczbę podziałów, jeśli chcesz, ale zapiszę pełne wyjaśnienie podziału o stałej szerokości dla innego artykułu).

Jeśli Twój zestaw danych zawiera wiele kolumn i wolisz nie używać tekstu do kolumn dla każdej z nich, możesz łatwo uzyskać ten sam efekt, korzystając z szybkiego eksportu i importu. Najpierw wyeksportuj arkusz kalkulacyjny jako plik wartości rozdzielanych przecinkami (CSV). Kliknij Plik> Zapisz jako… i zapisz plik jako CSV.

Teraz otwórz nowy arkusz kalkulacyjny i kliknij Plik> Importuj… Wybierz plik CSV i użyj kreatora importu, aby podzielić dane na dwie kolumny (wykonasz dokładnie te same kroki, co w przypadku kreatora Text to Columns). Wybierając “Przestrzeń” w sekcji Separatory powiesz programowi Microsoft Excel, aby dzielił dane wszędzie tam, gdzie znajdzie spację, która izoluje liczby i tekst.

Po zakończeniu kliknij przycisk Zakończ, a otrzymasz nowy arkusz kalkulacyjny z kolumnami podzielonymi na dwie części. Oczywiście, jeśli masz więcej niż jedną spację w komórce, skończysz z więcej niż dwiema kolumnami, jak widać tutaj:

Niestety nie ma dobrego rozwiązania tego problemu przy użyciu tej metody; musisz po prostu połączyć komórki z powrotem.

Bardziej skomplikowane sytuacje

Dzięki powyższym strategiom powinieneś być w stanie wyodrębnić liczby lub tekst z większości komórek o mieszanym formacie, które powodują problemy. Nawet jeśli nie, prawdopodobnie możesz połączyć je z niektórymi potężnymi funkcjami tekstowymi zawartymi w programie Microsoft Excel, aby uzyskać znaki, których szukasz. Istnieją jednak znacznie bardziej skomplikowane sytuacje, które wymagają bardziej skomplikowanych rozwiązań.

Na przykład znalazłem post na forum, w którym ktoś chciał wyodrębnić liczby z ciągu znaków takiego jak “45t * i 65 /”, żeby skończył “4565.” Inny plakat podał następującą formułę jako jeden ze sposobów:

= SUMPRODUCT (MID (0 i A1, LARGE (INDEX (ISNUMBER (- MID (A1, ROW (1 $: 25 USD), 1) 25 $)), * ROW (1 $: 25 USD), 0), ROW (1 $: 25 USD)) + 1, 1) * 10 ^ ROW (1 $: 25 $) / 10)

Szczerze mówiąc, nie mam pojęcia, jak to działa. Ale zgodnie z postem na forum zabierze liczby ze skomplikowanego ciągu liczb i innych znaków. Chodzi o to, że przy wystarczającym czasie, cierpliwości i wysiłku możesz wyodrębnić liczby i tekst z niemal wszystkiego! Musisz tylko znaleźć odpowiednie zasoby Potrzebujesz pomocy z formułami Excel? 7 Zasoby do skonsultowania Potrzebujesz pomocy z formułami Excel? 7 Zasoby do konsultacji Excel to złoty standard arkusza kalkulacyjnego. Jeśli musisz korzystać z programu Excel, a mimo to musisz się z nim zapoznać, te zasoby szybko wprowadzą Cię w podstawy i nie tylko. .

Jakiej strategii używasz?

Teraz, gdy masz lepszy pomysł, jak uzyskać cyfry i litery z komórek o mieszanym formacie, chcemy wiedzieć, jakich innych strategii użyłeś, aby zrobić to samo. Opublikuj swoje szalone formuły, procesy lub cokolwiek innego, co może być pomocne w komentarzach, abyśmy wszyscy mogli się z nich uczyć! Aby uzyskać więcej zabawy z programem Excel, sprawdź, jak oddzielić imię i nazwisko w programie Excel Jak oddzielić imię i nazwisko w programie Excel Jak oddzielić imię i nazwisko w programie Excel Pracujesz z nazwami w programie Microsoft Excel? Oto przydatny samouczek, który pokazuje, jak rozdzielić imiona i nazwiska w programie Excel. .