Excel etternavn initialer. Kombinere fornavn og etternavn. Finn navn i fullt navn

10.04.2024

Ved utarbeidelse av avtale, faktura, kommersielt forslag eller annet dokument arbeider vi ofte med kundens etternavn, fornavn og patronym. Noen ganger er det nødvendig å skille det fulle navnet og få bare fornavnet for å adressere klienten i teksten til dokumentet, noen ganger må du lage en spalte med underskriveren, og etterlate etternavnet og initialene.

Sikkert du kan skrive formelen, som vil trekke ut de nødvendige dataene fra det fulle navnet, men selv en erfaren person vil trenge 5-7 minutter for å gjøre dette. Hva om du ikke vet plasseringen til etternavnet i teksten på forhånd? Mye enklere og mer praktisk bruke ferdige funksjoner fra VBA-Excel-tillegg.

Finn etternavn i fullt navn

Etter at du har installert tillegget, vil en ny funksjon være tilgjengelig =ETTERNAVN(fullt navn), Hvor

Fullt navn- tekst eller lenke til en celle som inneholder hele navnet

Hensikten med denne funksjonen er å trekke ut bare etternavnet fra en celle. Dessuten er ikke rekkefølgen viktig for henne. Etternavnet kan stå enten i begynnelsen, på slutten eller midt i teksten.

Finn navn i fullt navn

Lignende funksjon for navn =NAVN(fullt navn). Den trekker ut et navn fra en tekst med fullt navn, uansett hvor den befinner seg.

Finn mellomnavn i fullt navn

Den siste funksjonen i denne serien er =PATERNICAL(FULLSTENDIG NAVN) for å trekke ut mellomnavnet.

Eksempler på arbeid med fullt navn i Excel

Vis etternavn og initialer

En av de vanlige oppgavene ved utarbeidelse av dokumenter er å konvertere tekst i formen Etternavn Fornavn Patronym til teksten Etternavn I. O. Du kan løse det ved å bruke følgende formel:

ETTERNAVN(A1)&" "&VENSTRE(NAVN(A1))&". "&VENSTRE(PATERNAVN(A1))&"."

Den er noe lang, men tro meg, den ville vært mye kortere og klarere hvis vi ikke brukte funksjoner fra tillegget.

Et annet pluss er at plasseringen av etternavnet i teksten ikke er viktig for oss, funksjonen vil bestemme alt automatisk.

Vi har tidligere vurdert muligheten ved å bruke eksemplet med å dele opp et fullt navn i dets komponenter. For å gjøre dette brukte vi verktøyet Tekst etter kolonner i Excel.

Dette er utvilsomt et veldig viktig og nyttig verktøy i Excel som kan forenkle mange oppgaver betraktelig. Men denne metoden har en liten ulempe. Hvis du for eksempel hele tiden får tilsendt data i en bestemt form, og du hele tiden trenger å dele dem, tar dette en viss tid, i tillegg, hvis dataene ble sendt til deg igjen, må du gjøre alle operasjoner igjen.

Hvis vi ser på eksemplet med å dele et fullt navn, vil det være mulig å dele teksten ved å bruke Excel-tekstformler, ved å bruke funksjonen og, som vi diskuterte i tidligere artikler. I dette tilfellet trenger du bare å sette inn data i en bestemt kolonne, og formlene vil automatisk dele teksten etter behov. La oss begynne å se på dette eksemplet.

Vi har en kolonne med en liste over fulle navn, vår oppgave er å plassere etternavn, fornavn og patronym i egne kolonner.

La oss prøve å beskrive handlingsplanen i detalj og dele løsningen på problemet i flere stadier.

Først av alt legger vi til hjelpekolonner for mellomberegninger for å gjøre det klarere for deg, og til slutt kombinerer vi alle formlene til én.

Så la oss legge til kolonneplassering 1. og 2. plass. Ved å bruke FIND-funksjonen, som vi allerede diskuterte i forrige artikkel, vil vi finne plasseringen til det første rommet. For å gjøre dette, i celle "H2" skriver vi formelen

FINN(" ";A2;1)

