The file contains procedures for 7.7 and 8.x. A string is passed to the procedure in the format Surname First Name Patronymic.

UPD 11/26/10: with gratitude - new version of the function. I have not tested! Questions about the work of this version - to him, for some reason he does not want to spread it :)

Function CasesFULLNAME (Value Full Name, Cases = 1, OnlyInitials = False, Value nSeparator = ".")
If TypeZnch (full name)<>Type ("String") Then
Report ("Invalid string passed to" "case full name!" ""); Return of full name;
EndIf;

// remove multiple spaces
While 1 = 1 Cycle
Full name = Abbreviation (StrReplace (full name, "", ""));
If Find (full name, "") = 0 Then Abort EndIf;
End of Cycle;

If TypeZnch (Case) = Type ("String") Then
pad = SocrLP (NReg (Leo (Case, 1)));
If Find ("irdvtp", pad) = 0 Then

EndIf;
ElseIf TypeZnch (Case) = Type ("Number") Then
If (Case<1) или (Падеж>6) Then
Report ("Incorrect case passed to" "case name" "!"); Return of full name;
EndIf;
pad = Case-1;
EndIf;

Full name = Abbreviation for LP (NReg (full name)); // this is more comfortable

// your own composition analyzer
Last name = "";
For d = 1 By Str Length (full name) Cycle
symbols = Middle (full name, d, 1);

Last name = Last name + characters;
End of Cycle;
s = d + 1; // crossed the space
Name = "";

symbols = Middle (full name, d, 1);
If char = "" Then Abort EndIf;
Name = Name + characters;
End of Cycle;
s = d + 1; // crossed the second space
Middle name = "";
For d = s By Str Length (full name) Cycle
symbols = Middle (full name, d, 1);
If char = "" Then Abort EndIf;
Patronymic = Patronymic + symbols;
End of Cycle;

// now we have separate Surname, Name and Patronymic.
// begins the actual block for analyzing the content and cases

// return if itself is nominative. If set to return OnlyInitials, then convert to initials
If (Leo (Case, 1) = "And") or (Case = 1) Then
If NOT Only Initials or Find (full name, ".") Then
Return of full name; // either an already converted string, or no need to convert
EndIf;
New FULL NAME = TREG (Surname) + "" + Vreg (Leo (Name, 1)) + pSeparator + Vreg (Leo (Patronymic, 1)) + pSeparator;
Return SokrLP (New Full Name); // in case the separator is a space. The last one we cut
EndIf;

// analyze gender M / F
If Right (Patronymic, 1) = "a" Then Gender = "F" Otherwise Gender = "M" EndIf;

// create a table structure that stores word endings
current = New Table of Values;
StringType = NewTypeDescription ("String", NewStringQualifiers (3));
NumberType = NewTypeDescription ("Number", NewNumber Qualifiers (1,0));
current.Columns.Add ("StarOk", LineType); // old ending 2 characters
// columns storing new word endings
current.Columns.Add ("p"); // genitive
current.Kolonki.Add ("d"); // dative
current.Columns.Add ("in"); // accusative
current.Columns.Add ("t"); // creative
current.Kolonki.Add ("p"); // prepositional
// to indicate how many letters from the end of the word to cut off,
current.Columns.Add ("NumberSection", NumberType); // number of cut letters

Vowels = "aeeooiyyayuyu"; // a 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
line.StarOk = "* s";

line.ColvoSlice = 0;

Stroke = current. Add (); // krasinsky
line.StarOk = "* th";
line.r = "wow"; line.d = "ohmu"; line.v = "wow"; line.t = "them"; line.p = "ohm";
line.QuantitySection = 2;

Stroke = current. Add (); // almighty
line.StarOk = "shy";
line.r = "him"; stotok.d = "him"; line.in = "him"; line.t = "them"; line.p = "eat";
line.QuantitySection = 2;

Stroke = current. Add (); // White
line.StarOk = "th";
line.r = "wow"; line.d = "ohmu"; line.v = "wow"; line.t = "ym"; line.p = "ohm";
line.QuantitySection = 2;

Stroke = current. Add (); // pale
line.StarOk = "* th";

line.QuantitySection = 1;

Stroke = current. Add (); // Rabinovich
line.StarOk = "* h";

line.ColvoSlice = 0;

