Usuwanie spacji za pomocą TRIM

Dane skopiowane ze stron internetowych, plików PDF lub innych systemów często zawierają nadmiarowe spacje. Przed nazwą może znajdować się spacja, między słowami mogą występować dwie spacje, a na końcu tekstu mogą znajdować się spacje, których nie da się dostrzec.

Te niewidoczne spacje zakłócają wyszukiwanie, sortowanie i dopasowywanie. Funkcja TRIM usuwa je, dzięki czemu tekst zachowuje się w przewidywalny sposób.

Funkcja TRIM usuwa spacje z tekstu zgodnie z prostą zasadą:

• Usuwa wszystkie spacje z początku i końca
• Redukuje każdy ciąg spacji między słowami do jednej spacji

Dlatego TRIM(" Hello world ") zwraca uporządkowany tekst Hello world z dokładnie jedną spacją pośrodku i bez spacji na początku ani na końcu.

W praktyce funkcję TRIM stosuje się do wskazanej komórki zawierającej nieuporządkowany tekst. Jeśli A2 zawiera   John Smith   z dodatkowymi spacjami, ta formuła zwróci uporządkowaną wersję:

Wynikiem będzie John Smith bez spacji na początku i na końcu. Formułę należy umieścić w nowej kolumnie, a następnie skopiować i wkleić same wartości z powrotem na oryginalne dane, jeśli mają one zostać zachowane w uporządkowanej postaci.

Częsta obawa brzmi: czy funkcja TRIM usuwa wszystkie spacje? Nie. Zachowuje pojedyncze spacje między słowami, ponieważ mają one znaczenie.

TRIM("New York") zwraca New York — trzy spacje zostają zredukowane do jednej. Zdania i nazwy pozostają czytelne; usuwany jest tylko nadmiar.

Spacja na końcu jest niewidoczna, ale rzeczywiście istnieje. Teksty Apple i Apple  (z końcową spacją) wyglądają identycznie, jednak arkusz kalkulacyjny traktuje je jako różne wartości.

Powoduje to nieudane wyszukiwanie i przepuszczanie duplikatów. Zastosowanie funkcji TRIM do obu kolumn przed ich porównaniem usuwa to ukryte niedopasowanie.

Można potwierdzić obecność nadmiarowych spacji w komórce, porównując jej długość przed zastosowaniem funkcji TRIM i po nim.

Jeśli LEN(A2) jest większe niż LEN(TRIM(A2)), komórka zawiera spacje, które funkcja TRIM usunęłaby. Ta formuła zwraca liczbę dodatkowych znaków spacji ukrytych w komórce.

Często stosowanym rozwiązaniem jest opakowanie szukanej wartości w funkcję TRIM, aby pojedyncza dodatkowa spacja nie powodowała braku dopasowania.

W tym przypadku wyszukiwany tekst w A2 jest oczyszczany funkcją TRIM przed rozpoczęciem wyszukiwania go przez VLOOKUP w tabeli. Nawet jeśli A2 zawierałoby spacje na końcu, wyszukiwanie dopasuje teraz wartość pozbawioną tych spacji w tabeli.

Funkcja TRIM obsługuje tylko zwykły znak spacji. Tekst pobrany z sieci może czasami zawierać spację nierozdzielającą — inny niewidoczny znak, którego funkcja TRIM nie usuwa.

Aby go usunąć, należy najpierw zamienić go na zwykłą spację za pomocą funkcji SUBSTITUTE i kodu znaku 160, a następnie zastosować funkcję TRIM. Połączenie tych dwóch funkcji usuwa niemal każdy uporczywy problem z odstępami.

Zaimportowany tekst może również zawierać ukryte znaki niedrukowalne, takie jak podziały wiersza. Funkcja CLEAN usuwa te znaki, natomiast TRIM obsługuje spacje.

Użycie obu funkcji razem zapewnia dokładnie oczyszczony tekst. Najpierw należy zastosować funkcję CLEAN, aby usunąć znaki sterujące, a następnie TRIM, aby uporządkować pozostałe odstępy.

Funkcja TRIM działa zarówno w programie Excel, jak i w Google Sheets, a jej cel jest taki sam. Istnieje jednak jedna różnica: funkcja TRIM w Excelu ignoruje spacje nierozdzielające, natomiast funkcja TRIM w Google Sheets obsługuje również niektóre z nich.

Dla bezpieczeństwa w obu narzędziach niezawodnym sposobem czyszczenia tekstu wklejonego z sieci jest użycie funkcji SUBSTITUTE z CHAR(160).

Formuła TRIM wyświetla oczyszczony tekst, ale pierwotna komórka z nieuporządkowanymi danymi nadal istnieje. Aby zastąpić ją na stałe, należy skopiować komórki zawierające formułę, a następnie użyć opcji Wklej specjalnie → Wartości na oryginalnej kolumnie.

W ten sposób wyniki formuł zostaną zamienione na zwykły tekst, który można zachować, a następnie usunąć kolumnę pomocniczą. Dane będą teraz trwale oczyszczone, a nie tylko wyświetlane w oczyszczonej postaci.

Sprawdź swoją wiedzę na temat TRIM.

W tym rozdziale nauczono się porządkować nieuporządkowane odstępy w tekście.

• TRIM(text) usuwa spacje na początku i na końcu
• Wewnętrzne ciągi spacji są redukowane do jednej spacji
• Spacje na końcu niezauważalnie powodują błędy wyszukiwania i dopasowywania
• Za pomocą LEN minus LEN(TRIM()) można wykrywać ukryte spacje
• W przypadku tekstu wklejanego z sieci funkcję można łączyć z CLEAN i SUBSTITUTE(CHAR(160))

Ukończono część dotyczącą funkcji tekstowych służących do wyodrębniania, mierzenia i oczyszczania tekstu. Następnie przedstawione zostaną sposoby łączenia i przekształcania tekstu.