Nå må vi finne sekvensnummeret til det andre rommet. Formelen vil være den samme, men med en liten forskjell. Hvis vi skriver samme formel, vil funksjonen finne oss det første rommet, men vi trenger det andre rommet. Dette betyr at det er nødvendig å endre det tredje argumentet i FINN-funksjonen - startposisjonen - det vil si posisjonen som funksjonen skal søke etter den søkte teksten fra. Vi ser at det andre rommet i alle fall er etter det første rommet, og vi har allerede funnet posisjonen til det første rommet, noe som betyr at ved å legge til 1 til posisjonen til det første rommet vil vi fortelle FINN-funksjonen å lete etter rommet starter fra første bokstav etter første mellomrom. Funksjonen vil se slik ut:

La oss begynne å dele den første delen av teksten - Etternavn

For å gjøre dette, vil vi bruke funksjonen, la meg minne deg om syntaksen til denne funksjonen:

PSTR(tekst; startposisjon; tall_tegn), hvor

tekst er et fullt navn, i vårt eksempel er det celle A2;
startposisjon- i vårt tilfelle er det 1, det vil si fra første bokstav;
tall_tegn - vi ser at etternavnet består av alle tegn, fra første bokstav og opp til 1. mellomrom. Og vi vet allerede posisjonen til det første rommet. Dette vil være antall tegn minus 1 tegn i selve mellomrommet.

Formelen vil se slik ut:

PSTR(A2;1;H2-1)

La oss begynne å dele den andre delen av teksten - Navn

Igjen bruker vi funksjonen =PSTR(tekst; startposisjon; tall_tegn), hvor

tekst er den samme fulle navnteksten, i vårt eksempel er det celle A2;
startposisjon- i vårt tilfelle begynner Navnet med den første bokstaven etter det første mellomrommet, når vi kjenner posisjonen til dette rommet får vi H2+1;
tall_tegn - antall tegn, det vil si antall bokstaver i navnet. Vi ser at navnet vårt er mellom to mellomrom, posisjonene vi kjenner til. Hvis vi trekker posisjonen til det første rommet fra posisjonen til det andre rommet, får vi forskjellen, som vil være lik antall tegn i navnet, det vil si I2-H2

Vi får den endelige formelen:

PSTR(A2;H2+1;I2-H2)

La oss begynne å dele den tredje delen av teksten - Patronymic

Og igjen funksjonen =PSTR(tekst; startposisjon; tall_tegn), hvor

teksten er den samme fulle navnteksten, i vårt eksempel er det celle A2;
startposisjon- Vårt patronym er plassert etter 2. mellomrom, noe som betyr at startposisjonen vil være lik posisjonen til det andre rommet pluss ett tegn eller I2+1;
tall_tegn - i vårt tilfelle er det ingen tegn etter patronymet, så vi kan ganske enkelt ta et hvilket som helst tall, det viktigste er at det er større enn det mulige antallet tegn i patronymet, jeg tok et tall med stor margin - 50

Vi får funksjonen

PSTR(A2 ;I2+1 ;50 )

Deretter velger du alle tre cellene og drar formlene ned og får resultatet vi trenger. Du kan fullføre her, eller du kan skrive mellomberegninger av plassering av mellomrom inn i selve tekstdelingsformlene. Det er veldig enkelt å gjøre. Vi ser at beregningen for det første rommet er i celle H2 - FINN(" ";A2;1) , og beregningen for det andre rommet er i celle I2 - FINN(" ";A2;H2 +1) . Vi ser at i formelen til celle I2 er det H2, vi endrer den til selve formelen og vi får en nestet formel i celle I2

Vi ser på den første formelen for å velge etternavnet og se hvor H2 eller I2 forekommer her og endre dem til formlene i disse cellene, på samme måte som fornavnet og etternavnet

Etternavn =PSTR(A2,1,H2 -1) vi får =PSTR(A2,1,FINN(" ",A2,1) -1)
Navn =PSTR(A2;H2 +1;I2 -H2) vi får =PSTR(A2;FINN(" ";A2;1) +1;
FINN(" ";A2;FINN(" ";A2;1)+1)-FINN(" ";A2;1) )
Patronym =PSTR(A2;I2 +1;50) vi får =PSTR(A2; FINN(" ";A2;FINN(" ";A2;1)+1)+1;50)