Stroke = current. Add (); // fixes, slug
line.StarOk = "* to";
line.r = "ka"; line.d = "ku"; line.v = "ka"; line.t = "com"; line.p = "ke";
line.QuantitySection = 2;

Stroke = current. Add (); // shinkar
line.StarOk = "* b";
strotok.r = "i"; stotok.d = "u"; line.v = "i"; line.t = "eat"; line.p = "e";
line.QuantitySection = 1;

Stroke = current. Add (); // perelman, oganesyan
line.StarOk = "* n";

line.ColvoSlice = 0;

Stroke = current. Add (); // barankin
line.StarOk = "in";
line.r = "a"; line.d = "y"; line.v = "a"; line.t = "ym"; line.p = "e";
line.ColvoSlice = 0;

ElseIf Gender = "F" Then
string = current.Add (); // sklodovskaya
line.StarOk = "th";
line.r = "oh"; line.d = "oh"; line.v = "yu"; line.t = "oh"; line.p = "oh";
line.QuantitySection = 2;

Stroke = current. Add (); // ivanova
line.StarOk = "* a";
line.r = "oh"; line.d = "oh"; line.v = "y"; line.t = "oh"; line.p = "oh";
line.QuantitySection = 1;
EndIf;

If Not EmptyString (Last Name) Then
pb = Right (Surname, 3); count = "StarOk"; // search for it
newSurname = Lastname; // if nothing changes, it will
str = current. Find (pb, count);
If str<>


Otherwise
// strictly not found by the last three characters, search by two characters only by the last
pb = Right (Surname, 2);
str = current. Find (pb, count);
If str<>Undefined Then
Base = Leo (Surname, StrLength (Surname) -str. ColvoSection);
newSurname = Basis + SokrLP (stro [pad]);
Otherwise // if we didn’t find two, look for one at a time
pb = "*" + Right (pb, 1);
str = current. Find (pb, count);
If str<>
Base = Leo (Surname, StrLength (Surname) -str. ColvoSection);
newSurname = Basis + SokrLP (stro [pad]);


str = current. Find (pb, count);
If str<>Undefined Then // found by type
Base = Leo (Surname, StrLength (Surname) -str. ColvoSection);
newSurname = Basis + SokrLP (stro [pad]);
EndIf;
EndIf;
EndIf;
EndIf;
Otherwise
newSurname = "";
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;

Stroke = current. Add (); // Sergei
line.old = "* th";
strotok.r = "i"; stotok.d = "u"; line.v = "i"; line.t = "eat"; line.p = "e";
line.colouction = 1;

Stroke = current. Add (); // ivan + lion + pavel
line.old = "* s";
line.r = "a"; line.d = "y"; line.v = "a"; strotok.t = "ohm"; line.p = "e";
line.colvoice = 0;

Stroke = current. Add (); // nikita
line.old = "* a";

line.colouction = 1;

Stroke = current. Add (); // bow
line.old = "ka";

line.colouction = 1;

Stroke = current. Add (); // Jeremiah
line.old = "ia";

line.colouction = 1;

Stroke = current. Add (); // ilya
line.old = "* i";

line.colouction = 1;

Stroke = current. Add (); // Igor
line.old = "* b";
strotok.r = "i"; stotok.d = "u"; line.v = "i"; line.t = "eat"; line.p = "e";
line.colouction = 1;

ElseIf Gender = "F" Then
// handle exceptions
// If Name = "Olga" Then Name = "Olga" EndIf;

Stroke = current. Add (); // Irina
line.old = "* a";
line.r = "s"; line.d = "e"; line.v = "y"; line.t = "oh"; line.p = "e";
line.colouction = 1;

Stroke = current. Add (); // inga, olga
line.old = "ha";
line.r = "and"; line.d = "e"; line.v = "y"; line.t = "oh"; line.p = "e";
line.colouction = 1;

Stroke = current. Add (); // Esther
line.old = "* b";
line.r = "and"; line.d = "and"; line.v = "b"; line.t = "new"; line.p = "and";
line.colouction = 1;

Stroke = current. Add (); // maria
line.old = "ia";
line.r = "and"; line.d = "and"; line.v = "u"; strotok.t = "her"; line.p = "and";
line.colouction = 1;

