Za pomocą programu Excel można podzielić informacje na mniejsze części. Znajdowanie potrzebnych danych i manipulowanie nimi jest ważnym celem dla wielu użytkowników programu Excel.
Jeśli masz pełne imię i nazwisko osoby, może być konieczne podanie tylko jej imienia lub nazwiska. Na przykład, jeśli wysyłasz przyjazną automatyczną wiadomość e-mail do swoich klientów, musisz używać ich imion, aby nie brzmieć bezosobowo. Jeśli przeglądasz listę ankietowanych, ważne może być po prostu użycie ich nazwisk lub ukrycie ich nazwisk w celu zachowania anonimowości.
Program Excel upraszcza ten proces i można zastosować wiele różnych podejść. Oto samouczek, który pomoże Ci utworzyć oddzielne kolumny Imię i Nazwisko przy użyciu formuł. Zajmujemy się również kwestią drugich imion.
Formuły programu Excel do dzielenia nazw na części
Od czego zaczynasz?
Oddzielanie imion
Oto ogólna formuła:
=LEWO(komórka;ZNAJDŹ(;komórka;1)-1)
Aby go wykonać, wymień komórka ze wskaźnikiem komórki zawierającym pierwsze imię i nazwisko, które chcesz podzielić. W tym przykładzie chcesz wybrać B2 i wprowadzić formułę:
=LEWO(A2,ZNAJDŹ(,A2,1)-1)
Należy jednak pamiętać, że na niektórych urządzeniach ta formuła używa średników zamiast przecinków. Jeśli więc powyższa formuła nie działa, możesz zamiast tego użyć następującej wersji:
=LEWO(komórka;ZNAJDŹ(;komórka;1)-1)
W tym przykładzie użyjesz:
=LEWO(A2;ZNAJDŹ(;A2;1)-1)
Teraz możesz po prostu przeciągnąć uchwyt wypełniania do końca kolumny Imię.
Funkcja LEFT pozwala oddzielić ciąg, zaczynając od lewego końca tekstu. Część ZNAJDŹ tej formuły lokalizuje pierwszą spację w pełnym imieniu, dzięki czemu otrzymujesz część Twojego pełnego imienia i nazwiska, która znajduje się przed pustą spacją.
W związku z tym imiona podzielone na łączniki pozostają razem, podobnie jak imiona zawierające znaki specjalne. Ale kolumna Pełne imię i nazwisko nie będzie zawierać drugich imion ani środkowych inicjałów.
Przecinek czy średnik?
Dlaczego formuła nie jest taka sama dla wszystkich?
W przypadku wielu użytkowników programu Excel funkcje programu Excel używają przecinków do oddzielania danych wejściowych. Ale na niektórych urządzeniach ustawienia regionalne są inne.
Aby odkryć, który symbol jest używany w programie Excel, po prostu zacznij pisać formułę. Kiedy zaczniesz, wejdź =LEWO( , zobaczysz tekst po najechaniu, który zasugeruje prawidłowe formatowanie.
Oddzielanie nazwisk
Podejmij takie samo podejście do oddzielania nazwisk. Tym razem powinieneś użyć formuły RIGHT, która oddziela ciągi zaczynające się od prawej strony.
Formuła, której potrzebujesz to:
=RIGHT(komórka, LEN(komórka) — SEARCH(#, SUBSTITUTE(komórka, , #, LEN(komórka) — LEN(SUBSTITUTE(komórka, , ))))))
W powyższym przykładzie użyjesz następującej formuły w komórce C2:
= W PRAWO (A2, LEN (A2) - SZUKAJ (#, ZAMIANA (A2,, #, LEN (A2) - LEN (ZAMIANA (A2,,))))))
Ponownie może być konieczne przełączenie się z przecinka na średnik, co oznacza, że może być konieczne użycie:
= PRAWO (A2; LEN (A2) - SZUKAJ (#; ZAMIENNIK (A2;; #; LEN (A2) - LEN (ZAMIENNIK (A2;;))))))
Dzielone nazwiska i nazwiska ze znakami specjalnymi pozostają nienaruszone.
Dlaczego ta formuła jest bardziej złożona niż ta dotycząca imion? Trudniej jest oddzielić drugie imiona i drugie inicjały od nazwisk.
Jeśli chcesz, aby drugie imiona i inicjały były wymienione wraz z nazwiskami, możesz użyć wzoru:
= PRAWO (komórka, DŁ (komórka) - SZUKAJ (, komórka))
lub:
= PRAWO (A2; DŁ (A2) - SZUKAJ (; A2))
lub:
= PRAWO (A2; DŁ (A2) - SZUKAJ (; A2))
Ale co, jeśli chcesz oddzielić drugie imiona? Jest to mniej powszechne, ale warto wiedzieć.
Rozdzielanie drugich imion
Wzór na drugie imię jest następujący:
czy możesz udostępnić ekran na niezgodzie?
=MID(komórka, SEARCH( , komórka) + 1, SEARCH( , komórka, SEARCH( , komórka)+1) – SEARCH( , komórka)-1)
W powyższym przykładzie otrzymujesz:
=MID(A2, SZUKAJ( , A2) + 1, SZUKAJ( , A2, SZUKAJ( , A2)+1) – SZUKAJ( , A2)-1)
Jeśli w programie Excel są używane średniki, formuła jest następująca:
=MID(A2; SZUKAJ( ; A2) + 1; SZUKAJ( ; A2; SZUKAJ( ; A2)+1) – SZUKAJ( ; A2)-1)
Po wprowadzeniu wzoru przeciągnij uchwyt wypełniania w dół. Oto kolumna Drugie imię dodana do powyższego przykładu:
Jeśli pełne imię i nazwisko nie zawiera drugiego imienia ani inicjału, otrzymasz w tej kolumnie wartości zerowe, które mogą być wyświetlane jako #ARG!. Aby uzyskać puste komórki zamiast #ARG !, możesz użyć funkcji JEŻELI BŁĄD.
Wtedy twoja formuła staje się:
=IFERROR(MID(komórka, SEARCH( , komórka) + 1, SEARCH( , komórka, SEARCH( , komórka)+1) – SEARCH( , komórka)-1),0)
lub:
=JEŻELIBŁĄD(ŚREDNI(A2, SZUKAJ( , A2) + 1, SZUKAJ( , A2, SZUKAJ( , A2)+1) – SZUKAJ( , A2)-1),0)
lub:
=JEŻELIBŁĄD(ŚREDNI(A2; SZUKAJ( ; A2) + 1; SZUKAJ( ; A2; SZUKAJ( ; A2)+1) – SZUKAJ( ; A2)-1);0)
Jedno podejście do rozdzielania wielu imion średnich
Co się stanie, jeśli ktoś na Twojej liście ma kilka drugich imion? Korzystając z powyższej formuły, zostanie pobrane tylko ich drugie imię.
Aby rozwiązać ten problem, możesz wypróbować inne podejście do oddzielania drugich imion. Jeśli masz utworzone kolumny z imieniem i nazwiskiem, możesz je po prostu odciąć. Wszystko, co pozostało, będzie liczyło się jako drugie imię.
Ta formuła to:
= TRIM (MID (komórka1, DŁ (komórka2) + 1, DŁ (komórka1) -LEN (komórka2 i komórka3)))
Tutaj komórka1 odnosi się do wskaźnika komórki w kolumnie Pełne imię, komórka2 odnosi się do wskaźnika komórki w kolumnie Imię, podczas gdy komórka3 odnosi się do wskaźnika komórki w kolumnie Nazwisko. W powyższym przykładzie otrzymujemy:
= TRIM (ŚREDNI (A2, DŁ (B2) + 1, DŁ (A2) -LEN (B2 & D2))))
lub:
= TRIM (ŚREDNI (A2; DŁ (B2) +1; DŁ (A2) -LEN (B2 & D2)))
Jeśli zastosujesz tę formułę, nie będziesz musiał martwić się o wartości zerowe.
Szybkie podsumowanie
Oto formuły, których możesz użyć do podzielenia pełnych nazw na części:
Imiona: =LEWO(komórka;ZNAJDŹ(;komórka;1)-1)
Nazwiska: =RIGHT(komórka, LEN(komórka) — SEARCH(#, SUBSTITUTE(komórka, , #, LEN(komórka) — LEN(SUBSTITUTE(komórka, , ))))))
Drugie imiona: =IFERROR(MID(komórka, SEARCH( , komórka) + 1, SEARCH( , komórka, SEARCH( , komórka)+1) – SEARCH( , komórka)-1),0)
Alternatywna formuła dla drugich imion: = TRIM (MID (komórka1, DŁ (komórka2) + 1, DŁ (komórka1) -LEN (komórka2 i komórka3)))
Oddzielanie imion i nazwisk bez użycia formuł
Jeśli nie masz ochoty wpisywać kilku formuł, które mogą zostać wprowadzone nieprawidłowo, skorzystaj z wbudowanego kreatora konwersji tekstu na kolumny programu Excel.
- Upewnij się, że Dane zakładka jest wybrana z menu u góry i zaznacz kolumnę, którą chcesz przekonwertować.
- Następnie kliknij Tekst do kolumn .
- Następnie upewnij się Ograniczony jest zaznaczony i kliknij Kolejny
. - Teraz wybierz Przestrzeń z opcji i kliknij Kolejny .
- Następnie zmień Miejsce docelowe do2 złi kliknij Koniec.
Efekt końcowy powinien wyglądać tak.
.
Efekt końcowy powinien wyglądać tak.
Ostatnie słowo
Istnieje wiele innych sposobów rozwiązania tego problemu w programie Excel. Jeśli żadna z dostępnych opcji nie robi tego, czego potrzebujesz, zrób więcej badań.
Korzystanie z formuł jest stosunkowo proste i nie zależy od używanej wersji programu Excel. Ale niestety nadal możesz napotkać błędy.
Na przykład, jeśli imię i nazwisko osoby zaczyna się od nazwiska, zostanie rozdzielone w odwrotny sposób. Formuły będą miały również problemy z nazwiskami, które zawierają przedrostki lub przyrostki, takie jak le Carré lub van Gogh. Jeśli imię i nazwisko osoby kończy się na Jr., zostanie wyświetlone jako nazwisko.
Istnieją jednak modyfikacje, które można dodać, aby rozwiązać te problemy, gdy się pojawiają. Praca z formułami zapewnia elastyczność niezbędną do radzenia sobie z tymi zawiłościami.