Nå kan mellomposisjonsberegninger med et mellomrom trygt fjernes. Dette er en av teknikkene når det for enkelhets skyld først letes etter mellomdata, og deretter blir funksjonen nestet i hverandre. Enig, hvis du skriver en så stor formel på en gang, er det lett å bli forvirret og gjøre feil.

Vi håper at dette eksemplet tydelig viste deg hvor nyttige Excel-tekstfunksjoner er for å jobbe med tekst og hvordan de lar deg dele tekst automatisk ved hjelp av formler med samme type data. Hvis du likte artikkelen, vil vi være takknemlige for å klikke på +1 og jeg liker den. Abonner og bli med i vår VKontakte-gruppe.

Eksempel 2: Hvordan dele tekst i kolonner i Excel ved hjelp av en formel

La oss se på det andre eksemplet, som også ofte oppstår i praksis. Eksemplet ligner det forrige, men det er mye mer data som må deles. I dette eksemplet vil jeg vise en teknikk som lar deg raskt løse problemet og ikke bli forvirret.

La oss si at vi har en liste over tall atskilt med komma, vi må dele teksten slik at hvert tall er i en egen celle (i stedet for kommaer, kan disse være alle andre tegn, inkludert mellomrom). Det vil si at vi må bryte teksten i ord.

La oss minne deg på at dette problemet kan løses manuelt (uten formler) ved å bruke metoden vi allerede har vurdert. I vårt tilfelle må vi gjøre dette ved å bruke formler.

Først må vi finne en felles inndeling som vi deler teksten etter. I vårt tilfelle er dette et komma, men for eksempel i den første oppgaven delte vi opp hele navnet og skilletegnet var et mellomrom. Vårt andre eksempel er mer universelt (mer praktisk når det er en stor mengde data), så for eksempel kan vi enkelt dele ikke bare hele navnet i separate celler, men hele setningen - hvert ord i en separat celle. Faktisk kom dette spørsmålet opp i kommentarene, så det ble besluttet å supplere denne artikkelen.

For enkelhets skyld vil vi indikere denne separatoren i den tilstøtende kolonnen slik at vi ikke trenger å skrive den i formelen, men bare referere til cellen. Dette vil også tillate oss å bruke filen til å løse andre problemer ved ganske enkelt å endre separatoren i cellene.

Nå er hovedessensen av teknikken.

Trinn 1. I hjelpekolonnen finner vi posisjonen til den første separatoren ved hjelp av FINN-funksjonen. Jeg vil ikke beskrive funksjonen i detalj, siden vi allerede har sett på den tidligere. La oss skrive formelen i D1 og utvide den ned til alle linjer

FINN(B1;A1;1 )

Det vil si at vi ser etter et komma i teksten, med utgangspunkt i posisjon 1

FINN($B1 ;$A1;D1+1 )

Først, la oss fikse kolonnen med ønsket verdi og tekst slik at cellereferansene ikke beveger seg når du drar formelen til høyre. For å gjøre dette må du skrive en dollar før kolonne B og A - enten manuelt, eller velg A1 og B1, trykk på F4-tasten tre ganger, hvoretter koblingene blir absolutte, ikke relative.

For det andre: vi vil beregne det tredje argumentet - begynnelsen av posisjonen som posisjonen til forrige skilletegn (vi fant det ovenfor) pluss 1, det vil si D1+1 siden vi vet at den andre skilletegnet er nøyaktig etter den første skilletegnet og vi trenger ikke å ta hensyn til det.

La oss skrive ned formelen og dra den nedover.

Trinn 3. Vi finner posisjonene til alle andre skilletegn. For å gjøre dette, utvide formelen for å finne den andre separatoren (trinn 2) til høyre med antall celler lik det totale antallet separat delte verdier med en liten margin. La oss få alle skilleposisjonene. Der #Value-feilen betyr at verdiene har gått tom og formelen ikke lenger finner skilletegn. Vi får følgende

Trinn 4. Vi skiller det første tallet fra teksten ved å bruke PSTR-funksjonen.

PSTR(A1;1 ;D1-1 )

Vår startposisjon er 1, vi beregner antall tegn som posisjonen til den første skilletegn minus 1: D1-1 dra formelen ned

