Aby wyodrębnić cyfry z tekstu w Excelu, możesz skorzystać zarówno z wbudowanych funkcji arkusza, jak i narzędzi Power Query oraz formuł zaawansowanych. Poniżej znajdziesz kompletny poradnik krok po kroku — zarówno dla początkujących, jak i zaawansowanych użytkowników.
1. Podstawowe funkcje tekstowe w Excelu (LEWY, PRAWY, FRAGMENT.TEKSTU)
Do prostego wyodrębniania znaków przydają się trzy funkcje:
- LEWY — wyciąga znaki od lewej strony tekstu.
- PRAWY — wyciąga znaki od prawej strony tekstu.
- FRAGMENT.TEKSTU — wyciąga określoną liczbę znaków ze środka tekstu.
Przykłady użycia –
-
Wyodrębnianie pierwszych 3 znaków (np. cyfry z początku tekstu) –
=LEWY(A1; 3)Jeśli wynikem mają być cyfry, zastosuj dodatkowo
WARTOŚĆdo przekształcenia tekstu na liczbę:=WARTOŚĆ(LEWY(A1;3)) -
Wyodrębnianie trzech ostatnich znaków –
=PRAWY(A1; 3) -
Wyodrębnianie fragmentu znaków ze środka tekstu –
excel =FRAGMENT.TEKSTU(A1; start; ilość_znaków)
2. Zaawansowane wyodrębnianie wszystkich cyfr z dowolnego miejsca tekstu
Formuła do pozyskania wszystkich cyfr jako liczby
W przypadku tekstów zawierających cyfry rozproszone, zastosuj formuły złożone:
Bez funkcji POŁĄCZ.TEKSTY i zatwierdzania jako formuły tablicowe –
Oto autorska formuła, która wyciąga wszystkie cyfry i tworzy z nich liczbę (pełne omówienie znajdziesz na exceliadam.pl):
=SUMA.ILOCZYNÓW(FRAGMENT.TEKSTU(0&A1;MAX.K(INDEKS(CZY.LICZBA(--FRAGMENT.TEKSTU(A1;WIERSZ(ADR.POŚR("1:"&DŁ(A1)));1))*WIERSZ(ADR.POŚR("1:"&DŁ(A1)));0);WIERSZ(ADR.POŚR("1:"&DŁ(A1))))+1;1)*10^WIERSZ(ADR.POŚR("1:"&DŁ(A1)))/10) Ta formuła rozbija tekst na znaki, identyfikuje cyfry, łączy je i zwraca je jako liczbę.
Alternatywnie z funkcją POŁĄCZ.TEKSTY (Excel 2016+) –
- Sprawdź poradnik na exceliadam.pl, który pokazuje, jak połączyć cyfry z tekstu przy pomocy nowszych funkcji.
3. Wyodrębnianie cyfr w Power Query
Jeśli pracujesz na większych zbiorach danych, warto wykorzystać Power Query.
Instrukcja krok po kroku –
- Zaznacz dane i wybierz „Dane” > „Z Power Query”.
- W Power Query przejdź do „Dodaj kolumnę” > „Kolumna niestandardowa”.
- Wprowadź formułę:
m =Text.Select([Tekst], {"0".."9"})To wyodrębni cyfry z każdej komórki i zwróci je razem jako tekst. - Zaakceptuj i wykonaj „Załaduj” do arkusza.
Możesz także usunąć znaki specjalne lub wyodrębnić litery – wszystko w ten sam sposób.
4. Wyodrębnianie liczb z początku lub końca tekstu
Jeżeli cyfra lub liczba znajduje się na początku lub końcu tekstu, można użyć połączenia funkcji ZNAJDŹ/SZUKAJ.TEKST, LEWY/PRAWY i DŁ (długość):
-
Znajdowanie i wyodrębnianie liczby z początku –
=LEWY(A1;ZNAJDŹ(" ";A1)-1) -
Wyodrębnianie liczby z końca tekstu –
excel =PRAWY(A1;DŁ(A1)-ZNAJDŹ(" ";A1))
5. Najważniejsze funkcje tekstowe przydatne do ekstrakcji cyfr
| Funkcja | Opis | Przykład |
|---|---|---|
| LEWY | Znaki od lewej strony tekstu | =LEWY(A1;3) |
| PRAWY | Znaki od prawej strony tekstu | =PRAWY(A1;3) |
| FRAGMENT.TEKSTU | Określona ilość znaków ze środka tekstu | =FRAGMENT.TEKSTU(A1;2;4) |
| DŁ | Liczba znaków w tekstowej komórce | =DŁ(A1) |
| ZNAJDŹ/SZUKAJ.TEKST | Pozycja danego znaku w tekście | =ZNAJDŹ(" ";A1) |
| POŁĄCZ.TEKSTY/ZŁĄCZ.TEKST | Łączenie fragmentów tekstów | =POŁĄCZ.TEKSTY("",A1;A2) |
| WARTOŚĆ | Zamiana tekstu na liczby | =WARTOŚĆ(LEWY(A1;3)) |
6. Częste błędy i jak ich uniknąć
- Wynik w formie tekstu zamiast liczby — zawsze stosuj
WARTOŚĆjeśli oczekujesz wyniku liczbowego. - Najpierw przetestuj formułę na kilku komórkach, zanim skopiujesz ją do całej kolumny.
- Pamiętaj, że formuły dla wyodrębniania wszystkich cyfr wymagają od Excela wersji od 2016 (lub Office 365) oraz nie każda metoda działa w Excelu Online.
- Funkcje Power Query są niedostępne w starszych wersjach Excela.
7. Podsumowanie i wybór metody
- Proste wyciąganie z określonej pozycji – LEWY, PRAWY, FRAGMENT.TEKSTU.
- Wyciąganie wszystkich cyfr z tekstu – Zaawansowana formuła arkuszowa lub Power Query.
- Szybka ekstrakcja w dużych zbiorach – Power Query.
- Wydobycie liczb z początku/końca – Kombinacje LEWY/PRAWY/DŁ/ZNAJDŹ.
Wybór metody zależy od stopnia skomplikowania Twoich danych, wersji Excela oraz tego, czy cyfry występują zawsze w tym samym miejscu.
Stosując te techniki, skutecznie wyodrębnisz cyfry z tekstów w Excelu i zautomatyzujesz przygotowanie danych do analiz!
