Spacje w danych z Excela psują filtrowanie, wyszukiwanie i formuły, nawet wtedy, gdy na pierwszy rzut oka wszystko wygląda poprawnie.
Rozwiązaniem jest użycie właściwej metody do konkretnego typu odstępu: zwykłej spacji, twardej spacji CHAR(160) albo spacji ukrytej na początku i końcu tekstu.
Jeśli w jednej kolumnie „Warszawa” występuje raz jako Warszawa, a raz jako Warszawa , Excel traktuje te wartości jako różne. To dlatego nie działa dopasowanie w WYSZUKAJ.X, nie zgadzają się sumy i pojawiają się duplikaty, których nie da się od razu wychwycić. Usuwanie spacji w Excelu da się zrobić szybko, ale trzeba dobrać narzędzie do rodzaju problemu. W tekście pokazano, kiedy wystarczy funkcja USUŃ.ZBĘDNE.ODSTĘPY, kiedy potrzebne jest PODSTAW, a kiedy najlepiej sięgnąć po Power Query. Do tego są gotowe formuły, skróty i sytuacje, w których jedna metoda po prostu nie zadziała.
Jak rozpoznać, z jakim typem spacji jest problem
Nie każda spacja w Excelu jest taka sama. To najważniejszy powód, dla którego jedna formuła działa w jednym arkuszu, a w drugim nie daje żadnego efektu.
W praktyce najczęściej pojawiają się 3 typy problemów:
- zwykłe spacje na początku lub końcu tekstu, np. ” Kraków” albo „Kraków „,
- wielokrotne spacje w środku, np. „Jan Kowalski”,
- tzw. twarda spacja, czyli znak o kodzie 160, często po imporcie z PDF, stron WWW albo systemów ERP.
Najprostszy test to porównanie długości tekstu funkcją DŁ. Jeśli komórka wygląda identycznie jak inna, ale DŁ(A1) zwraca inną liczbę znaków, w środku siedzi dodatkowy odstęp lub niewidoczny znak.
Funkcja USUŃ.ZBĘDNE.ODSTĘPY usuwa nadmiar zwykłych spacji, ale nie czyści twardej spacji CHAR(160). To najczęstsza pułapka przy danych kopiowanych z internetu.
Przy danych importowanych z przeglądarki Google Chrome, z plików CSV albo z raportów z SAP problem z kodem 160 pojawia się regularnie. Gdy zwykłe czyszczenie nie pomaga, trzeba przejść poziom wyżej.
Usuwanie spacji w Excelu funkcją USUŃ.ZBĘDNE.ODSTĘPY
To jest podstawowa i najszybsza metoda dla zwykłych spacji. Sprawdza się wtedy, gdy dane mają niepotrzebne odstępy przed tekstem, po tekście albo wielokrotne spacje w środku.
W polskim Excelu należy użyć funkcji:
=USUŃ.ZBĘDNE.ODSTĘPY(A1)
Ta formuła robi dokładnie 2 rzeczy: usuwa spacje z początku i końca tekstu oraz redukuje wiele spacji między wyrazami do jednej. Jeśli w A1 jest wpis ” Anna Nowak „, wynik będzie „Anna Nowak”.
Kiedy ta funkcja działa najlepiej
Najlepiej sprawdza się przy czyszczeniu nazw klientów, miast, kodów produktów i adresów e-mail, o ile problem dotyczy zwykłych odstępów wpisanych z klawiatury. W arkuszach Microsoft Excel 2016, Excel 2019 i Microsoft 365 działa tak samo.
Dobry schemat pracy wygląda tak: w kolumnie pomocniczej wpisywana jest formuła, następnie wyniki są kopiowane i wklejane jako Wartości. Dzięki temu stare dane z błędami znikają, a formuły nie zostają w tabeli na stałe.
Czego ta funkcja nie usuwa
USUŃ.ZBĘDNE.ODSTĘPY nie czyści znaków niedrukowalnych ani twardych spacji. Jeśli tekst został skopiowany z PDF albo z systemu magazynowego, efekt potrafi być zerowy mimo poprawnej składni formuły.
Właśnie dlatego po użyciu tej funkcji warto sprawdzić, czy problem rzeczywiście zniknął. Jeśli DŁ nadal pokazuje za dużo znaków, trzeba użyć innej metody.
Jak usunąć twarde spacje i niewidoczne znaki
Twarda spacja wymaga osobnego czyszczenia. To znak o kodzie 160, którego standardowe usuwanie spacji nie obejmuje.
Najskuteczniejsza formuła to połączenie dwóch funkcji:
=USUŃ.ZBĘDNE.ODSTĘPY(PODSTAW(A1;ZNAK(160);" "))
Najpierw PODSTAW zamienia twardą spację na zwykłą, a dopiero potem USUŃ.ZBĘDNE.ODSTĘPY porządkuje tekst. W praktyce to najczęściej rozwiązuje problem z danymi z portali, CRM-ów i raportów eksportowanych do CSV UTF-8.
Jeśli poza spacjami są też inne śmieciowe znaki, warto dołożyć jeszcze funkcję OCZYŚĆ:
=USUŃ.ZBĘDNE.ODSTĘPY(OCZYŚĆ(PODSTAW(A1;ZNAK(160);" ")))
To szczególnie przydaje się przy kopiowaniu danych z Adobe Acrobat, z wiadomości e-mail w Outlooku albo z tabel publikowanych na stronach urzędów.
Jeśli po skopiowaniu danych nie działa sortowanie lub dwa identycznie wyglądające rekordy nie chcą się połączyć, winna bardzo często jest spacja o kodzie 160, a nie zwykła spacja z klawiatury.
Znajdź i zamień: szybkie usuwanie spacji bez formuł
Metoda „Znajdź i zamień” jest najszybsza przy jednorazowym czyszczeniu prostych danych. Nie wymaga kolumn pomocniczych i działa od razu na zaznaczonym zakresie.
Wystarczy użyć skrótu Ctrl + H i w zależności od sytuacji:
- w polu „Znajdź” wpisać jedną spację, a w „Zamień na” nic nie wpisywać — usuwa wszystkie spacje, także te między wyrazami,
- w polu „Znajdź” wpisać dwie spacje, a w „Zamień na” jedną — redukuje wielokrotne odstępy,
- powtórzyć operację kilka razy, aż Excel pokaże 0 zamian.
To działa dobrze przy kodach typu AB 123 45, jeśli finalnie ma zostać AB12345. Nie nadaje się jednak do nazw własnych, adresów czy opisów, gdzie jedna spacja między wyrazami jest potrzebna.
Kiedy tej metody nie powinno się używać
Nigdy nie powinno się usuwać wszystkich spacji hurtowo w kolumnach z imionami, nazwiskami i adresami. Po jednej operacji z Jan Kowalski zrobi się JanKowalski, a z ul. Jana Pawła II 12 powstanie ciąg trudny do odczytania.
Przy większych zestawach danych lepiej działa formuła albo Power Query, bo daje kontrolę i możliwość odtworzenia procesu.
Power Query przy dużych plikach i cyklicznych importach
Power Query jest najlepszym rozwiązaniem dla dużych tabel i danych odnawianych co tydzień lub codziennie. Zamiast ręcznie czyścić arkusz, tworzy się proces, który potem wystarczy odświeżyć.
W Excelu Microsoft 365 oraz Excel 2019 Power Query jest dostępne w karcie Dane. Przy imporcie można zaznaczyć kolumnę tekstową i użyć poleceń:
- Przekształć → Formatuj → Przytnij — usuwa spacje z początku i końca,
- Przekształć → Formatuj → Wyczyść — usuwa znaki niedrukowalne,
- Zamień wartości — pozwala podmienić twardą spację na zwykłą.
Największa przewaga jest prosta: po podmianie pliku źródłowego nie trzeba powtarzać czyszczenia ręcznie. Wystarczy kliknąć Odśwież wszystko. Przy plikach z 10 000, 50 000 albo 200 000 wierszy różnica w czasie jest ogromna.
Kiedy Power Query wygrywa z formułami
Gdy dane są pobierane z ERP, z systemu sprzedażowego albo z comiesięcznego raportu CSV, formuły szybko rozrastają arkusz i spowalniają pracę. Power Query porządkuje dane przed załadowaniem ich do tabeli i nie zaśmieca skoroszytu dodatkowymi kolumnami.
Jeśli proces ma być powtarzalny, ta metoda wygrywa bez dyskusji.
Porównanie metod usuwania spacji w Excelu
Nie istnieje jedna metoda dobra do wszystkiego. Poniższe porównanie ułatwia wybór narzędzia zależnie od typu danych i skali pracy.
| Metoda | Usuwa zwykłe spacje | Usuwa twarde spacje 160 | Najlepsze zastosowanie | Skala danych |
|---|---|---|---|---|
| USUŃ.ZBĘDNE.ODSTĘPY | Tak | Nie | Nazwy, adresy, dane wpisane ręcznie | Od 10 do 20 000 wierszy |
| PODSTAW + ZNAK(160) | Tak, po połączeniu z USUŃ.ZBĘDNE.ODSTĘPY | Tak | Dane z PDF, WWW, CSV | Od 10 do 20 000 wierszy |
| Znajdź i zamień | Tak | Zwykle nie | Jednorazowe poprawki | Małe zakresy, np. do 5 000 komórek |
| Power Query | Tak | Tak, po zamianie wartości | Cykliczne importy i duże pliki | Od 10 000 do 200 000+ wierszy |
Najczęstsze błędy przy usuwaniu spacji
Najwięcej problemów powoduje czyszczenie danych bez sprawdzenia, jaki znak faktycznie siedzi w komórce. Wtedy łatwo dojść do wniosku, że „Excel nie działa”, choć problemem jest zły dobór metody.
Typowe błędy są cztery:
- użycie samego USUŃ.ZBĘDNE.ODSTĘPY dla danych z internetu, gdzie występuje ZNAK(160),
- usunięcie wszystkich spacji funkcją Ctrl + H w kolumnie z pełnymi nazwami,
- pozostawienie formuł zamiast wklejenia wyników jako wartości,
- czyszczenie tylko jednej kolumny, gdy porównanie odbywa się z drugą, nadal brudną kolumną.
Warto też pamiętać o regionalnych różnicach w składni. W polskim Excelu argumenty funkcji są zwykle oddzielane średnikiem ;, a nie przecinkiem ,. To drobiazg, ale potrafi zatrzymać pracę na kilka minut.
Jeśli po czyszczeniu nadal nie działa WYSZUKAJ.PIONOWO albo WYSZUKAJ.X, problem często leży nie w formule, tylko w tym, że jedna tabela ma tekst po czyszczeniu, a druga wciąż zawiera ukryte znaki.
Najczęstsze pytania
Jak usunąć wszystkie spacje w Excelu, także między wyrazami?
Najprościej użyć Ctrl + H, wpisać jedną spację w polu „Znajdź” i zostawić puste pole „Zamień na”. Ta metoda usuwa każdą spację, więc nadaje się głównie do kodów, numerów i identyfikatorów.
Dlaczego funkcja USUŃ.ZBĘDNE.ODSTĘPY nie działa?
Najczęściej dlatego, że w komórkach występuje twarda spacja ZNAK(160) albo inny znak niedrukowalny. Wtedy trzeba użyć formuły z PODSTAW i ewentualnie OCZYŚĆ.
Jak usunąć spacje z początku i końca komórki, ale zostawić jedną w środku?
Do tego służy =USUŃ.ZBĘDNE.ODSTĘPY(A1). Funkcja przycina tekst po bokach i zostawia pojedyncze odstępy między słowami.
Jak usunąć spacje w całej kolumnie naraz?
Najbezpieczniej dodać kolumnę pomocniczą z odpowiednią formułą, skopiować ją w dół, a potem wkleić wyniki jako Wartości. Przy cyklicznych plikach lepiej od razu zrobić to w Power Query.
Jak sprawdzić, czy w komórce są ukryte spacje?
Warto porównać wynik funkcji DŁ z oczekiwaną liczbą znaków albo użyć KOD/ZNAK w testach pomocniczych. Jeśli tekst wygląda dobrze, ale ma za dużo znaków, w środku jest ukryty odstęp lub znak specjalny.