Jak usunąć liczby w programie Excel od lewej: to proste!

Czasami pracujesz z danymi, które nie są sformatowane w wymagany sposób. Na przykład możesz mieć kombinację liczb i tekstu w komórce i musisz wyczyścić te dane.

Istnieje kilka technik usuwania liczb w programie Excel z lewej strony tekstu. Niektóre metody działają, gdy dane mają spójny wzorzec. VBA lub bardziej złożone formuły działają na mniej ustrukturyzowanych danych.

W tym artykule przedstawiono siedem metod usuwania liczb po lewej stronie danych tekstowych. Pod koniec będziesz mieć pełne zrozumienie różnych technik i kiedy ich używać.

Chodźmy!

Spis treści

• Jak używać wypełniania błyskawicznego do usuwania liczb w programie Excel z lewej strony
• Jak korzystać z funkcji Tekst do kolumn, aby usunąć liczby
• 5 funkcji tekstowych do usuwania liczb w programie Excel z lewej strony
  • 1. Funkcja MID
  • 2. Połącz z funkcją LEN
  • 3. Łączenie funkcji LEWA i ZASTĄP
  • 4. Korzystanie z funkcji PRAWO i LEN
  • 5. Korzystanie z funkcji ZAMIEŃ
• Jak używać dodatku Power Query do usuwania liczb z lewej strony
• 3 zaawansowane techniki niezależnie od liczby postaci
  • 1. Połącz kolumnę pomocniczą z formułą, aby usunąć znaki z lewej strony komórek
  • 2. Utwórz makro VBA, aby usunąć liczby
  • 3. Utwórz funkcję zdefiniowaną przez użytkownika
• Końcowe przemyślenia

Jak używać wypełniania błyskawicznego do usuwania liczb w programie Excel z lewej strony

Flash Fill to funkcja programu Excel, która może automatycznie wypełniać wzorki za Ciebie. To szybki i łatwy sposób na usunięcie liczb z lewej strony komórek.

Ta metoda może nie zawsze działać idealnie, ale może być pomocna w prostych przypadkach. Wykonaj następujące kroki, aby użyć Flash Fill:

1. W pustej kolumnie obok danych wpisz żądany wynik bez liczb (np. jeśli „1234jabłko” znajduje się w komórce A1, wpisz „jabłko” w komórce B1).

2. Wybierz komórkę z nowymi danymi (w naszym przykładzie B1) i kliknij kartę Dane na wstążce.

3. Kliknij przycisk Flash Fill w sekcji Narzędzia danych.

4. Excel automatycznie zastosuje wzór, aby usunąć liczby z lewej strony sąsiednich komórek.

To jest lokalizacja przycisku Flash Fill na karcie Dane.

Jeśli wolisz używać skrótów, naciśnij CTRL + E w systemie Windows lub CMD + E na komputerze Mac.

Flash Fill to przydatna funkcja, ale podobnie jak w przypadku większości rzeczy w programie Excel, istnieje wiele sposobów wykonania tego samego zadania.

W następnej sekcji przyjrzymy się, jak możesz zrobić to samo, korzystając z funkcji "Tekst na kolumny".

Jak korzystać z funkcji Tekst do kolumn, aby usunąć liczby

Możesz użyć funkcji „Zamień tekst na kolumny” w programie Excel, aby oddzielić liczby od lewej strony komórki. Jednak ta metoda zakłada, że ​​istnieje spójny wzorzec lub ogranicznik między liczbami a tekstem w komórce.

Zajmijmy się zbiorem danych, w którym do oddzielenia cyfr od liter użyto myślnika, takiego jak ten:

• 1234-jabłko

• 4321-pomarańczowy

• 9876-gruszka

Oto efekt przed i po, który chcemy osiągnąć:

Oto przewodnik krok po kroku z tym przykładowym zestawem danych:

1. Wybierz kolumnę A.

2. Przejdź do zakładki Dane i kliknij kreatora Tekst na kolumny.

3. Wybierz opcję Rozdzielone i kliknij Dalej.

4. Usuń zaznaczenie wszystkich ograniczników (domyślnie ta karta może być zastosowana).

