Wyodrębnianie drugiego imienia z imienia i nazwiska w programie Excel. Połącz słowa z różnych komórek w tekst Excela. W tym filmie szczegółowo pokazano, jak podzielić tekst na kolumny

Jeśli masz zakres komórek zawierający imiona i nazwiska osób, często konieczna jest praca tylko z imionami lub tylko nazwiskami. Na przykład może zaistnieć potrzeba oddzielnego przechowywania imion i nazwisk w celu późniejszego zaimportowania ich do bazy danych, w której przechowywany jest certyfikat zawodowego księgowego. Lub może być konieczne posortowanie nazw.

Rozwiązaniem jest użycie funkcji ZNAJDŹ() w celu znalezienia spacji oddzielającej dwie części nazwy, a następnie użycie funkcji LEWO() lub PRAWO() w celu wybrania żądanej części. Aby podświetlić lewą stronę (zakładając, że znajduje się tam nazwisko), użyj następującego wzoru:
=LEWO(A2;ZNAJDŹ("",A2)-1)

Należy pamiętać, że formuła odejmuje jeden od liczby znaków wymaganych do wyróżnienia, aby zapobiec pojawieniu się spacji. Możesz użyć tej formuły w dowolnym bardziej ogólnym przykładzie, aby wyodrębnić pierwsze słowo z dowolnego ciągu. Aby podświetlić nazwę, musisz zbudować podobną formułę za pomocą funkcji PRAWY():
=PRAWY(A2,DŁUGOŚĆ(A2)-ZNAJDŹ("",A2))

Aby wyodrębnić wymaganą liczbę znaków, formuła pobiera długość słowa i odejmuje od niego pozycję spacji. Możesz użyć tej formuły w dowolnym bardziej ogólnym przykładzie, aby wyodrębnić drugie słowo z dowolnego ciągu. Na ryc. Rysunek 4.13 przedstawia wyniki pracy.

Tutaj, w kolumnie D, widzisz wynik połączenia dwóch poprzednich formuł w celu zmiany kolejności słów. Należy pamiętać, że te formuły wygenerują błąd, jeśli linia będzie zawierać tylko jedno słowo. Aby temu zapobiec, użyj funkcji IFERROR():
=JEŻELI(LEWO(A2;ZNAJDŹ("",A2)-1),A2) . W takim przypadku, jeśli komórka nie zawiera spacji, zostanie po prostu wyświetlona wartość komórki A2.

Jeśli pełne imię i nazwisko osoby, z którą współpracujesz, zawiera nazwisko, imię i drugie imię, nie będziesz mógł zastosować powyższych formuł. W tym przypadku pierwsza formuła podświetlenia nazwiska sprawdzi się dobrze, ale wybranie prawej strony nie będzie działać w ten sposób. Istnieje kilka sposobów wyjścia z tej sytuacji; przyjrzymy się rozwiązaniu za pomocą funkcji SZUKAJ() i ZNAJDŹ().

Na przykład otrzymujemy następujące imię: Bułhakow Wiktor Kirsanowicz. Zakładając, że nazwa znajduje się w komórce A2, formuła =ZNAJDŹ(” „;A2) zwróci 9, czyli pozycję pierwszego odstępu. Aby znaleźć położenie następnej spacji, należy określić położenie pierwszego odstępu plus 1: =ZNAJDŹ(" ";A2;ZNAJDŹ(" ";A2)+1).

Teraz możesz zastosować tę konstrukcję, aby znaleźć część znajdującą się najbardziej na prawo (w tym przypadku jest to drugie imię): =RIGHT(A2,LENGTH(A2)-FIND(" ";A2;ZNAJDŹ(" ",A2)+1)) .