Stroke = current. Add (); // sofia
line.old = "* i";
line.r = "and"; line.d = "e"; line.v = "u"; strotok.t = "her"; line.p = "e";
line.colouction = 1;
EndIf;

// the table is full. count the last 2 letters and look for them
If Not EmptyString (Name) Then
pb = Right (Name, 2); count = "StarOk"; // search for it
newName = Name; // if nothing changes, it will
str = current. Find (pb, count);
If str<>Undefined Then // found strict immediately


Otherwise // strictly not found, we search only by the last
pb = "*" + Right (pb, 1);
str = current. Find (pb, count);
If str<>Undefined Then // found by last
Base = Leo (Name, StrLength (Name) -string.ColvoSection);
newName = Basis + SokrLP (stro [pad]);
Otherwise // we didn’t find the last one, we look for the letters
pb = "*" +? (Find (Vowels, Right (pb, 1)) = 0, "s", "g");
str = current. Find (pb, count);
If str<>Undefined = 1 Then // found by type
Base = Leo (Name, StrLength (Name) -string.ColvoSection);
newName = Basis + SokrLP (stro [pad]);
EndIf;
EndIf;
EndIf;
Otherwise
newName = "";
EndIf;

// ======== process the middle name, it's easier here ==========
current.Clear ();

If Gender = "M" Then
string = current.Add ();
line.r = "a"; line.d = "y"; line.v = "a"; line.t = "eat"; line.p = "e";
line.colvoice = 0;
ElseIf Gender = "F" Then
string = current.Add ();
line.r = "s"; line.d = "e"; line.v = "y"; line.t = "oh"; line.p = "e";
line.colouction = 1;
EndIf;
If Not Empty String (Patronymic) Then
Basis = Leo (Patronymic, Str Length (Patronymic) -tok.KolvoSrez);
new patronymic = Basis + SokrLP (current [pad]);
Otherwise
new patronymic = "";
EndIf;

If Only Initials Then
newName = Leo (newName, 1); new patronymic = Leo (new patronymic, 1);
EndIf;

// set the first letters to uppercase
newSurname = ВReg (Leo (newSurname, 1)) + Average (newSurname, 2);
newName = VReg (Leo (newName, 1)) + Middle (newName, 2);
new patronymic = VReg (Leo (new patronymic, 1)) + Middle (new patronymic, 2);

// and now everything is together
If Initials Only Then // if initials format is specified
new full name = new last name + "" + new name + separator + new patronymic + separator;
Otherwise
new full name = new last name + "" + new name + "" + new middle name;
EndIf;

If Find (full name, ".") Then // In case the input parameter is Surname with initials. Do not touch the initials
novFIO = newSurname + "" + Treg (Name) + Treg (Patronymic);
EndIf;

Return SokrLP (novFIO);
EndFunction

Online (guests: 590, users: 12, of which hidden: 3),

Today is the birthday of (37), (32), (28), (37), (36), (52)

Total registered users: 83105

Participated in the discussion: 31843

Total topics: 105875

declension by cases 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 divisions, then you can. True, this is a tool rather for a developer. However, the user can figure it out too. The examples have ready-made macros for Excel.
Good luck!
PS Search in Google for the key "Declination of surnames, names and patronymics by cases"

22 answers

Hey! 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 special case, you can use the IF function to select one or the other.

Answer from Proscenium
You are in the "Programming" category, so no one can prevent you from writing your own function in VBA, which will do this godly business. And just solving the problem in this format is quite realistic.

Answer from christen
I heard that the reform of the Russian language has passed, but I could not even imagine that “nouns in Excel format” were introduced))))))

Custom function for MS Excel

Declination of full name by case

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 formation of documents in MS Excel (or to facilitate their manual creation), 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 contained in the MS Excel tables.

Examples of using:

• in the contract: "represented by the head Baranov Petr Vyacheslavovich«
• in cash orders: "issued Hnatyuk Pavel Vasilievich"," Received from Irina Sergeevna Gritsenko«
• in the power of attorney: “I trust the management of the vehicle Smirnitsky Vitaly Valerievich«
• in the order: "to appoint Stepashova Olga Nikolaevna«
• in the minutes: "the report is prepared Dvorzhetsky Nikita Ivanovich«

What is implemented:

• surnames with all the endings “-ov”, “-ev”, “-in”, “-yan (c)”, “-sky (-tsky)”, “-skoy (-tskoy)” are declined correctly "," -Th "," -ich "," -ih "," -ets "," -nko "," -uk "," -yuk "," -ich "," -ya "and others: Pavlov, Zinoviev, Gagarin, Petrosyan, Vysotsky, Trubetskoy, Tsoi, Zadorozhny, Sadovnichy, Sedykh, Dolgikh, Soskovets, Nesterenko, Goncharuk, Danilyuk, Rabinovich, Danelia, Kvasha, Vrubel, Kozak, Kazachok, etc., as well as the corresponding female surnames
• the differences in the declension of male and female surnames are taken into account: Tkach Viktor and Tkach Svetlana, Kozovets Anton and Kozovets Ekaterina
• compound surnames are processed correctly: for example, Mamin-Sibiryak, Saltykov-Shchedrin, Nemirovich-Danchenko, Lebedev-Kumach
• foreign-language surnames are not inclined: Galois, Morua, Defoe, Dali, Meringue, Verdi, Garcia, Hemingway, etc.
• patronymics with particles "-oglu", "-kyzy", "ibn" are successfully processed: Mamedov Polad Murtuza oglu -> Mamedov Polad Murtuza oglu, Ghassan Abdurahman ibn Khattab -> Gassan Abdurahman ibn Khattab, Abu Ali ibn Ali ibn -> Abu Ali ibn Ali ibn Sinoy
• the gender (male / female) is automatically determined by the patronymic and, based on this, the appropriate declension rules are selected
• for situations when it is difficult to automatically determine the gender (the middle name is foreign or absent), you can specify the gender as a parameter of the function
• possible conclusion in abbreviated form "surname + initials": Polozov N. A., Vasilyeva A. K., Dvorzhetskiy E. Ye.
• the ability to use both in the form superstructures(can be used in all files at this workplace), and in the form code embedded in the file(the function will work in this file at any workplace - it is convenient if the file, for example, is sent to a counterparty)
• open source code - if you wish, you can study the algorithm or modify

How to connect?

The program is a single file named bdgFIOPropisyu.bas... In order to use it in a particular file, the function must be imported into this file. This requires:

1. open the MS Excel file where you want to use the function
2. enter the VBA editor by pressing the "Alt + F11" hotkey or through the menu:
3. in the main menu of the VBA editor, select the item "File" -> "Import file ...":
4. in the file selection window that appears, select the file bdgFIOPropisyu.bas and click the "Open" button:

That's it, the program code is imported and you can use it.

How to use?

Here are some more examples of its use:

Situations where the function can be incorrect:

Despite the fact that there are a lot of situations when the operation of the function may be unsatisfactory, there are quite a few listed below, the likelihood of a collision with such a full name in practical work is rather small, since in 99.9% of cases, surnames found on the territory of Russia are inclined 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 stress in a word, the origin of the surname, about the dependence writing the feminine form of the surname from the spelling of 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 surnames ending in unstressed"-A" after consonants, declines: Seneca -> Seneca -> Petrarch -> Petrarch, Sour cream -> Sour cream, Kurosava -> Kurosawa, Glinka -> Glinka, Okudzhava -> Okudzhava, etc. Declination of surnames with percussion The “-a” at the end depends on its origin: surnames of French origin are not inclined: Dumá, Dega, Fermá, Petipa, etc., surnames of a different origin (from Slavic and Eastern languages) are declined: Mitta -> Mitty, Kvasha -> Kvashi, Frying pan -> Frying pans, Kocherga -> Kochergi, Hamza -> Hamzy, etc.
  Likewise, the surnames of French origin with a stressed "-i" at the end are indestructible: Zola, Troyet. All other surnames on "-ya" are declined: Golovnya -> Golovni, Danelia -> Danilia, Beria -> Beria, Goya -> Goya.
  Since it is impossible to programmatically determine the position of the stress and the origin of the surname, it is assumed in the program algorithm that all such surnames are declined according to general rules. Therefore, French surnames with an emphasis on the ending are processed incorrectly by the program: Dumas -> Dumas, Zola -> Zoli, Petipa -> Petipa.