5. Zaznacz ogranicznik Inne i wpisz „-” w polu wprowadzania.

6. Wybierz odpowiedni ogranicznik (spacja, przecinek itp.).

7. Kliknij Dalej i Zakończ.

Dane zostaną teraz podzielone na dwie kolumny. Usunięte znaki znajdują się w pierwszej kolumnie, a reszta tekstu w drugiej kolumnie. To zdjęcie pokazuje, jak ustawić ogranicznik:

Następnie przyjrzyjmy się pięciu funkcjom tekstowym do usuwania liczb w programie Excel.

5 funkcji tekstowych do usuwania liczb w programie Excel z lewej strony

Jeśli wiesz, ile znaków składa się na cyfry, możesz użyć funkcji tekstowej, aby je usunąć.

Załóżmy, że komórka A1 zawiera ciąg „ 1234jabłko ”. Pierwsze cztery znaki to cyfry, które chcemy usunąć.

Istnieje kilka funkcji, których można używać samodzielnie lub łączyć w celu usuwania znaków będących wartościami numerycznymi.

1. Funkcja MID

Możesz użyć funkcji MID, aby wyodrębnić część ciągu, która zaczyna się od piątego znaku.

Funkcja MID wyodrębnia określoną sekcję ciągu tekstowego, biorąc pod uwagę pozycję początkową i liczbę znaków do wyodrębnienia. Składnia funkcji MID jest następująca:

MID(tekst; numer_początkowy; liczba_znaków)

• tekst: Ciąg tekstowy zawierający znaki, które chcesz wyodrębnić.

• numer_początkowy: Pozycja pierwszego znaku, który chcesz wyodrębnić.

• num_chars: Liczba znaków do wyodrębnienia z ciągu tekstowego.

Aby usunąć cyfry po lewej stronie, wykonaj następujące kroki:

1. wybierz komórkę B2, aby zawierała funkcję i wyniki.

2. Wpisz formułę: MID(A1, 5, 7)

Powyższa formuła usunie znaki z piątego miejsca w ciągu.

2. Połącz z funkcją LEN

Wadą tej metody jest to, że ciąg tekstowy może nie zawsze mieć taką samą długość.

Możesz użyć funkcji LEN do dynamicznego obliczania długości. Ta funkcja zwraca długość ciągu tekstowego.

Możesz zmodyfikować formułę, aby zastąpić trzeci parametr funkcją LEN w następujący sposób:

=ŚRODK(A1;5;DŁ(A1)-4)

Możesz skopiować formułę w dół, używając uchwytu wypełniania. To zdjęcie pokazuje powyższą formułę w użyciu:

3. Łączenie funkcji LEWA i ZASTĄP

Funkcja LEWA zwraca skrajne lewe znaki z ciągu tekstowego. Na przykład LEWY(„1234jabłko”, 4) zwróci wartość „1234”.

Jest to przydatne, gdy chcesz wyodrębnić liczby. Ale co, jeśli chcesz je usunąć?

Możesz połączyć LEWY z funkcją SUBSTITUTE, aby zastąpić liczbę pustym łańcuchem. Oto format naszego przykładu:

=SUBSTITUT(A1; LEFT(A1; 4); „”)

4. Korzystanie z funkcji PRAWO i LEN

Funkcja RIGHT zwraca skrajne prawe znaki z ciągu tekstowego. Składnia funkcji PRAWA jest następująca:

PRAWY(tekst; [liczba_znaków])

• tekst: Ciąg tekstowy zawierający znaki, które chcesz wyodrębnić.

• num_chars: (Opcjonalne) Liczba znaków do wyodrębnienia z prawej strony ciągu tekstowego. Jeśli zostanie pominięty, domyślnie jest to 1.

Na przykład RIGHT("1234jabłko", 5) zwróci wartość "jabłko".

Możesz także łączyć funkcje PRAWA i DŁ., aby uniknąć kodowania na stałe liczby znaków alfanumerycznych. Zakłada się, że znasz liczbę znaków numerycznych.

