Izdvajanje srednjeg imena iz punog imena u Excelu. Kombinirajte riječi iz različitih ćelija u Excel tekst. Ovaj videozapis detaljno pokazuje kako podijeliti tekst u stupce

Ako imate niz ćelija koje sadrže imena i prezimena ljudi, često je potrebno raditi samo s imenom ili samo s prezimenom. Na primjer, možda ćete morati odvojeno pohraniti ime i prezime za kasniji uvoz u bazu podataka u kojoj je pohranjen vaš certifikat profesionalnog računovođe. Ili ćete možda morati sortirati imena.

Rješenje je korištenje funkcije FIND() za pronalaženje razmaka koji razdvaja dva dijela imena, a zatim korištenje funkcije LEFT() ili RIGHT() za odabir željenog dijela. Da biste istaknuli lijevu stranu (pod pretpostavkom da je tamo prezime), upotrijebite sljedeću formulu:
=LIJEVO(A2,TRAŽI("",A2)-1)

Imajte na umu da formula oduzima jedan od broja znakova potrebnih za označavanje kako bi se spriječilo pojavljivanje razmaka. Ovu formulu možete koristiti za bilo koji općenitiji primjer za izdvajanje prve riječi iz bilo kojeg niza. Da biste istaknuli naziv, trebate izraditi sličnu formulu pomoću funkcije RIGHT():
=DESNO(A2,DULJINA(A2)-TRAŽI("",A2))

Kako bi izdvojila potreban broj znakova, formula uzima duljinu riječi i od nje oduzima položaj razmaka. Ovu formulu možete koristiti za bilo koji općenitiji primjer za izdvajanje druge riječi iz bilo kojeg niza. Na sl. Na slici 4.13 prikazani su rezultati rada.

Ovdje u stupcu D vidite rezultat kombiniranja dvije prethodne formule za promjenu redoslijeda riječi. Imajte na umu da će ove formule generirati pogrešku ako redak sadrži samo jednu riječ. Da biste to spriječili, koristite funkciju IFERROR():
=IFERROR(LEFT(A2,FIND("",A2)-1),A2) . U tom slučaju, ako ćelija ne sadrži razmak, jednostavno će se prikazati vrijednost ćelije A2.

Ako puno ime osobe s kojom radite sadrži prezime, ime i srednje ime, nećete moći primijeniti gornje formule. U ovom slučaju, prva formula za isticanje prezimena će raditi dobro, ali odabir desne strane neće raditi na ovaj način. Postoji nekoliko načina za izlazak iz ove situacije; pogledat ćemo rješenje pomoću funkcija SEARCH() i FIND().

Na primjer, dano nam je sljedeće ime: Bulgakov Viktor Kirsanovich. Pod pretpostavkom da je ime u ćeliji A2, formula =FIND(" ";A2) vratit će 9, položaj prvog razmaka. Da biste pronašli položaj sljedećeg razmaka, morate navesti položaj prvog razmaka plus 1: =FIND(" ";A2;FIND(" ";A2)+1).

Sada možete primijeniti ovu konstrukciju da pronađete krajnji desni dio (u ovom slučaju to je srednje ime): =RIGHT(A2,LENGTH(A2)-FIND(" ";A2;FIND(" ",A2)+1)) .

