Skorzystaj z tych formuł Excel do czyszczenia danych sprzedażowych, gdy przed importem do CRM chcesz wyodrębnić domeny e-mail, ujednolicić numery telefonów i uporządkować nazwy firm. Zachowaj oryginalne kolumny, a każdy wynik zapisuj w nowej kolumnie, aby łatwo sprawdzić wprowadzone zmiany.
Zacznę od kilku kłopotliwych przypadków: numeru telefonu rozpoczynającego się od zera, adresu e-mail bez separatora oraz nazwy firmy zawierającej słowo przypominające przyrostek formy prawnej. Formuła, która działa w pierwszym poprawnym wierszu, po skopiowaniu w dół może uszkodzić takie dane.
W tym poradniku omówimy trzy zadania:
- Wyodrębnianie domen z adresów e-mail.
- Czyszczenie numerów telefonów z zachowaniem oryginalnych cyfr.
- Usuwanie sprawdzonego przyrostka nazwy firmy bez kasowania słów wewnątrz nazwy.
📌 Podsumowanie dla zabieganych
Przygotuj kolumny z danymi sprzedażowymi do kontrolowanego importu do CRM za pomocą trzech precyzyjnych przekształceń w Excelu. Zachowaj wartości źródłowe obok wyników i sprawdź wyjątki, zanim cokolwiek zastąpisz.
- Dane wejściowe: adresy e-mail, numery telefonów zapisane jako tekst i nazwy firm.
- Wynik: wyodrębnione domeny, numery telefonów bez wybranych znaków interpunkcyjnych oraz nazwy firm oczyszczone według jasno określonej reguły przyrostków.
Jak wyodrębnić domeny z adresów e-mail
W Excelu domeny z listy adresów e-mail można wyodrębnić na dwa sposoby:
Pierwszy sposób to funkcja TEXTAFTER, dostępna w Microsoft 365, Excelu dla sieci Web i Excelu 2024. Sprawdź aktualną dokumentację funkcji Microsoft, aby upewnić się, że obsługuje ją Twoja wersja programu.
Drugi sposób to połączenie funkcji RIGHT, LEN i FIND — dla wszystkich, którzy nie mogą skorzystać z funkcji „TEXTAFTER”.
Aby wyodrębnić domenę z adresu e-mail:
- Wpisz formułę
=TEXTAFTERw polu formuły - Wybierz komórkę zawierającą pierwszy adres e-mail (np. B3)
- Dodaj
@jako wyszukiwany tekst
Formuła w Excelu będzie wyglądać tak:
=TEXTAFTER(B3,"@")
Aby zastosować formułę do wszystkich adresów e-mail, przeciągnij uchwyt w prawym dolnym rogu komórki z pierwotną formułą.
Wyodrębnianie domeny w starszej wersji Excela
Teraz pokażę, jak wyodrębnić domenę z adresu e-mail w starszej wersji Excela, która nie obsługuje funkcji „TEXTAFTER”.
Użyjemy formuły =RIGHT(B3,LEN(B3)-FIND("@",B3)).
Wklej ją w polu formuły i zastosuj do komórki zawierającej pierwszy adres e-mail.
Jak działa ta formuła? Funkcja RIGHT pobiera znaki od końca adresu e-mail, a FIND i LEN wspólnie ustalają, ile dokładnie znaków należy wyodrębnić.
Weźmy jako przykład john.doe123@abc.com — ten adres ma łącznie 19 znaków:
LEN(B3) zwraca 19 znaków.
Funkcja FIND wyszukuje symbol @ w adresie john.doe123@abc.com i wskazuje, że jest on 12. znakiem:
FIND("@",B3) zwraca 12.
Odejmujemy 12 od 19, a otrzymana wartość 7 trafia do funkcji RIGHT.
Excel pobiera więc siedem ostatnich znaków i zwraca wynik abc.com.
Aby zastosować formułę do wszystkich komórek, przeciągnij w dół uchwyt w prawym dolnym rogu komórki z pierwotną formułą.
Jeśli wolisz pracować z plikiem CSV, możesz wyodrębnić domeny w Datablist, a następnie porównać wyniki z kolumną źródłową.
Sprawdź wyjątki przed skopiowaniem formuły
TEXTAFTER(B3,"@") zwraca błąd, jeśli brakuje separatora. Przykładowa formuła ułatwiająca kontrolę to =IFERROR(LOWER(TRIM(TEXTAFTER(B3,"@"))),"Sprawdź e-mail"). Wyodrębnia ona tekst, ale nie potwierdza, czy adres istnieje ani czy można na niego dostarczyć wiadomość. Odrzucaj wiersze zawierające kilka adresów lub kilka znaków @, zamiast uznawać wyodrębnioną końcówkę za wiarygodną domenę firmy.
Przykładowo alex@example.com zwróci example.com, natomiast alex.example.com wymaga sprawdzenia. Zachowaj pełny adres e-mail obok domeny, aby dwie osoby z tej samej firmy nie zostały przypadkowo scalone w jeden kontakt.
Czyszczenie i formatowanie numerów telefonów
Teraz wyczyścimy i sformatujemy numery telefonów w dwóch krokach.
Pierwszy krok polega na oczyszczeniu numerów za pomocą następującej formuły:
=TEXTJOIN("",TRUE,TEXTSPLIT(B3,{"(",")","-"," ","."},,TRUE))
Usuwa ona wyłącznie wymienione znaki i zwraca tekst. Nie dodawaj +0 ani nie konwertuj wyniku na liczbę w inny sposób: spowoduje to usunięcie zer wiodących oraz międzynarodowego znaku +. Oryginalną kolumnę z telefonami również importuj jako tekst — formuła nie odzyska zer, które Excel już usunął. Microsoft opisuje to zachowanie w poradniku dotyczącym zer wiodących.
Funkcja TEXTSPLIT wymaga obsługiwanej wersji Excela. W tym przykładzie użyto angielskich nazw funkcji i przecinków jako separatorów argumentów; lokalna wersja programu może stosować inne nazwy lub separatory. Formuła nie weryfikuje kodów krajów, numerów wewnętrznych ani tego, czy numer jest aktywny.
Tak wygląda zastosowanie tej formuły:
Zastosuj formułę do wszystkich numerów, przeciągając w dół uchwyt w prawym dolnym rogu pierwotnej komórki, jak pokazano poniżej.
Przykładowo tekst 020 7946 0958 zmieni się w 02079460958, a +44 (20) 7946-0958 w +442079460958. Numery wewnętrzne przechowuj osobno, a litery i nieoczekiwane znaki oznaczaj do sprawdzenia. Numer telefonu jest identyfikatorem, a nie wartością służącą do obliczeń.
Poniższe zrzuty ekranu przedstawiają opcjonalny amerykański format wyświetlania. Używaj go wyłącznie dla zweryfikowanych, dziesięciocyfrowych numerów z USA i tylko w osobnej kolumnie. W eksporcie międzynarodowym i imporcie do CRM zachowaj oczyszczony tekst — ten etap nie służy do międzynarodowej normalizacji numerów.
Utwórz osobną kolumnę o nazwie Sformatowane numery telefonów.
Wybierz komórkę pod nagłówkiem i użyj skrótu „Command” + „1” w Excelu, aby otworzyć wszystkie opcje formatowania.
Wybierz opcję formatowania „Special”.
Wybierz „Phone Numbers” oraz kraj, z którego pochodzą numery.
💡 Ważne
Formaty liczb w Excelu zmieniają jedynie sposób wyświetlania komórek liczbowych. Nie dodają brakującego kodu kraju ani nie weryfikują numeru telefonu. W przypadku listy międzynarodowej zachowaj wartości tekstowe i sprawdź dane o kraju, zanim wybierzesz osobny proces czyszczenia telefonów.
Teraz zamknij panel i sprawdź, czy Excel zastosował format.
Wystarczy upewnić się, że w polu formatu przy tej kolumnie widnieje „Special”. Odpowiednie miejsce zaznaczono na poniższym obrazie.
Ostatni krok to wpisanie w tej kolumnie formuły, która przeniesie oczyszczone numery do nowej kolumny przygotowanej do wyświetlania numerów z USA.
W tym przykładzie, przeznaczonym wyłącznie do wyświetlania sprawdzonych numerów z USA, formuła =VALUE(D3) przekazuje do kolumny wartość liczbową. Komórkę D3 pozostaw bez zmian jako tekst. Samo odwołanie =D3 nadal zwraca tekst, więc nie zostanie objęte liczbowym formatem numeru telefonu.
Po wpisaniu formuły naciśnij „ENTER”, aby ją zastosować.
Jeśli wszystko zostało wykonane poprawnie, wynik powinien wyglądać następująco:
Przeciągnij uchwyt w prawym dolnym rogu komórki w dół, aby zastosować formatowanie do wszystkich numerów telefonów w Excelu, jak pokazano poniżej:
Czyszczenie nazw firm
Czyszczenie nazw firm to jedno z najważniejszych zadań, które warto wykonać przed rozpoczęciem Outboundu do potencjalnych klientów. Dlatego nie mogło go zabraknąć w tym poradniku.
Stosuj regułę usuwania przyrostków, którą łatwo wyjaśnić. Funkcja SUBSTITUTE zastępuje pasujący tekst wszędzie, gdzie go znajdzie. Przykładowo =SUBSTITUTE("Acme Consulting"," Co","") zwróci Acmensulting, usuwając część właściwej nazwy. Dopasowanie rozróżnia wielkość liter: użycie " co" pozostawi nazwę Acme Consulting bez zmian, chyba że wcześniej zamienisz tekst źródłowy na małe litery. Łańcuch kilku zamian zależy także od ich kolejności. Nie należy stosować tak szerokiego zestawu zamian do kolumny przeznaczonej do CRM.
Jeśli zatwierdzona reguła brzmi usuń znajdujący się na końcu odstęp i skrót LLC, zastosuj:
=IF(RIGHT(TRIM(B4),4)=" LLC",TRIM(LEFT(TRIM(B4),LEN(TRIM(B4))-4)),TRIM(B4))
Ta reguła jest celowo precyzyjna. Acme LLC zmieni się w Acme, natomiast Acme LLC Services i Acme Consulting pozostaną bez zmian. Formuła nie obejmuje zapisu L.L.C., wszystkich form prawnych ani wszystkich krajów. Jeśli wymagają tego Twoje dane, dodaj osobno sprawdzone reguły.
Poniższe zrzuty ekranu przedstawiają starszy przykład oparty na szerokim zastępowaniu tekstu. W wyniku końcowym zastosuj podaną wyżej precyzyjną regułę i przed zaakceptowaniem zmiany porównaj rezultat z oryginalną nazwą.
Jak działa bardziej precyzyjna formuła: TRIM usuwa standardowe spacje z początku i końca tekstu, RIGHT sprawdza cztery ostatnie znaki, a LEFT usuwa wyłącznie zweryfikowane zakończenie. W standardowych formułach Excela porównanie nie rozróżnia wielkości liter. Nie zakładaj, że TRIM usuwa każdy znak odstępu Unicode.
Tak wygląda efekt zastosowania formuły czyszczącej nazwę firmy:
Nie zapomnij przeciągnąć w dół uchwytu w prawym dolnym rogu komórki z pierwotną formułą, aby zastosować ją do wszystkich nazw firm, jak pokazano poniżej:
Pozostaje jeszcze jedna kwestia: nazwy firm nie są zapisane wielkimi literami na początku wyrazów.
Używaj =PROPER(D4) tylko wtedy, gdy oczekiwanym wynikiem jest zapis każdego wyrazu wielką literą. Funkcja zmienia IBM na Ibm, dlatego w razie potrzeby zachowaj oficjalną pisownię marki lub dodaj konkretne wyjątki. Zrzuty ekranu pokazują przekształcenie, ale nie gwarantują uzyskania poprawnej nazwy handlowej.
Tak wygląda prawidłowo wykonane przekształcenie:
Na koniec przeciągnij uchwyt w prawym dolnym rogu komórki w dół, aby zastosować formułę do całej listy nazw firm.
Jeśli masz już listę zatwierdzonych starych i nowych nazw, skorzystaj z procesu zamiany wielu wartości, aby zastosować te mapowania.
W przypadku bardziej zróżnicowanych nazw alternatywę znajdziesz w poradniku czyszczenia nazw firm z użyciem AI. Niezależnie od wybranej metody zachowaj oryginalne wartości oraz sprawdź przyrostki form prawnych, akronimy i niejednoznaczne przypadki.




