Format naszego przykładu to: =PRAWY(A1; DŁUG(A1)-4)

5. Korzystanie z funkcji ZAMIEŃ

Funkcji REPLACE można użyć do zamiany określonej liczby znaków od lewej w komórce. Oto składnia:

ZAMIEŃ(stary_tekst, numer_początkowy, liczba_znaków, nowy_tekst)

• stary_tekst: oryginalny ciąg tekstowy, który chcesz zmodyfikować.

• numer_początkowy: pozycja w starym_tekście, od której chcesz rozpocząć zastępowanie znaków.

• num_chars: liczba znaków w old_text, które chcesz zastąpić.

• nowy_tekst: ciąg tekstowy, którym chcesz zastąpić określone znaki.

Poniższa formuła dotyczy naszych przykładowych danych:

=ZASTĄP(A1, 1, 4, „”)

I to jest ostatnia z naszych pięciu funkcji tekstowych do usuwania znaków z lewej strony w Excelu.

W większości przypadków siedem metod, które omówiliśmy do tej pory, powinno wystarczyć, ale jeśli potrzebujesz większej mocy, odpowiedzią jest Power Query, o czym porozmawiamy w następnej sekcji!

Jak używać dodatku Power Query do usuwania liczb z lewej strony

Power Query to potężne narzędzie do przekształcania danych dostępne w programie Excel. Metoda pokazana w tej sekcji zakłada, że ​​między liczbami a tekstem w komórce znajduje się ogranicznik.

Pierwszym krokiem jest przekształcenie docelowych danych w tabelę:

1. Przejdź do zakładki Dane.

2. Wybierz zakres komórek.

3. Wybierz „Z tabeli/zakresu” z sekcji Pobierz dane na wstążce.

Po otwarciu edytora dodatku Power Query wykonaj następujące kroki:

1. Wybierz kolumny docelowe.

2. Przejdź do zakładki „Przekształć”.

3. Kliknij „Wyodrębnij” na wstążce i wybierz „Tekst po separatorze”.

4. W oknie dialogowym „Tekst po ograniczniku” wprowadź myślnik (-).

5. Kliknij „OK”, aby zaakceptować szczegóły.

Przekształcona kolumna będzie teraz miała usunięte liczby z lewej strony tekstu.

Kliknij „Zamknij i załaduj”, aby załadować przekształcone dane z powrotem do arkusza.

Jeśli dopiero zaczynasz korzystać z tego narzędzia danych, zapoznaj się z naszym samouczkiem dotyczącym korzystania z dodatku Power Query w programie Excel .

Jeśli chcesz bardziej zagłębić się w korzystanie z tabel w dodatku Power Query, ten film przedstawia kilka bardziej złożonych przykładów:

3 zaawansowane techniki niezależnie od liczby postaci

Główną wadą dotychczasowych metod jest to, że liczby we wszystkich komórkach muszą mieć ten sam wzór lub mieć określoną liczbę znaków.

Co zrobić, jeśli chcesz usunąć znaki bez wyraźnego wzoru w oryginalnych danych? Techniki opisane w tej sekcji są przeznaczone dla średniozaawansowanych użytkowników programu Excel.

1. Połącz kolumnę pomocniczą z formułą, aby usunąć znaki z lewej strony komórek

Możesz łączyć funkcje z kolumnami pomocniczymi, gdy nie znasz całkowitej długości liczb. Ta metoda wymaga dwuetapowego procesu:

• użyj formuły, aby utworzyć tymczasową zmodyfikowaną wersję oryginalnego tekstu

• użyj innej formuły, aby usunąć liczby na podstawie zmodyfikowanego tekstu.

Pierwszym krokiem jest użycie następującego wzoru w celu zastąpienia wszystkich liczb określonym znakiem (np. „|”):

=SUBSTITUT(SUBSTITUTE(SUBSTITUT(SUBSTITUT(A1, „0”, „|”), „1”, „|”), „2”, „|”), „3”, „|”), „4”, „|”), „5”, „|”), „6”, „|”), „7”, „|”), „8”, „ |”), „9”, „|”)