Za odabir središnjeg dijela potrebno je odrediti razliku u položaju prvog i drugog razmaka i upotrijebiti formulu za odabir iz bilo kojeg dijela retka: =PSTR(A2;FIND(" ";;A2)+1; FIND(" ";A2; FIND(" "; A2)+1-FIND(" ";A2)) Na slici 4.14 prikazani su rezultati rada.

Excel za Office 365 Excel za Office 365 za Mac Excel 2019 Excel 2016 Excel 2019 za Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 za Mac Excel za Mac 2011 Excel Online Excel za iPad Excel za iPhone Excel za Android tablete Excel za Android telefone Excel Mobile Excel Starter 2010 Manje

Recimo da želite stvoriti jedan potpuno kvalificirani stupac imena spajanjem dva druga stupca, imena i prezimena. Za kombiniranje imena i prezimena upotrijebite funkciju CONCATENATE ili operator ampersand (&).

Važno: Ova funkcija u Excelu 2016Excel Mobile i Excel Online zamijenjena je funkcijom UNION. Iako je funkcija CONCATENATE još uvijek dostupna za kompatibilnost s prethodnim verzijama, od sada bi se trebalo koristiti COMBINES. To je zato što CONCATENATE možda neće biti dostupan u budućim verzijama Excela.

Primjer

Kopirajte sljedeće na prazan list papira.

Prezime Vladimire Formula Opis (rezultat) Kombinira gornja imena odvojena razmakom (Belova) Kombinira gornja imena, odvajajući ih točkom i zarezom (Novikov, Pavel) SPAJANJE(A2;" ";B2)

Često, prilikom sastavljanja ugovora, fakture, komercijalnog prijedloga ili drugog dokumenta, radimo s prezimenom, imenom i patronimom klijenta. Ponekad je potrebno odvojiti puno ime i prezime kako bi se u tekstu dokumenta oslovilo klijenta, ponekad je potrebno napraviti rubriku s potpisnikom, ostavljajući prezime i inicijale.

Naravno, možete napisati formulu koja će izdvojiti potrebne podatke iz vašeg punog imena, ali čak i iskusnoj osobi trebat će 5-7 minuta za to. Što ako mjesto prezimena u tekstu ne znate unaprijed? Mnogo je lakše i praktičnije koristiti gotove funkcije iz VBA-Excel dodatka.

Pronađite prezime u punom imenu

Nakon instaliranja dodatka bit će dostupna nova funkcija = PREZIME(PUNO IME), gdje

• Puno ime - tekst ili poveznica na ćeliju koja sadrži puno ime

Svrha ove funkcije je izdvojiti samo prezime iz ćelije. Štoviše, redoslijed joj nije bitan. Prezime može stajati na početku, na kraju ili u sredini teksta.

Pronađite ime u punom imenu

Slična funkcija za ime =NAME(puno ime) . Izvlači ime iz teksta s punim imenom, bez obzira gdje se nalazi.

Pronađite srednje ime u punom imenu

Posljednja funkcija u ovoj seriji je funkcija =PATERNICAL(PUNO IME) za izdvajanje srednjeg imena.

Primjeri rada s punim imenom u Excelu Prikaz prezimena i inicijala

Jedan od uobičajenih zadataka pri izradi dokumenata je pretvaranje teksta oblika Prezime Ime Patronim u tekst Prezime I. O. To možete riješiti pomoću sljedeće formule:

PREZIME(A1)&" "&LEFT(IME(A1))&". "&LEFT(PATERNAME(A1))&"."

Nešto je dugačak, ali vjerujte, bio bi puno kraći i pregledniji da ne koristimo funkcije iz dodatka.

Još jedan plus je što mjesto prezimena u tekstu nije važno za nas, funkcija će sve odrediti automatski.

Funkcija prima kao parametar tekstualni niz u obliku "Prezime ime patronim", te odsijeca ime i patronim ostavljajući samo inicijale - u obliku "Prezime I. O."

Ova se funkcija može koristiti kao UDF (korisnički definirana funkcija) u ćelijama radnog lista programa Excel.
(vidi primjer u prilogu)

Opis značajki funkcije - na forumu: programmersforum.ru/showpost.php?p=757147&postcount=6

Ne postoji točan formalni algoritam za dijeljenje punog imena na dijelove.
Nemoguće je formalno odvojiti prezime od imena bez poznavanja genealogije i jezika izvornog govornika. Možete koristiti samo razne heuristike.

Osim toga, različita su tijela (u različito vrijeme) imala različita stajališta o tome kako bi inicijali trebali izgledati u određenom slučaju.
Jedno su dokumenti Federalne službe za migracije, drugo su matičari Središnje banke, a drugo je priprema komercijalnih dokumenata.
Stoga neke usluge zahtijevaju da se 2-3 složena kratka imena jugoistočne Azije ne skraćuju. Ali neki ne.

Da, s pitanjem gdje bi inicijali trebali biti desno ili lijevo - nema jednoznačnosti. Ne, naravno da postoje pravila ruskog jezika... Ali prevladavaju pravila poslovnog prometa jedne ili druge strukture (ili jednostavno kako hoće zadnja lijeva noga službenika koji prima dokumente)...

Mnogi bi mogli imati pitanje: odakle dolazi cijeli ovaj zoološki vrt i zašto je sve to potrebno? U pravilu su svi ti Don Pedri strani državljani koji dobivaju rusku putovnicu na temelju dokumenata koji su prethodno izdani izvan jurisdikcije Ruske Federacije. Pravne situacije su različite. I vrlo često se u novu rusku putovnicu upisuje transliteracija na ćirilicu s materinjeg jezika.
Ruske putovnice s -ogly i -kyzy sada se izdaju u Tatarstanu. Upoznao sam klijenta čije je ime prema putovnici iz 2007. bilo Mustafa Oleg ogly...

Opcija Compare Text Public Function Initials(s As String , Optional ToLeft As Boolean = False ) Dim sv As Variant , sF As String , sI As String , sO As String , i As Long , k As Long Application.Volatile True If InStr(s , "." ) > 0 Ili Len(Trim$(s)) = 0 Then Initials = s "Iniciali specificirani eksplicitno ili prazan niz Exit Function End If "Normaliziraj ulazni niz s = Replace(Application.Trim(s), Chr( 30), "-" ) s = Zamijeni(Zamijeni(e, " -" , "-") , "- " , "-") s = Zamijeni(Zamijeni(e, "" " , """) , " "" , """ ) " O "Henry Alexander; O" Henry Alexander; N" Homo; D" Treville sv = Split(s) sI = vbNullString: sO = vbNullString: sF = vbNullString i = UBound (sv) Ako 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 Zatim "Standardni završetak za ruska patronimika sO = SropWord(sv(i)) Else "Ime tipa Bosan Slavich sI = SropWord(sv(i)): sF = sv(0) End If i = i - 1 Case Else k = InStr(sv(i), "-" ) Ako je k > 0, onda odaberite slučaj Mid$(sv(i), k + 1) slučaj "ogly", "kyzy", "zade", "ugli", "uul " , "ool" "Varijanta nasab "-ogly" i "-zade" tipa Mahmud-ogly sO = UCase(Left$(sv(i), 1)) & "." i = i - 1 Ako je i = 0 Zatim sI = sO sO = vbNullString End If End Odaberite ElseIf i > 2 Zatim Odaberite Case sv(i - 1) Case "ibn", "ben", "bin" sO = UCase(Left$(sv(i), 1) ) & "." " Usertal Alisher bin Suleiman i = i - 2 End Select Else " Ben Eduard sI = UCase(Left$(sv(i), 1)) If Len(sv(i)) > 1 Then sI = sI & " ." i = i - 1 End If End Select End Select Select Case sv(0) Case "de" , "del" , "dos" , "sept" , "van" , "von" , "tsu" If i > = 2 Tada je sF = sv(0) & " " & StrConv(sv(1), vbProperCase) sI = SropWord(sv(2)) Else "De Nikolai Ako je Len(sI) > 0 Tada je sF = sv(0 ) & " " & StrConv(sv(1), vbProperCase) Else sF = StrConv(sv(0), vbProperCase): sI = SropWord(sv(1)) End If End If Case Else If Len(sF) = 0 Then "More nije definirao prezime sF = StrConv(sv(0), vbProperCase) If Len(sI) = 0 Then sI = SropWord(sv(1)) End If End Select If ToLeft Then Initials = sI & sO & " " & sF Else Initials = sF & " " & sI & sO End Function Javna funkcija SropWord(s As Variant ) As String If Len(s) = 1 Then SropWord = s Else ss$ = UCase(Left$(s, 1) ) & "." : k = InStr(s, "-") Ako je k > 0 Onda je ss$ = ss$ & "-" & Mid$(s, k + 1, 1) & "." CropWord = ss$ End If End funkcija

Veličina privitka

Popis u Excelu može se ispravljati pomoću formula - zamjena imena i srednjeg imena inicijalima, spajanje riječi iz ćelija u rečenicu, umetanje riječi u Excel popis.
Imamo tablicu u kojoj su prezime, ime i patronim napisani u različitim ćelijama. Moramo ih smjestiti u jednu ćeliju. Ručno prepisivanje popisa traje dugo. Ali, u Excel tablici postoji posebna funkcija. Postoje dvije mogućnosti.
Prva opcija.
Imamo ovaj popis.
U ćeliju D1 trebamo napisati svoje puno ime i prezime u jednoj rečenici. Zapisujemo formulu u ovu ćeliju (D1). Kliknite na ćeliju (učinite je aktivnom).
Idite na karticu "Formule" u odjeljku "Function Library", odaberite "Text" i odaberite funkciju "CONCATENATE". U prozoru koji se pojavi označavamo adrese ćelija koje trebamo spojiti u jednu rečenicu. Ispalo je ovako.

Puno ime piše se bez razmaka. Kako bi se to popravilo, formula se mora poboljšati. Između adresa ćelija napišite "" iza točke i zareza. Rezultat je sljedeća formula.
=SPAJANJE(A1;" ";B1;" ";C1)
Ispalo je ovako.
Sada kopirajte formulu niz stupac.
Druga opcija.
Umjesto CONCATENATE, možete jednostavno pritisnuti gumb ampersand (&). Formula će izgledati ovako.
=A2&B2&C1
Rezultat je isti kao u prvoj opciji. Ako između riječi nema razmaka, umetnite razmak (" ").
Formula će biti ovakva. =A2&" "&B2&" "&C2
Možete kombinirati ne samo riječi, već i brojeve. Možete napraviti rečenicu iz podataka ćelije u Excelu.
Možete postaviti formule u potrebne ćelije obrasca. Na primjer, imamo popis klijenata s adresama.

Moramo dati prijedlog. Zapisujemo formulu u ćeliju.
=A2&" "&B2&" "&C2&" "&"živi u"&" "&"g."&" "&D2&" "&"st."&" "&E2&" "&"d."&" "&F2& " ."
Ovo je bio prijedlog.

Ovo načelo koristimo za izradu bilo kakvih prijedloga.
Ako je tekst u ćelijama već napisan, ali moramo umetnuti dodatne riječi prije postojećih, to se može učiniti pomoću formule. Imamo ovaj popis.
Prije prezimena trebamo umetnuti riječ Stanar. U ćeliju novog stupca upisujemo formulu.
= "Stanar"&" "&A8
Prepišite ovu formulu niz stupac. Rezultat je ovakav popis.
Prvi stupac se može sakriti ili se može kopirati vrijednost novog stupca bez formula, a mogu se izbrisati prvi stupac i drugi s formulama.
Za drugi način dodavanja teksta, brojeva, simbola tekstu u ćeliji, pogledajte članak “Dodavanje teksta u ćelije s Excel tekstom”.
Pomoću formula možete pretvoriti popis na kojem su imena, srednje i prezimena ispisani u cijelosti u popis s prezimenom i inicijalima. Na primjer, ćelija kaže:
U sljedeći stupac upisujemo sljedeću formulu.
=CONCATENATE(LEFT(SPACE(A1),FIND(" ",SPACE(A1),1)),PSTR(SPACE(A1),FIND(" ",SPACE(A1),1)+1,1);" ";PSTR(RAZMAK(A1);FIND(" ";RAZMAK(A1); FIND(" ";RAZMAK(A1);1)+1)+1;1);"")
Dogodilo se.
Ako između riječi ima dodatnih razmaka, možete ih ukloniti. Više o tome pročitajte u članku “Kako ukloniti višak razmaka u Excelu”. Koristeći iste metode, možete ukloniti razmake između brojeva u formuli, jer Dodatni razmaci mogu dovesti do pogreške prilikom izračuna ili se formula neće računati.
Možete premjestiti podatke u retku iz zadnjih ćelija u prve ili okrenuti red. Na primjer, u ćelijama je napisano: u prvoj ćeliji je Ivanov, u drugoj - Maria. Moramo napisati Maria u prvu ćeliju, Ivanova u drugu. Kako to učiniti brzo u velikoj tablici, pogledajte članak "".