Jak usunąć polskie znaki z tekstu

W mojej pracy zdarzyło się, że musiałem polskich liter pozbawić nazwy produktów w sklepie internetowych by podstawić je do linków tworzonych przez system zarządzania sklepem.

Potrzebowałem tego również by na podstawie imion i nazwisk wygenerować adresy email.

Zobacz plik roboczy.

Usuwanie polskich znaków bez zważania na wielkość liter

W obu powyższych zastosowaniach mogłem zignorować kwestię wielkości liter. Zarówno linki jak i maile nie biorą ich pod uwagę. Użyłem następującej formuły.

=lower(join("";ifna(
ArrayFormula(VLOOKUP(ArrayFormula(mid(B2;sequence(len(B2));1));{"ą"\"a";"ć"\"c";"ę"\"e";"ł"\"l";"ń"\"n";"ź"\"z";"ż"\"z";"ś"\"s";"ó"\"o"};2;FALSE));
ArrayFormula(mid(B2;sequence(len(B2));1))
)))

Rozbierzmy tą formułę by zrozumieć o co chodzi.

Najbardziej wewnętrzna (a więc uruchomiana jako pierwsza) jest sekwencja:

ArrayFormula(mid(B2;sequence(len(B2));1)) – bierze ona po kolei wszystkie znaki w badanym ciągu (B2). Formuła mid bierze z komórki najpierw znak pierwszy, potem drugi, trzeci, etc. Tą kolejność wyznacza jej funkcja zapętlająca sequence przyjmująca wartości od 1 do wartości odpowiadającej długości ciągu w B2, czyli len(B2). Poprzez zastosowanie arrayformula możemy wykonać operację wiele razy.

Jeśli puścimy ją samodzielnie, jej efekt będzie następujący – powstanie tabela:

Skoro mamy tabelę, to możemy przy pomocy formuły vlookup przejść po niej i przypisać każdej polskiej literze jej odpowiednik bez ogonka. Żeby nie zajmować miejsca w komórkach, tablica przypisań jest umieszczona w samej formule.

ArrayFormula(VLOOKUP(ArrayFormula(MID(B2;sequence(len(B2));1));{"ą"\"a";"ć"\"c";"ę"\"e";"ł"\"l";"ń"\"n";"ź"\"z";"ż"\"z";"ś"\"s";"ó"\"o"};2;FALSE))

To co widzimy w nawiasie klamrowym jest odpowiednikiem tabeli wyglądającej tak:

Przesukujemy tabelę stworzoną z rozebranego zdania i za każdym razem, gdy znajdziemy polską literę to podstawiamy wartość z drugiej kolumny stworzonej tabeli przypisań.

Jeśli nie będzie polskiej litery, wyskoczy błąd N/A, wówczas zostanie przepisana litera taka jaka była. Do tego jest wykorzystywana formuła IFNA – za każdym razem kiedy jest błąd, jest uruchamiana formuła: ArrayFormula(mid(B2;sequence(len(B2));1)) , która wpisuje aktualnie badany znak.

Na koniec używam funkcji join by złożyć tabelę w jedną komórkę, oraz lower, która zamienia wszystkie wielkie litery na małe.

Dlaczego nie rozróżnia wielkości liter?

Wynika to z cech formuły vlookup, która nie jest wrażliwa na wielkość liter. Dla niej Ą i ą są identyczne.

Usuwanie polskich znaków z zachowaniem wielkości liter

Nowa formuła jest bardzo podobna, natomiast zamiast znaków porównujemy ich numery w tablicy Unicode (to tabela w której każdemu znakowi jest przypisany numer. Zawiera zarówno małe, wielkie litery, wszelkie “ogonki”, a także znaki specjalne i pokaźny zbiór emotikonów).

Literze Ą odpowiada numer 260, a ą 261. Tworzymy więc tablicę, w której określimy jakim kodom mają odpowiadać litery “bez ogonków”. W standardowym zapisie wygląda ona tak:

Gotowa formuła wygląda tak:

=join("";ifna(
ArrayFormula(VLOOKUP(ArrayFormula(code(MID(B3;sequence(len(B3));1)));{260\"A";262\"C";280\"E";321\"L";323\"N";211\"O";379\"Z";377\"Z";346\"S";261\"a";263\"c";281\"e";322\"l";324\"n";378\"z";380\"z";347\"s";243\"o"};2;FALSE));
ArrayFormula(MID(B3;sequence(len(B3));1))
))

W porównaniu z poprzednią jest dodana formuła code(), która zamienia znaki na kody Unicode, oraz tabela.

Działa to tak:

Jak podmieniać inne znaki?

W przypadku konstruowania linków, może być konieczne zamienianie spacji na myślniki lub podkreślenia. Mogą się też trafić inne znaki diakrytyczne. W tej sytuacji należy poszerzyć tablicę używając znaku \ by rozdzielać znaki w jednej kolumnie i ; by przechodzić do kolejnego wiersza. Jeśli chcemy poznać kod odpowiadający znakowi, wystarczy że wpiszemy =code("tu znak") i arkusz wyświetli odpowiedni kod.