• Male surnames ending in "-y", "-y", "-y" are declined as adjectives Gorky -> Gorky, Blissful -> Blissful, Bronevoy -> Armor. Exceptions are cases when a surname with such an ending is a common noun (Nightingale, Vodopoy, Genius, 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 "-th", "-th", "-y" are declined as adjectives. In the overwhelming majority of cases, this is true, however, in exceptional cases with surnames-nouns, the result of the function will be erroneous: Kozodoy Yakov -> Kozodoy Yakov, Genius Stefan -> Genego Stefan.
  However, as an exception, the algorithm took into account the declension of surnames with these endings and less than 5 letters in length (mostly Korean in origin): Viktor Tsoi -> Viktor Tsoi.
• 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: Pearl Lyudmila, Malina Svetlana can be declined as in the usual form: Pearl Lyudmila -> Pearl 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: Pearl Lyudmila -> Pearls Lyudmila, Malina Svetlana -> Malina Svetlana (if the male forms of the surname, for example, Pearl Boris and Malina Oleg).
  It is impossible to take into account such nuances in the algorithm, therefore, all women's surnames are processed by the function according to the same rules (as adjectives), which in some cases may 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, Owl, Barin, Korova, Owl, Painting, Perina, Old Man, etc.). There is no way to programmatically establish the identity of a surname and a noun by any formal signs, therefore declining such surnames according to the same general rules leads to incorrect results of the form: Lev Pavel -> Levym Pavel, Filin Sergei -> Filin Sergei, Korova Ksenia -> Korovoy Ksenia, Owl Olga -> Olga's Owl, Alla's Painting -> Alla's Painting.
• Male surnames ending in a soft sign are inclined: Mikhail Vrubel -> Mikhail Vrubel, Nikolai Gogol -> Nikolai Gogol, etc. However, when declining, the endings of such surnames depend on what kind of nouns - masculine, feminine or neuter - they are.
  It is not possible to determine the gender of a noun algorithmically, therefore, in cases where a male surname is a feminine or neuter noun, the function will not work correctly: Steel Ivan -> Steel Ivan, Wormwood Victor -> Wormwood Victor.
• The formation of the instrumental case of surnames ending in "-zha", "-ca", "-cha", "-sha", "-sha" depends on the stress in the word: if this ending is unstressed, then with declination it changes to "-Ey": Kapitsa Peter -> Kapitsa Peter, Cloud Fyodor -> Cloud Fyodor; if the ending is accented, it is replaced by "-oi": Kvasha Igor -> Kvashoy Igor, Candle Ivan -> Candle Ivan. The situation is similar with surnames ending in "-ets": without stress, the ending changes to "-em": Victor Finger -> Victor Finger; under stress - on "-th": Oleg Soskovets -> Oleg Soskovets.
  As mentioned above, it is impossible to programmatically determine the position of stress in a word, therefore, in the program algorithm it is assumed that in words ending in “-ja”, “-ca”, “-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 accepted 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: Kvashey Igor, Candles Ivan, Finger Viktor.
• According to the rules of the Russian language, the first part of a double Russian surname is inclined if it itself can be used as a surname: the work of Mamin-Sibiryak, the story of Saltykov-Shchedrin, the opera of Rimsky-Korsakov, etc. Otherwise, it does not change: the painting of Van Dyck, the adventures of Don Juan, the squire of Don Quixote, etc. So, for example, the first part is inclined in the surname Semyonov-Tyan-Shansky, but not the second: the works of Semyonov-Tyan-Shansky.
  Again, it is algorithmically impossible to determine whether the constituent part of the surname is independent, therefore it is assumed for work that all parts of the compound surname should be declined (in most cases this is so), which in a number of situations leads to errors: Vana-Dyck, Don -Quixote, Semyonova-Tyana-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 "-th": Ivanov, Gagarin, Fonvizin, etc. Despite the fact that the above surnames (Franklin, Darwin, Chaplin, Green) are taken into account in the algorithm as exceptions, there is a small chance that some other similar foreign surname will be encountered - in this case, the result of the function will be incorrect.

The declension rules for surnames and first names are used in accordance with sections 13.1 and 13.2 of the work of N.A.Eskova “Difficulties of inflection of nouns. Teaching materials for practical training in the course "Language of Modern Press"(State Press Committee of the USSR. All-Union Institute for Advanced Training of Press Workers. M., 1990).

Price: 1500 rubles