Trinn 5. Vi finner også det andre ordet ved å bruke PSTR-funksjonen i celle P1

PSTR($A1;D1+1;E1-D1-1)

Startposisjonen til det andre tallet begynner etter det første kommaet. Vi har posisjonen til det første kommaet i celle D1, legg til en og få startposisjonen til vårt andre nummer.

Antall tegn er forskjellen mellom posisjonen til den tredje skilletegn og den andre og minus ett tegn, det vil si E1-D1-1

La oss fikse kolonne A i kildeteksten slik at den ikke beveger seg når du drar formelen til høyre.

Trinn 6. La oss strekke formelen oppnådd i trinn 5 til høyre og ned og få tekst i separate celler.

Trinn 7 I prinsippet er problemet vårt allerede løst, men for skjønnhet vil vi i samme celle P1 skrive en formel som fanger en feil, og erstatte den med en tom verdi. Du kan også gruppere og skjule hjelpekolonner slik at de ikke kommer i veien. Vi får den endelige løsningen på problemet

FEIL(PSTR($A1;D1+1;E1-D1-1); "")

Merk. Vi gjorde den første posisjonen til skilletegn og den første inndelingen av ordet annerledes enn andre, og på grunn av dette kunne vi bare utvide formelen fra de andre verdiene. Mens jeg skrev problemet, la jeg merke til at problemet kunne forenkles. For å gjøre dette, i kolonne C var det nødvendig å skrive inn 0 for verdien av den første separatoren. Etter dette finner vi verdien av den første separatoren

FINN($B1;$A1;C1+1)

og den første teksten som

PSTR($A1;C1+1;D1-C1-1)

Etter dette kan du umiddelbart utvide formelen til de gjenværende verdiene. Jeg lar dette alternativet være et eksempel for nedlasting. I prinsippet kan filen brukes som mal. Sett inn data i kolonne "A", spesifiser en separator i kolonne "B", strekk formlene til det nødvendige antallet celler og få resultatet.

Merk følgende! Kommentarene bemerket at siden vi ikke har et skilletegn på slutten av teksten, teller vi ikke antall tegn fra siste skilletegn til slutten av linjen, så den siste avgrensede teksten mangler. For å løse problemet kan du enten i første trinn legge til en hjelpekolonne ved siden av kildeteksten, hvor du kan kombinere denne teksten med en skilletegn. På denne måten vil vi ha en skilletegn på slutten av teksten, noe som betyr at formlene våre vil beregne posisjonen og alt vil fungere.

Eller den andre løsningen er i trinn 3, når vi komponerer en formel for å beregne posisjonene til separatorene og supplerer den. Sjekk, hvis det er en feil, angi et åpenbart stort tall, for eksempel 1000.

FEIL(FINN($B1;$A1;C1+1);1000)

Funksjonen mottar som parameter en tekststreng i formen "Etternavn fornavn patronymisk", og kutter av fornavn og patronym, og etterlater bare initialene - i formen "Etternavn I. O."

Denne funksjonen kan bli brukt som UDF (brukerdefinerte funksjoner) i Excel-arkceller.
(se eksempel i vedlegg)

Beskrivelse av funksjonene til funksjonen - på forumet: programmersforum.ru/showpost.php?p=757147&postcount=6

Det er ingen eksakt formell algoritme for å dele opp et fullt navn i deler.
Det er umulig å formelt skille et etternavn fra et gitt navn uten å kjenne slektsforskningen og språket til morsmålet. Du kan bare bruke en rekke heuristikker.

I tillegg hadde ulike myndigheter (til ulike tider) ulike syn på hvordan initialer skulle se ut i en gitt sak.
Federal Migration Service-dokumenter er én ting, sentralbankregistratorer er en annen, og utarbeidelse av kommersielle dokumenter er en annen.
Derfor krever noen tjenester at 2-3 komplekse korte navn i Sørøst-Asia ikke forkortes. Men noen gjør det ikke.

Ja, med spørsmålet om hvor initialene skal være til høyre eller venstre - er det ingen entydighet. Nei, selvfølgelig er det regler for det russiske språket... Men reglene for forretningsomsetning av en eller annen struktur råder (eller rett og slett som venstre bakbein til kontoristen som godtar dokumenter ønsker)...

