The file contains procedures for 7.7 and 8.x. A string in the format Surname First Name Patronymic is passed to the procedure.
UPD 11/26/10: with gratitude - a new version functions. I haven't tested! Questions about the work of this version - to him, for some reason he does not want to spread it :)
Function CaseName(ValueName,Case=1,InitialsOnly=False, Value pDelimiter=".")
If TypeValue(full name)<>Type("String") Then
Show("Invalid string passed to ""Full name case!"""); Full name return;
EndIf;
// remove multiple spaces
While 1=1 Loop
Full name = Abbreviated LP (StrReplace(Name, " "," "));
If Find(Name," ")=0 Then Abort EndIf;
EndCycle;
If TypeValue(Case)=Type("String") Then
pad = AbbrLP(NReg(Lev(Case,1)));
If Find("irdwtp",pad)=0 Then
EndIf;
ElseIf TypeValue(Case)=Type("Number") Then
If (case<1) или (Падеж>6) Then
Report("Incorrect case passed to ""case of full name""!"); Full name return;
EndIf;
case=case-1;
EndIf;
Full name=AblP(НReg(full name)); // it is more comfortable
// own composition analyzer
Surname="";
For d=1 By StrLength(Name) Cycle
chars=Average(full name, th, 1);
Surname=Lastname+character;
EndCycle;
s=d+1; // crossed a space
Name="";
chars=Average(full name, th, 1);
If chars=" " Then Abort EndIf;
Name=Name+character;
EndCycle;
s=d+1; // cross the second space
Middle name="";
For y=s By StrLength(full name) Cycle
chars=Average(full name, th, 1);
If chars=" " Then Abort EndIf;
Middle name = Middle name + symbol;
EndCycle;
// now we have the Surname, First Name and Patronymic separately.
// the actual content and case analysis block begins
// return if itself is nominative. If ReturnOnlyInitials is set, then convert to initials
If (Left(Case,1)="And") or (Case=1) Then
If NOT Initials Only or Find(Name, ".") Then
Full name return; // either already converted string, or do not need to be converted
EndIf;
NewName = TReg(Last name) + " " + Vreg(Lev(Name,1)) + pSeparator + Vreg(Lev(Middle name,1)) + pSeparator;
Return SokrLP (New full name); // in case the separator is a space. Last cut
EndIf;
// analyze gender M/F
If Right(Patronymic,1)="a" Then Gender="F" Else Gender="M" EndIf;
// create the structure of a table that stores word endings
current=New ValueTable;
StringType=NewTypeDescription("String",NewStringQualifiers(3));
NumberType=NewTypeDescription("Number",NewNumberQualifiers(1,0));
current.Columns.Add("StarOk",StringType); // old ending 2 characters
// columns storing new word endings
current.Columns.Add("p"); // parent
current.Columns.Add("d"); // dative
current.Columns.Add("in"); // accusative
current.Columns.Add("t"); // creative
current.Columns.Add("p"); // prepositional
// to specify how many letters from the end of the word to cut off,
current.Columns.Add("NumberSlice",NumberType); // number of cut letters
Vowels="aeeouiyyayu"; // list of vowels as a string
// ======== process the last name ==========
// fill the table with data for the last name
If gender="M" Then
string=current.Add(); // Ivanov
string.StarOk="*s";
rows.CountSlice=0;
Row=current.Add(); // krasinsky
string.StarOk="*th";
string.r="wow"; string.d="mu"; line.in="th"; string.t="im"; string.p="ohm";
rows.CountSlice=2;
Row=current.Add(); // almighty
line.StarOk="sch";
string.r="his"; string.d="to him"; string.in="his"; string.t="im"; string.p="eat";
rows.CountSlice=2;
Row=current.Add(); // white
string.StarOk="th";
string.r="wow"; string.d="mu"; line.in="th"; string.t="th"; string.p="ohm";
rows.CountSlice=2;
Row=current.Add(); // pale
string.StarOk="*th";
rows.CountSlice=1;
Row=current.Add(); // rabinovich
string.StarOk="*h";
rows.CountSlice=0;
Row=current.Add(); // repairs, zaliznyak
string.StarOk="*k";
string.r="ka"; string.d="ku"; line.in="ka"; string.t="com"; string.p="ke";
rows.CountSlice=2;
Row=current.Add(); // shinkar
string.StarOk="*b";
string.r="i"; string.d="u"; string.in="i"; string.t="eat"; string.p="e";
rows.CountSlice=1;
Row=current.Add(); // perelman, oganesyan
string.StarOk="*n";
rows.CountSlice=0;
Row=current.Add(); // lamb
string.StarOk="in";
string.r="a"; string.d="y"; line.in="a"; string.t="th"; string.p="e";
rows.CountSlice=0;
ElseIf Gender="F" Then
string=current.Add(); // Sklodovskaya
line.StarOk="th";
string.r="oops"; string.d="oops"; line.in="th"; string.t="oops"; string.p="oops";
rows.CountSlice=2;
Row=current.Add(); // ivanova
string.StarOk="*a";
string.r="oops"; string.d="oops"; line.in="y"; string.t="oops"; string.p="oops";
rows.CountSlice=1;
EndIf;
If Not EmptyString(LastName) Then
pb=Right(Lastname,3); count="StarOk"; // look for it
newLastName=LastName; // if nothing changes, so be it
stro=current.Find(pb, count);
If strictly<>
Otherwise
// strictly not found by the last three characters, we are looking for two characters only by the last one
pb=Right(Lastname,2);
stro=current.Find(pb, count);
If strictly<>Undefined Then
Stem=Lev(LastName, StrLength(Last Name)-stro.CountSlice);
newLastName=Base+Abb(stro[pad]);
Otherwise // if two are not found, we are looking for one
pb="*"+Right(pb,1);
stro=current.Find(pb, count);
If strictly<>
Stem=Lev(LastName, StrLength(Last Name)-stro.CountSlice);
newLastName=Base+Abb(stro[pad]);
stro=current.Find(pb, count);
If strictly<>Undefined Then // found by sight
Stem=Lev(LastName, StrLength(Last Name)-stro.CountSlice);
newLastName=Base+Abb(stro[pad]);
EndIf;
EndIf;
EndIf;
EndIf;
Otherwise
newLastname="";
EndIf;
// ======== process the name ==========
// fill the table with data for the name
current.Clear();
If Gender="M" Then
// handle exceptions
If Name="lion" Then Name="lion" EndIf;
If Name="paul" Then Name="paul" EndIf;
Row=current.Add(); // Sergey
string.starok="*th";
string.r="i"; string.d="u"; string.in="i"; string.t="eat"; string.p="e";
row.cutoff=1;
Row=current.Add(); // Ivan + Lev + Pavel
string.starok="*s";
string.r="a"; string.d="y"; line.in="a"; string.t="ohm"; string.p="e";
row.cutoff=0;
Row=current.Add(); // nikita
string.starok="*a";
row.cutoff=1;
Row=current.Add(); // bow
string.starok="ka";
row.cutoff=1;
Row=current.Add(); // jeremiah
string.starok="ia";
row.cutoff=1;
Row=current.Add(); // ilya
string.starok="*i";
row.cutoff=1;
Row=current.Add(); // igor
string.starok="*ь";
string.r="i"; string.d="u"; string.in="i"; string.t="eat"; string.p="e";
row.cutoff=1;
ElseIf Gender="F" Then
// handle exceptions
//If Name="olga" Then Name="olga" EndIf;
Row=current.Add(); // Irina
string.starok="*a";
string.r="s"; string.d="e"; line.in="y"; string.t="oops"; string.p="e";
row.cutoff=1;
Row=current.Add(); // Inga, Olga
string.starok="ha";
string.r="and"; string.d="e"; line.in="y"; string.t="oops"; string.p="e";
row.cutoff=1;
Row=current.Add(); // esther
string.starok="*ь";
string.r="and"; string.d="and"; line.in="b"; string.t="w"; string.p="and";
row.cutoff=1;
Row=current.Add(); // maria
string.starok="ia";
string.r="and"; string.d="and"; line.in="u"; string.t="her"; string.p="and";
row.cutoff=1;
Row=current.Add(); // sophia
string.starok="*i";
string.r="and"; string.d="e"; line.in="u"; string.t="her"; string.p="e";
row.cutoff=1;
EndIf;
// table is full. count the last 2 letters and look for them
If Not EmptyString(Name) Then
pb=Right(Name,2); count="StarOk"; // look for it
newName=Name; // if nothing changes, so be it
stro=current.Find(pb, count);
If strictly<>Undefined Then // found strict immediately
Otherwise // not strictly found, we are looking only for the last one
pb="*"+Right(pb,1);
stro=current.Find(pb, count);
If strictly<>Undefined Then // found by last
Base=Lev(Name, StrLength(Name)-str. Count Slice);
newName=base+abbrlp(stro[pad]);
Otherwise // the last one was not found, we are looking for letters by type
pb="*"+?(Find(Vowers,Right(pb,1))=0,"s","g");
stro=current.Find(pb, count);
If strictly<>Undefined=1 Then // found by type
Base=Lev(Name, StrLength(Name)-str. Count Slice);
newName=base+abbrlp(stro[pad]);
EndIf;
EndIf;
EndIf;
Otherwise
newName="";
EndIf;
// ======== Let's process the patronymic, it's easier here ==========
current.Clear();
If Gender="M" Then
string=current.Add();
string.r="a"; string.d="y"; line.in="a"; string.t="eat"; string.p="e";
row.cutoff=0;
ElseIf Gender="F" Then
string=current.Add();
string.r="s"; string.d="e"; line.in="y"; string.t="oops"; string.p="e";
row.cutoff=1;
EndIf;
If Not EmptyString(Patronymic) Then
Base \u003d Leo (Patronymic, StrLength (Patronymic) - current. Number of Slices);
newPatronymic name = Base + Abbreviated LP (current [pad]);
Otherwise
new patronymic="";
EndIf;
IfOnlyInitials Then
newName=Lion(newName,1); newPatronymic=Lion(newPatronymic,1);
EndIf;
// set first letters to upper case
newLastName=ВReg(Lev(newLastName,1))+Mid(newLastName,2);
newName=ВReg(Lev(newName,1))+Average(newName,2);
newPatronymic=ВReg(Lev(newPatronymic,1))+Mid(newPatronymic,2);
// and now everything is together
If InitialsOnly Then // if initials format is specified
newName=newLastName+" "+newName+pSeparator+newPatronymic+pSeparator;
Otherwise
newName=newSurname+" "+newName+" "+newPatronymic;
EndIf;
If Find(full name, ".") Then // In case the input parameter is Surname with initials. Don't touch the initials
newName = newLastname + " " + TReg(First Name) + Treg(Patronymic);
EndIf;
Return SokrLP (new full name);
EndFunctions
Here you can get answers to questions about Microsoft Excel | 57638 | 471543 | ||
44519 | 357828 | |||
Top featured topics from the main forum | 14 | 80 | ||
If you are a happy Mac owner 😉 | 217 | 1059 |
21 Sep 2018 06:21:34 |
|
Section for placement paid questions, projects and tasks and search for executors for them. | 2100 | 13379 | ||
If you have downloaded or purchased the PLEX add-in for Microsoft Excel and you have questions or requests, please click here. | 310 | 1587 | ||
808 | 11620 | |||
Discussion of functionality, rules, etc. | 269 | 3464 | ||
Online now (guests: 590, users: 12, of which hidden: 3) ,
Today is the birthday of (37), (32), (28), (37), (36), (52)
Total registered users: 83105
Took part in the discussion: 31843
Total topics: 105875
case declension in excel
In chapter Other languages and technologies to the question whether it is possible to automatically inflect nouns in Excel format, given by the author Zufar the best answer is Good day.
If we are talking about full name, job titles or departments, then you can. True, this is a tool more for the developer. However, the user can also figure it out. The examples contain ready-made macros for Excel.
Good luck!
PS Search Google for the key "Declination of surnames, names and patronymics by cases"
22 answers
Hello! Here is a selection of topics with answers to your question: is it possible to automatically inflect nouns in Excel format
Answer from flush
No, this feature is not supported in Excel. But for a particular case, you can use the IF function to select one or the other option.
Answer from Proscenium
You are in the "Programming" category, so no one can forbid you to write your own function in VBA, which will do this charitable work. And just the solution of the problem in this format is quite real.
Answer from to christen
I heard that the reform of the Russian language had passed, but I couldn’t even imagine introducing “nouns in Excel format”))))))
Custom function for MS Excel
Declension of full name by cases
The function allows you to display the last name, first name and patronymic, given in the nominative case, in the form of any other case.
Application area:
The function is convenient to use to automate the generation of documents in MS Excel (or facilitate their creation manually), where the full name must be indicated not in the nominative case: various kinds of contracts, cash orders, receipts, reconciliation acts, orders, protocols, powers of attorney, etc.
The function is also indispensable if the automation tools implement the formation of the above documents in the MS Word application based on the data in MS Excel tables.
Examples of using:
- in the contract: “in the person of the head Baranov Petr Vyacheslavovich«
- in cash warrants: "issued Gnatyuk Pavel Vasilyevich"," received from Gritsenko Irina Sergeevna«
- in the power of attorney: "I entrust the management of the vehicle Smirnitsky Vitaly Valerievich«
- in the order: "appoint Stepashova Olga Nikolaevna«
- in the minutes: “the report was prepared Dvorzhetsky Nikita Ivanovich«
What is implemented:
- surnames with all common endings “-ov”, “-ev”, “-in”, “-yan (ts)”, “-sky (-tsky)”, “-sky (-tsky)” are correctly declined ”, “-th”, “-th”, “-their”, “-ets”, “-nko”, “-uk”, “-yuk”, “-ich”, “-iya”, etc.: Pavlov, Zinoviev, Gagarin, Petrosyan, Vysotsky, Trubetskoy, Tsoi, Zadorozhny, Sadovnichy, Sedykh, Dolgikh, Soskovets, Nesterenko, Goncharuk, Danilyuk, Rabinovich, Daneliya, Kvasha, Vrubel, Kozak, Kazachok, etc., as well as the corresponding female surnames
- differences in the declension of male and female surnames are taken into account: Weaver Viktor and Weaver Svetlana, Kozovets Anton and Kozovets Ekaterina
- compound surnames are processed correctly: for example, Mamin-Sibiryak, Saltykov-Shchedrin, Nemirovich-Danchenko, Lebedev-Kumach
- foreign surnames do not decline: Galois, Morois, Defoe, Dali, Meringue, Verdi, Garcia, Hemingway, etc.
- patronymics with particles “-ogly”, “-kyzy”, “ibn” are successfully processed: Mammadov Polad Murtuza oglu -> Mamedov Polad Murtuza oglu, Gassan Abdurakhman ibn Khattab -> Gassan Abdurakhman ibn Khattab, Abu Ali ibn Sina -> Abu Ali ibn Sinai
- the gender (male/female) is automatically determined by patronymic and based on this, the appropriate declension rules are selected
- for situations where it is difficult to determine the gender automatically (middle name is foreign or absent), you can specify the gender as a function parameter
- output in abbreviated form “last name + initials” is possible: Polozov N. A., Vasilyeva A. K., Dvorzhetsky E. E.
- possibility of use as superstructures(can be used in all files at this workplace), and in the form code embedded in file(the function will work in this file at any workplace - it is convenient if, for example, the file is sent to a counterparty)
- open source code - if you wish, you can study the algorithm or modify it
How to connect?
The program is a single file called bdgFIOPropisyu.bas. In order to use it in a particular file, the function must be imported into this file. For this you need:
- open the MS Excel file where you want to use the function
- enter the VBA editor by pressing the hot key "Alt + F11" or through the menu:
- in the main menu of the VBA editor, select the item "File" -> "Import file ...":
- in the file selection window that appears, select the file bdgFIOPropisyu.bas and click the "Open" button:
Everything, the program code is imported and it can be used.
How to use?
Here are some more examples of its use:
Situations where a function might work incorrect:
Despite the fact that there are quite a few situations where the operation of the function may be unsatisfactory, the probability of encountering such a full name in practical work is quite small, because in 99.9% of cases, surnames found on the territory of Russia are declined according to general rules.
In addition, it should be noted that other algorithms and programs also cannot guarantee the correctness of the result in 100% of cases - the Russian language is too large and powerful to be described by a set of logical rules (see below about the influence of the position of the stress in the word, the origin of the surname, about the dependence writing the female form of the surname from writing the masculine form, etc.).
- According to the rules of the Russian language, the declension of male and female foreign language Surnames ending in "-a" and "-ya" depend on the place of stress in the word and the origin of the surname.
All last names ending in unstressed“-a” after consonants, they decline: Seneca -> Seneca, Petrarch -> Petrarch, Smetana -> Smetana, Kurosawa -> Kurosawa, Glinka -> Glinka, Okudzhava -> Okudzhava, etc. Declension of surnames with shock“-a” at the end depends on its origin: surnames of French origin are not inclined: Dumá, Degá, Fermá, Petipá, etc., surnames of other origin (from Slavic and Oriental languages) are inclined: Mitta -> Mitty, Kvasha -> Kvashi, Frying pan -> Frying pans, Poker -> Pokers, Hamza -> Hamza, etc.
Similarly, surnames of French origin with a stressed “-ya” at the end are indeclinable: Zola, Troyat. All other surnames on "-ya" are declined: Golovnya -> Golovny, Danelia -> Danilia, Beria -> Beria, Goya -> Goya.
Since it is impossible to programmatically determine the positions of stress and the origin of the surname, the program algorithm assumes that all such surnames are declined according to general rules. Therefore, French surnames with an accent on the ending are processed incorrectly by the program: Dumas -> Dumas, Zola -> Zoli, Petipa -> Petipa. - Male surnames ending in "-y", "-y", "-oy" are declined as adjectives Gorky -> Gorky, Blessed -> Blessed, Armored -> Armored. Exceptions are cases when a surname with such an ending is a common noun (Kozodoy, Vodopoy, Genii, Scenario) or is foreign in origin (Tsoi, Tskhoi, Choi). Since it is not possible to programmatically determine whether a surname is such an exception, it is conventionally accepted that all surnames with the endings "-y", "-y", "-y" are declined as adjectives. In the vast majority of cases, this is true, however, in exceptional cases with nouns, the result of the function will be erroneous: Kozodoy Yakov -> Kozodogo Yakov, Geniy Stefan -> Genego Stefan.
However, as an exception, the algorithm takes into account the declension of surnames with these endings and less than 5 letters in length (mainly Korean in origin): Tsoi Viktor -> Tsoi Viktor. - In some cases, the correct declension of a female surname ending in “-ina”, “-ova” is possible only if the declension of the corresponding male surname is known: Zhemchuzhina Lyudmila, Malina Svetlana can be declined as in the usual form: Zhemchuzhina Lyudmila -> Zhemchuzhina Lyudmila, Malina Svetlana -> Malina Svetlana (if the corresponding male surnames are, for example, Zhemchuzhin Boris and Malin Oleg), and in the form of a noun: Zhemchuzhina Lyudmila -> Zhemchuzhina Lyudmila, Malina Svetlana -> Malina Svetlana (if the male forms of the surname, for example, Zhemchuzhina Boris and Malina Oleg).
It is impossible to take into account such nuances in the algorithm, so all female surnames are processed by the function according to the same rules (as adjectives), which in some cases can lead to incorrect results. - A similar situation is with male and female surnames ending in “-ov (a)”, “-ev (a)”, “-in (a)”, which are homonymous with a common noun: Borov, Lev, Filin, Barin, Korov, Owl, Painting, Feather, Antiquity, etc.). There is no way to programmatically establish the identity of a surname and a noun by any formal signs, therefore the declension of such surnames according to uniform general rules leads to incorrect results of the form: Lev Pavel -> Levy Pavel, Filin Sergey -> Filin Sergey, Korov Kseniya -> Kovoy Xenia, Owl Olga -> Owl Olga, Painting by Alla -> Painting by Alla.
- Male surnames ending in a soft sign tend to: Vrubel Mikhail -> Vrubel Mikhail, Gogol Nikolai -> Gogol Nikolai, etc. However, when declining, the endings of such surnames depend on what kind of nouns - male, female or middle - they are.
It is not possible to determine the gender of a noun algorithmically, therefore, in cases where a male surname is a noun of a feminine or neuter gender, the function will not work correctly: Stal Ivan -> Stal Ivan, Wormwood Victor -> Wormwood Victor. - The formation of the instrumental case of surnames ending in “-zha”, “-tsa”, “-cha”, “-sha”, “-sha” depends on the stress in the word: if this ending is unstressed, then when declension it changes to "-ey": Kapitsa Peter -> Kapitsa Peter, Cloud Fedor -> Cloud Fedor; if the ending is stressed, it is replaced by “-oy”: Kvasha Igor -> Kvasha Igor, Candle Ivan -> Candle Ivan. The situation is similar with surnames ending in “-ets”: without stress, the ending changes to “-em”: Palets Viktor -> Finger Viktor; under stress - on the "-th": Soskovets Oleg -> Soskovets Oleg.
As mentioned above, it is impossible to programmatically determine the positions of stress in a word, therefore, the program algorithm assumes that in words ending in “-zha”, “-tsa”, “-cha”, “-sha”, “-sha” , the ending is unstressed (there are more such words), that is, the instrumental case is always formed by the ending “-ey”. On the contrary, in words ending in "ets", it is assumed that the ending is under stress, that is, the declension will be with the ending "-om". Accordingly, in other cases, errors of the form will be observed: Igor Kvasha, Ivan Svechey, Viktor Finger. - According to the rules of the Russian language, the first part of a double Russian surname is declined if it can be used as a surname in itself: the work of Mamin-Sibiryak, the story of Saltykov-Shchedrin, Rimsky-Korsakov's opera, etc. Otherwise, it does not change: the painting by Van Dyck, the adventures of Don Juan, the squire of Don Quixote, etc. So, for example, in the surname Semenov-Tyan-Shansky, the first part is inclined, but not the second: the works of Semenov-Tyan-Shansky.
Again, it is not algorithmically possible to determine whether the component part of the surname is independent, therefore, for the work it is assumed that all parts of the composite surname should be declined (in most cases this is the case), which in a number of situations leads to errors: Van Dyck, Dona - Quixote, Semyonov-Tyan-Shansky. - Foreign surnames ending in "-ov" and "-in" in the instrumental case change the ending to "-th": Benjamin Franklin, Charles Darwin, Charlie Chaplin, Alexander Green. "Russian" surnames with similar endings end in "-y": Ivanov, Gagarin, Fonvizin, etc. Despite the fact that the surnames listed above (Franklin, Darwin, Chaplin, Green) are taken into account in the algorithm as exceptions, there is a small chance that any other similar foreign surname will occur - in this case, the result of the function will be incorrect.
The rules for declension of surnames and names are used in accordance with sections 13.1 and 13.2 of the work of N. A. Eskova Difficulties in inflection of nouns. Educational and methodological materials for practical exercises on the course "Language of Modern Printing"(State Press Committee of the USSR. All-Union Institute for Advanced Training of Print Workers. M., 1990).
Price: 1500 rubles