Aby wybrać część środkową, należy określić różnicę położenia pierwszej i drugiej spacji i skorzystać ze wzoru na wybór dowolnej części linii: =PSTR(A2;ZNAJDŹ(" ";;A2)+1; ZNAJDŹ(” „;A2; ZNAJDŹ(” „; A2)+1-ZNAJDŹ(” „;A2)) . Rysunek 4.14 przedstawia wyniki pracy.

Excel dla Office 365 Excel dla Office 365 dla komputerów Mac Excel 2019 Excel 2016 Excel 2019 dla komputerów Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 dla komputerów Mac Excel 2011 dla komputerów Mac Excel Online Excel dla iPada Excel dla telefonu iPhone Excel dla tabletów z systemem Android Excel dla telefonów z systemem Android Excel Mobile Excel Starter 2010 Mniej

Załóżmy, że chcesz utworzyć jedną w pełni kwalifikowaną kolumnę z nazwą, łącząc dwie inne kolumny, imię i nazwisko. Aby połączyć imię i nazwisko, użyj funkcji CONCATENATE lub operatora ampersand (&).

Ważne: Ta funkcja w programach Excel 2016Excel Mobile i Excel Online została zastąpiona funkcją UNIA. Chociaż funkcja CONCATENATE jest nadal dostępna w celu zapewnienia kompatybilności wstecznej, od teraz należy używać funkcji COMBINES. Dzieje się tak, ponieważ funkcja CONCATENATE może nie być dostępna w przyszłych wersjach programu Excel.

Przykład

Skopiuj poniższy tekst na czystą kartkę papieru.

Nazwisko Włodzimierz Formuła Opis (wynik) Łączy powyższe nazwy oddzielone spacją (Belova) Łączy powyższe nazwy, oddzielając je średnikiem (Novikov, Pavel) POŁĄCZ.(A2;" ";B2)

Często sporządzając umowę, fakturę, ofertę handlową lub inny dokument, pracujemy z nazwiskiem, imieniem i patronimiką klienta. Czasami konieczne jest oddzielenie pełnego imienia i otrzymanie tylko imienia, aby w tekście dokumentu zwrócić się do klienta; czasami konieczne jest utworzenie kolumny z podpisującym, pozostawiając nazwisko i inicjały.

Można oczywiście napisać formułę, która wydobędzie niezbędne dane z Twojego imienia i nazwiska, ale nawet doświadczona osoba będzie potrzebować na to 5-7 minut. Co się stanie, jeśli nie znasz wcześniej lokalizacji nazwiska w tekście? Dużo łatwiej i wygodniej jest korzystać z gotowych funkcji z dodatku VBA-Excel.

Znajdź nazwisko w pełnym imieniu

Po zainstalowaniu dodatku dostępna będzie nowa funkcja = NAZWISKO(IMIĘ IMIĘ), gdzie

• Imię i nazwisko - tekst lub link do komórki zawierającej imię i nazwisko

Celem tej funkcji jest wyodrębnienie z komórki tylko nazwiska. Co więcej, kolejność nie jest dla niej ważna. Nazwisko może znajdować się na początku, na końcu lub w środku tekstu.

Znajdź imię i nazwisko w pełnym imieniu

Podobna funkcja dla nazwy =NAME(imię i nazwisko) . Wyodrębnia nazwę z tekstu zawierającego pełne imię i nazwisko, niezależnie od tego, gdzie się znajduje.

Znajdź drugie imię w pełnym imieniu

Ostatnią funkcją w tej serii jest funkcja =PATERNICAL(PEŁNE IMIĘ), służąca do wyodrębnienia drugiego imienia.

Przykłady pracy z imieniem i nazwiskiem w programie Excel Wyświetl nazwisko i inicjały

Jednym z typowych zadań podczas sporządzania dokumentów jest konwersja tekstu z formularza Nazwisko Imię Patronimiczne na tekst Nazwisko I. O. Można to rozwiązać za pomocą następującego wzoru:

NAZWISKO(A1)&" "&LEWO(IMIĘ(A1))&". "&LEWO(NAZWISKO(A1))&"."

Jest trochę długi, ale wierzcie mi, byłby znacznie krótszy i wyraźniejszy, gdybyśmy nie korzystali z funkcji z dodatku.

Kolejnym plusem jest to, że lokalizacja nazwiska w tekście nie jest dla nas istotna; funkcja wszystko ustali automatycznie.

Funkcja jako parametr otrzymuje ciąg tekstowy w postaci „Nazwisko imię patronimiczne” i obcina imię i patronimię, pozostawiając jedynie inicjały - w postaci „Nazwisko I. O”.

Funkcji tej można używać jako funkcji UDF (funkcji zdefiniowanej przez użytkownika) w komórkach arkusza programu Excel.
(patrz przykład w załączniku)

Opis funkcji funkcji - na forum: programrsforum.ru/showpost.php?p=757147&postcount=6

Nie ma dokładnego formalnego algorytmu podziału pełnego imienia i nazwiska na części.
Nie da się formalnie oddzielić nazwiska od imienia bez znajomości genealogii i języka rodzimego użytkownika języka. Można stosować jedynie różne heurystyki.

Ponadto różne władze (w różnym czasie) miały odmienne poglądy na temat tego, jak powinny wyglądać inicjały w danej sprawie.
Dokumenty Federalnej Służby Migracyjnej to jedno, rejestratorzy Banku Centralnego to drugie, a przygotowanie dokumentów handlowych to drugie.
Dlatego niektóre usługi wymagają, aby 2-3 złożone krótkie nazwy Azji Południowo-Wschodniej nie były skracane. Ale niektórzy tego nie robią.

Tak, z pytaniem, gdzie powinny znajdować się inicjały po prawej czy po lewej stronie - nie ma pewności. Nie, oczywiście, że obowiązują zasady języka rosyjskiego... Ale panują zasady obrotu gospodarczego tej czy innej struktury (albo po prostu tak, jak chce lewa tylna noga urzędnika przyjmującego dokumenty)...

Wielu może mieć pytanie: skąd się wzięło to całe zoo i po co to wszystko jest potrzebne? Z reguły wszyscy ci Don Pedros to obcokrajowcy otrzymujący rosyjski paszport na podstawie dokumentów wydanych wcześniej poza jurysdykcją Federacji Rosyjskiej. Sytuacje prawne są różne. Dość często w nowym rosyjskim paszporcie zapisana jest transliteracja na cyrylicę z języka ojczystego.
Rosyjskie paszporty z -ogly i -kyzy są obecnie wydawane w Tatarstanie. Spotkałem klienta, którego nazwisko według paszportu z 2007 roku brzmiało Mustafa Oleg ogly...

Opcja Porównaj tekst Funkcja publiczna Inicjały(y jako ciąg znaków, opcjonalnie ToLeft jako wartość logiczna = fałsz) Dim sv jako wariant, sФ jako ciąg, sИ jako ciąg, sО jako ciąg, i tak długo, k jako długi Application.Volatile True If InStr(s , "." ) > 0 Or Len(Trim$(s)) = 0 Następnie Inicjały = s "Inicjały określone jawnie lub pusty ciąg znaków Wyjście z funkcji Koniec Jeśli "Normalizuj ciąg wejściowy s = Zamień(Aplikacja.Trim(s), Chr( 30), "-" ) s = Zamień(y, " -" , "-" ), "- " , "-" ) s = Zamień(Zamień(y, "" ", """ ), " "" , """ ) " O " Henryku Aleksandrze; O” Henryk Aleksander; N” Homo; D" Treville sv = Split(s) sИ = vbNullString: sО = vbNullString: sФ = vbNullString i = UBound (sv) If i< 1 Then Инициалы = s: Exit Function Select Case sv(i) Case "оглы" , "кызы" , "заде" "бей, бек, заде, зуль, ибн, кызы, оглы, оль, паша, уль, хан, шах, эд, эль i = i - 1 sО = UCase(Left$(sv(i), 1)) & "." i = i - 1 Case "паша" , "хан" , "шах" , "шейх" i = i - 1 Case Else Select Case Right$(sv(i), 3) Case "вич" , "вна" If i >= 2 Wtedy „Standardowe zakończenie rosyjskich patronimików sО = СropWord(sv(i)) Else „Typ nazwy Bosan Slavich sИ = СropWord(sv(i)): sФ = sv(0) End If i = i - 1 Case Else k = InStr(sv(i), "-" ) Jeśli k > 0 Następnie wybierz Case Mid$(sv(i), k + 1) Case "ogly" , "kyzy" , "zade" , "ugli" , "uul " , "ool" "Wariant nasab "-ogly" i "-zade" typu Mahmud-ogly sО = UCase(Left$(sv(i), 1)) & "." i = i - 1 Jeśli i = 0 Następnie sИ = sО sО = vbNullString End If End Wybierz ElseIf i > 2 Następnie wybierz Case sv(i - 1) Case "ibn" , "ben" , "bin" sО = UCase(Left$(sv(i), 1) ) & "." " Usertal Alisher bin Suleiman i = i - 2 End Select Else " Ben Eduard sИ = UCase(Left$(sv(i), 1)) Jeśli Len(sv(i)) > 1 Wtedy sИ = sИ & " ." i = i - 1 Koniec Jeśli Koniec Wybierz Koniec Wybierz Wybierz przypadek sv(0) Przypadek "de", "del", "dos", "sept", "van", "von", "tsu" Jeśli i > = 2 Wtedy sФ = sv(0) & " " & StrConv(sv(1), vbProperCase) sИ = СropWord(sv(2)) Else "De Nikolai Jeśli Len(sИ) > 0 Wtedy sФ = sv(0 ) & " " & StrConv(sv(1), vbProperCase) Else sФ = StrConv(sv(0), vbProperCase): sИ = СropWord(sv(1)) End If End If Case Else If Len(sФ) = 0 Wtedy „Więcej nie zdefiniowało nazwiska sФ = StrConv(sv(0), vbProperCase) Jeśli Len(sИ) = 0 To sИ = СropWord(sv(1)) End If End Wybierz If ToLeft To Inicjały = sИ i sО & „ " & sФ Else Inicjały = sФ & " " & sИ i sО Funkcja końcowa Funkcja publiczna СropWord(s As Variant) Jako ciąg znaków Jeśli Len(s) = 1 Wtedy СropWord = s Else ss$ = UCase(Left$(s, 1) ) & „.” : k = InStr(s, „-” ) Jeśli k > 0 To ss$ = ss$ & „-” & Mid$(s, k + 1, 1) & „.” CropWord = ss$ Zakończ, jeśli koniec funkcji