Mange har kanskje et spørsmål: Hvor kommer hele denne dyrehagen fra og hvorfor er alt nødvendig? Som regel er alle disse Don Pedros utenlandske statsborgere som mottar et russisk pass på grunnlag av dokumenter tidligere utstedt utenfor jurisdiksjonen til den russiske føderasjonen. Juridiske situasjoner er forskjellige. Og ganske ofte skrives en translitterasjon til kyrillisk fra morsmålet inn i det nye russiske passet.
Russiske pass fra -ogly og -kyzy utstedes nå i Tatarstan. Jeg møtte en klient hvis navn ifølge passet hans fra 2007 var Mustafa Oleg ogly...

Alternativ Sammenlign tekst Offentlig funksjon initialer(er som streng , valgfri Til venstre som boolsk = usann ) Dim sv As variant , sФ Som streng , sИ Som streng , sО Som streng , i Så lang , k Så lang Application.Volatile True If InStr(s) , "." ) > 0 Eller Len(Trim$(s)) = 0 Deretter initialer = s "Initialer spesifisert eksplisitt eller tom streng Avslutt funksjon Slutt If "Normaliser inndatastreng s = Replace(Application.Trim(s), Chr(30), "-" ) s = Replace(Replace(s, " -" , "-" ), "- " , "-" ) s = Replace(Replace (s, "" " , """ ), " "" , """ ) "O" Henry Alexander; O" Henry Alexander; N" Homo; D" Treville sv = Split(er) sИ = vbNullString: sО = vbNullString: sФ = vbNullString i = UBound (sv) Hvis i< 1 Then Инициалы = s: Exit Function Select Case sv(i) Case "оглы" , "кызы" , "заде" "bey, bek, zade, zul, ibn, kyzy, ogly, ol, pasha, ul, khan, shah, ed, el i = i - 1 sО = UCase(Left$(sv(i), 1)) & "." i = i - 1 Case "pasha" , "khan" , "shah" , "sheikh" i = i - 1 Case Else Velg Case Right$(sv(i), 3) Case "vich" , "vna" Hvis jeg >= 2 Deretter "Standard avslutning for russisk patronym sО = СropWord(sv(i)) Else "Navn som Bosan Slavich sИ = СropWord(sv(i)): sФ = sv(0) End If i = i - 1 Case Else k = InStr(sv(i), "-" ) Hvis k > 0, så velg Case Mid$(sv( i), k + 1) Sak "ogly", "kyzy", "zade", "ugli", "uuly", "ool" "Variant av nasab "-ogly" og "-zade" type Mahmud-ogly sО = UCase(Left$(sv(i), 1)) & "." i = i - 1 Hvis i = 0 Da sИ = sО sО = vbNullString End If End Velg ElseIf i > 2 Så velg Case sv(i - 1) Case "ibn" , "bin" , "bin" sО = UCase(Venstre $(sv(i), 1)) & "." "Bruker Alisher bin Suleiman i = i - 2 End Select Else " Ben Edward sИ = UCase(Left$(sv(i), 1)) If Len(sv(i)) > 1 Then sИ = sИ & "." i = i - 1 End Hvis End Velg Slutt Velg Velg Sak sv(0) Sak "de" , "del" , "dos" , "sept" , "van" , "fon" , "tsu" Hvis i >= 2 Så sФ = sv(0 ) & " " & StrConv(sv(1), vbProperCase) sИ = СropWord(sv(2)) Else "De Nikolai If Len(sИ) > 0 Then 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 Then – Vi har ikke bestemt oss for etternavn ennå. sФ = StrConv(sv(0), vbProperCase) If Len(sИ) = 0 Then sИ = СropWord(sv(1)) End If End Velg If ToLeft Then Initials = sИ & sО & " " & sФ Else Initials = sФ & " " & sИ & sО Sluttfunksjon Offentlig funksjon СropWord(s As Variant ) As String If Len(s) = 1 Then СropWord = s Else ss$ = UCase(Left$(s, 1)) & "." : k = InStr(s, "-" ) Hvis k > 0 Så ss$ = ss$ & "-" & Mid$(s, k + 1, 1) & "." СropWord = ss$ End If End-funksjon