Drugim krokiem jest użycie następującej formuły w innej kolumnie pomocniczej (np. kolumnie C):

=PRZYTNIJ(PRAWY(PODŁUŻ(B1; „|”, POWTÓRZ(” „, DŁUG(B1))), DŁUG(B1)))

To jest przed i po z formułami w kolumnach B i C:

Kiedy zaczniesz łączyć funkcje w ten sposób, możesz napotkać problemy z wyborem niewłaściwych opcji w swoich formułach. Nasz artykuł na temat znajdowania odwołań cyklicznych w programie Excel pomoże Ci rozwiązać typowy błąd.

Możesz także udostępnić arkusz kalkulacyjny zawierający kolumny pomocnicze. Ważne jest, aby Twoi współpracownicy nie usuwali ani nie zmieniali formuł w wynikach pośrednich. Nasz artykuł na temat sposobów blokowania kolumn w Excelu pomoże ci stworzyć tę funkcjonalność.

2. Utwórz makro VBA, aby usunąć liczby

VBA to wszechstronny język skryptowy , który umożliwia automatyzację zadań i tworzenie niestandardowych rozwiązań w programie Excel.

Na przykład możesz napisać makro, które usuwa liczby z lewej strony wszystkich komórek w określonej kolumnie lub zakresie.

Aby to zrobić, wykonaj następujące kroki:

1. Naciśnij ALT + F11, aby otworzyć edytor VBA.

2. Kliknij Wstaw > Moduł, aby utworzyć nowy moduł.

3. Skopiuj i wklej następujący kod, aby usunąć tylko liczby z lewej strony kolumny A:

Sub RemoveLeftNumbers()
    Dim ws As Worksheet, cell As Range, i As Integer
    
    ' Set the active worksheet as the target worksheet
    Set ws = ActiveSheet

    ' Loop through all the cells in column A
    For Each cell In ws.Range("A1:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
        ' Remove numbers from the left of each cell
        For i = 1 To Len(cell.Value)
            If Not IsNumeric(Mid(cell.Value, i, 1)) Then
                Exit For
            End If
        Next i
        cell.Value = Mid(cell.Value, i)
    Next cell
End Sub

Ostatnim krokiem jest uruchomienie makra. Kliknij zielony trójkąt lub element menu „Uruchom”.

3. Utwórz funkcję zdefiniowaną przez użytkownika

Możesz także utworzyć funkcję zdefiniowaną przez użytkownika za pomocą VBA, którą możesz wykorzystać w swoich formułach.

Aby to zrobić, wykonaj następujące kroki:

1. Naciśnij ALT + F11, aby otworzyć edytor VBA.

2. Kliknij Wstaw > Moduł, aby utworzyć nowy moduł.

3. Skopiuj i wklej następujący kod:

Public Function RemoveNumbersFromLeft (text As String) As String
    Dim i As Integer
    For i = 1 To Len(text)
        If Not IsNumeric(Mid(text, i, 1)) Then
            Exit For
        End If
    Next i
    RemoveNumbersFromLeft = Mid(text, i)
End Function

Teraz wróć do arkusza programu Excel i użyj funkcji publicznej w formule.

Na przykład ta formuła działa poprzez akceptację komórki A1:

=UsuńLiczbyOdLewej(A1)

Końcowe przemyślenia

W tym artykule zbadaliśmy wiele metod usuwania liczb z lewej strony ciągów tekstowych w programie Excel. Każda technika ma swoje zalety i ograniczenia.

Żadna pojedyncza metoda nie jest idealna w każdej sytuacji, ale z pewnością istnieje podejście, które najlepiej odpowiada Twoim potrzebom. Dzięki praktyce staniesz się biegły w stosowaniu tych technik do radzenia sobie z każdym wyzwaniem związanym z formatowaniem danych, które stanie na twojej drodze.

Więc masz to, tajniki usuwania niechcianych cyfr z lewej strony w Excelu. Na początku może to wyglądać na trochę trudne, ale dzięki odpowiednim technikom poradzisz sobie z tym w mgnieniu oka! Szczęśliwe łamanie liczb!