Rozmiar załącznika

Listę w programie Excel można korygować za pomocą formuł - zastępując imię i drugie imię inicjałami, łącząc słowa z komórek w zdanie, wstawiając słowa do listy Excel.
Mamy tabelę, w której nazwisko, imię i nazwisko rodowe są zapisane w różnych komórkach. Musimy umieścić je w jednej komórce. Ręczne przepisywanie listy zajmuje dużo czasu. Ale w tabeli Excela istnieje specjalna funkcja. Istnieją dwie opcje.
Pierwsza opcja.
Mamy tę listę.
Musimy wpisać nasze pełne imię i nazwisko w komórce D1 w jednym zdaniu. W tej komórce (D1) zapisujemy formułę. Kliknij komórkę (uczyń ją aktywną).
Przejdź do zakładki „Formuły” w sekcji „Biblioteka funkcji”, wybierz „Tekst” i wybierz funkcję „CONCATENATE”. W wyświetlonym oknie wskazujemy adresy komórek, które musimy połączyć w jedno zdanie. Okazało się tak.

Imię i nazwisko zapisuje się bez spacji. Aby to naprawić, należy ulepszyć formułę. Pomiędzy adresami komórek wpisz „” po średniku. Rezultatem jest następujący wzór.
=POŁĄCZ.(A1;" ";B1;" ";C1)
Okazało się tak.
Teraz skopiuj formułę w dół kolumny.
Druga opcja.
Zamiast funkcji CONCATENATE możesz po prostu nacisnąć przycisk ampersand (&). Formuła będzie wyglądać następująco.
=A2&B2&C1
Wynik jest taki sam jak w przypadku pierwszej opcji. Jeśli między słowami nie ma spacji, wstaw spację („ ”).
Formuła będzie taka. =A2&" "&B2&" "&C2
Możesz łączyć nie tylko słowa, ale także liczby. Możesz utworzyć zdanie z danych komórkowych w programie Excel.
Możesz ustawić formuły w wymaganych komórkach formularza. Przykładowo mamy listę klientów z adresami.

