Formuła ogólna
=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," ")))
Podsumowanie
Aby usunąć część naturalnej złożoności tekstu (usuwanie interpunkcji, znormalizowanie wielkości liter, usunięcie dodatkowych spacji), możesz użyć formuły opartej na funkcji PODSTAW, z pomocą funkcji PRZYCINAJ i DOLNIE.
Kontekst
Może się zdarzyć, że będziesz musiał usunąć część zmienności tekstu przed innym przetwarzaniem. Jednym z przykładów jest sytuacja, gdy chcesz policzyć określone słowa w większych ciągach tekstowych. Ponieważ program Excel nie obsługuje wyrażeń regularnych, nie można konstruować dokładnych dopasowań. Na przykład, jeśli chcesz policzyć, ile razy słowo „lis” pojawia się w komórce, w końcu policzysz „lisy”. Możesz szukać słowa „lis” (ze spacją), ale to się nie powiedzie w przypadku „lis” lub „lis”. Jednym ze sposobów obejścia tego problemu jest uproszczenie tekstu za pomocą formuły w kolumnie pomocniczej, a następnie zliczanie w wersji uproszczonej. Przykład na tej stronie pokazuje jeden sposób, aby to zrobić.
Wyjaśnienie
Formuła pokazana w tym przykładzie używa serii zagnieżdżonych funkcji PODSTAW w celu usunięcia nawiasów, łączników, dwukropków, średników, wykrzykników, przecinków i kropek. Proces przebiega od wewnątrz, przy czym każde PODSTAWIENIE zastępuje jeden znak pojedynczą spacją, a następnie jest przekazywane do następnego ZAMIENNIKA. Najbardziej wewnętrzna opcja SUBSTITUTE usuwa lewy nawias, a wynik jest przekazywany do następnego SUBSTITUTE, który usuwa prawe nawiasy i tak dalej.
W poniższej wersji dodano podziały wierszy w celu zwiększenia czytelności i ułatwienia edycji zamienników. Program Excel nie dba o podziały wierszy w formułach, więc możesz użyć formuły bez zmian.
= LOWER( TRIM( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "("," "), ")"," "), "-"," "), ":"," "), ";"," "), "!"," "), ","," "), "."," ")))
Po zakończeniu wszystkich podstawień wynik jest przepuszczany przez TRIM w celu znormalizowania spacji, a następnie funkcja LOWER, aby wymusić na całym tekście małe litery.
Uwaga: musisz dostosować rzeczywiste zamienniki do swoich danych.
Dodanie spacji wiodącej i końcowej
W niektórych przypadkach możesz chcieć dodać znak spacji na początku i na końcu czyszczonego tekstu. Na przykład, jeśli chcesz dokładnie policzyć słowa, możesz chcieć poszukać słowa otoczonego spacjami (tj. Wyszukać „lis”, „mapa”), aby uniknąć fałszywych dopasowań. Aby dodać początkową i końcową spację, po prostu połącz spację („”) na początku i na końcu:
=" "&formula&" "
Gdzie „formuła” to dłuższa formuła powyżej.