Vedlegget	Størrelse

Liste i Excel kan rettes med formler - bytt ut for- og mellomnavn med initialer, kombiner ord fra celler til en setning, sett inn ord i en Excel-liste.
Vi har en tabell hvor etternavn, fornavn og patronym er skrevet i ulike celler. Vi må plassere dem i én celle. Manuell omskriving av listen tar lang tid. Men i Excel-tabellen er det en spesiell funksjon.Det er to alternativer.
Første alternativ.
Vi har denne listen.
Vi må skrive hele navnet vårt i celle D1 i én setning.Vi skriver formelen i denne cellen (D1). Klikk på cellen (gjør den aktiv).
Gå til «Formler»-fanen i «Funksjonsbibliotek»-delen, velg «Tekst», og velg «KONKATER»-funksjonen.I vinduet som vises, angir vi adressene til cellene som vi trenger å kombinere til en setning. Det ble slik.

Fullt navn skrives uten mellomrom. For å fikse dette, må formelen forbedres.Skriv mellom celleadressene etter semikolon" " . Resultatet er følgende formel.
=SAMMENKNYTT(A1;" ";B1;" ";C1)
Det ble slik.
Kopier nå formelen nedover kolonnen.
Andre alternativ.
I stedet for CONCATENATE-funksjonen kan du ganske enkelt trykke på og-tegnet (&).Formelen vil se slik ut.
=A2&B2&C1
Resultatet er det samme som i det første alternativet. Hvis det ikke er mellomrom mellom ordene, setter du inn et mellomrom (" ").
Formelen vil være slik.=A2&" "&B2&" "&C2
Du kan kombinere ikke bare ord, men også tall. Kan lag en setning fra celledata i Excel.
Du kan angi formler i de nødvendige cellene i skjemaet.For eksempel har vi en liste over kunder med adresser.

Vi må komme med et forslag. Vi skriver formelen i cellen.
=A2&" "&B2&" "&C2&" "&"bor på"&" "&"g."&" "&D2&" "&"st."&" "&E2&" "&"d."&" "&F2& " ."
Dette var forslaget.

Vi bruker dette prinsippet for å utarbeide eventuelle forslag.
Hvis teksten i cellene allerede er skrevet, men vi trenger sette inn flere ord før eksisterende, så kan dette gjøres ved hjelp av formelen.Vi har denne listen.
Vi må sette inn ordet "Leietaker" før etternavnene.I cellen i den nye kolonnen skriver vi formelen.
="Leietaker"&" "&A8
Kopier denne formelen nedover i kolonnen. Resultatet er en slik liste.
Den første kolonnen kan skjules eller verdien til en ny kolonne uten formler kan kopieres, og den første kolonnen og den andre med formler kan slettes.
En annen måte å legge til tekst, tall, symboler i tekst i en celle, se artikkelen " Legg til tekst i Excel-tekstceller ".
Ved hjelp av formler kan du konvertere en liste, hvor fornavn, mellomnavn og etternavn er skrevet i sin helhet, til liste med etternavn og initialer. For eksempel sier cellen:
I neste kolonne skriver vi følgende formel.
=SAMMENKNYTT(VENSTRE(MELLOMROM(A1);FINN(" ",MELLOMROM(A1);1)),PSTR(MELLOMROM(A1);FINN(" ",MELLOMROM(A1);1)+1,1);" .";PSTR(MELLOMROM(A1);FINN(" ";MELLOMROM(A1); FINN(" ";MELLOMROM(A1);1)+1)+1;1);."")
Skjedde.
Hvis det er ekstra mellomrom mellom ordene, kan du fjerne dem. Les mer om dette i artikkelen " Slik fjerner du ekstra mellomrom i Excel". Ved å bruke de samme metodene kan du fjerne mellomrom mellom tall i en formel, siden ekstra mellomrom kan føre til en feil ved beregning eller formelen vil ikke telle.
Du kan flytte data på rad fra de siste cellene til de første, snu linjen. For eksempel, i cellene er det skrevet: i den første cellen er Ivanov, i den andre - Maria. Vi må skrive Maria i den første cellen, Ivanova i den andre.Hvordan gjøre dette raskt i et stort bord, se artikkelen "".