Musimy złożyć propozycję. Zapisujemy formułę w komórce.
=A2&" "&B2&" "&C2&" "&"mieszka pod adresem"&" "&"g."&" "&D2&" "&"ulica."&" "&E2&" "&"d."&" "&F2& " .”
Taka była propozycja.

Tą zasadą kierujemy się przy sporządzaniu wszelkich propozycji.
Jeśli tekst w komórkach został już zapisany, ale przed istniejącymi musimy wstawić dodatkowe słowa, można to zrobić za pomocą formuły. Mamy tę listę.
Przed nazwiskami musimy wstawić słowo „Najemca”. W komórce nowej kolumny wpisujemy formułę.
="Najemca"&" "&A8
Skopiuj tę formułę w dół kolumny. Rezultatem jest następująca lista.
Pierwszą kolumnę można ukryć lub skopiować wartość nowej kolumny bez formuł, a także usunąć pierwszą kolumnę i drugą z formułami.
Aby poznać inny sposób dodawania tekstu, liczb i symboli do tekstu w komórce, zobacz artykuł „Dodawanie tekstu do komórek za pomocą tekstu Excel”.
Za pomocą formuł możesz przekonwertować listę, w której imię, drugie imię i nazwisko są zapisane w całości, na listę zawierającą nazwisko i inicjały. Na przykład komórka mówi:
W następnej kolumnie zapisujemy następującą formułę.
=CONCATENATE(LEWO(SPACJA(A1),ZNAJDŹ(" ",SPACJA(A1),1)),PSTR(SPACJA(A1),ZNAJDŹ(" ",SPACJA(A1),1)+1,1);" ";PSTR(SPACJA(A1);ZNAJDŹ(" ";SPACJA(A1); ZNAJDŹ(" ";SPACJA(A1);1)+1)+1;1);."")
Stało się.
Jeśli między słowami znajdują się dodatkowe spacje, możesz je usunąć. Więcej na ten temat przeczytasz w artykule „Jak usunąć dodatkowe spacje w Excelu”. Używając tych samych metod, możesz usunąć spacje między liczbami w formule, ponieważ Dodatkowe spacje mogą spowodować błąd w obliczeniach lub formuła nie zostanie policzona.
Możesz przenieść dane w wierszu z ostatnich komórek do pierwszej lub odwrócić wiersz. Na przykład w komórkach jest napisane: w pierwszej komórce jest Iwanow, w drugiej - Maria. Musimy wpisać Marię w pierwszej komórce, Iwanowę w drugiej. Jak to zrobić szybko na dużym stole, zobacz artykuł „”.