Excel last name initials. Combining first and last names. Find name in full name

10.04.2024

Often, when drawing up an agreement, invoice, commercial proposal or other document, we work with the client’s last name, first name and patronymic. Sometimes it is necessary to separate the full name and get only the first name in order to address the client in the text of the document; sometimes you need to make a column with the signatory, leaving the last name and initials.

Certainly you can write the formula, which will extract the necessary data from the full name, but even an experienced person will need 5-7 minutes to do this. What if you don’t know the location of the last name in the text in advance? Much easier and more convenient use ready-made functions from VBA-Excel add-ins.

Find last name in full name

After installing the add-on, a new feature will be available =LAST NAME(full name), Where

Full name- text or link to a cell that contains the full name

The purpose of this function is to extract only the last name from a cell. Moreover, the order is not important to her. The surname can appear either at the beginning, at the end or in the middle of the text.

Find name in full name

Similar function for name =NAME(full name). It extracts a name from a text with a full name, no matter where it is located.

Find middle name in full name

The latest feature in this series is the =PATERNICAL(FULL NAME) to extract the middle name.

Examples of working with full name in Excel

Display last name and initials

One of the common tasks when drawing up documents is converting text of the form Last Name First Name Patronymic to the text Last Name I. O. You can solve it using the following formula:

LAST NAME(A1)&" "&LEFT(NAME(A1))&". "&LEFT(PATERNAME(A1))&"."

It is somewhat long, but believe me, it would be much shorter and clearer if we did not use functions from the add-in.

Another plus is that the location of the last name in the text is not important to us; the function will determine everything automatically.

We previously considered the possibility using the example of dividing a full name into its component parts. To do this, we used the Excel Text by Columns tool.

Undoubtedly, this is a very important and useful tool in Excel that can greatly simplify many tasks. But this method has a slight drawback. If, for example, you are constantly sent data in a certain form, and you constantly need to divide it, then this takes a certain time, in addition, if the data was sent to you again, then you will need to do all the operations again.

If we look at the example of dividing a full name, then it will be possible to divide the text using Excel text formulas, using the and function, which we discussed in previous articles. In this case, you just need to insert data into a specific column, and the formulas will automatically split the text as you need. Let's start looking at this example.

We have a column with a list of full names, our task is to place the last name, first name and patronymic in separate columns.

Let's try to describe the action plan in great detail and divide the solution to the problem into several stages.

First of all, we’ll add auxiliary columns for intermediate calculations to make it clearer for you, and at the end we’ll combine all the formulas into one.

So, let's add columns position 1st and 2nd spaces. Using the FIND function, as we already discussed in the previous article, we will find the position of the first space. To do this, in cell “H2” we write the formula

FIND(" ";A2;1)

Now we need to find the sequence number of the second space. The formula will be the same, but with a slight difference. If we write the same formula, the function will find us the first space, but we need the second space. This means that it is necessary to change the third argument in the FIND function - the starting position - that is, the position from which the function will search for the searched text. We see that the second space is in any case after the first space, and we have already found the position of the first space, which means by adding 1 to the position of the first space we will tell the FIND function to look for the space starting from the first letter after the first space. The function will look like this:

Let's start dividing the first part of the text - Surnames

To do this, we will use the function, let me remind you of the syntax of this function:

PSTR(text; start_position; number_characters), where

text is a full name, in our example it is cell A2;
start_position- in our case it is 1, that is, starting from the first letter;
number_characters - we see that the surname consists of all characters, starting from the first letter and up to the 1st space. And we already know the position of the first space. This will be the number of characters minus 1 character of the space itself.

The formula will look like this:

PSTR(A2 ;1 ;H2-1 )

Let's start dividing the second part of the text - Name

Again we use the function =PSTR(text; start_position; number_characters), where

text is the same full name text, in our example it is cell A2;
start_position- in our case, the Name begins with the first letter after the first space, knowing the position of this space we get H2+1;
number_characters - the number of characters, that is, the number of letters in the name. We see that our name is between two spaces, the positions of which we know. If we subtract the position of the first space from the position of the second space, we get the difference, which will be equal to the number of characters in the name, that is, I2-H2

We get the final formula:

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

Let's start dividing the third part of the text - Patronymic

And again the function =PSTR(text; start_position; number_characters), where

the text is the same full name text, in our example it is cell A2;
start_position- Our patronymic is located after the 2nd space, which means the initial position will be equal to the position of the second space plus one sign or I2+1;
number_characters - in our case there are no signs after the Patronymic, so we can simply take any number, the main thing is that it is greater than the possible number of characters in the Patronymic, I took a number with a large margin - 50

We get the function

PSTR(A2 ;I2+1 ;50 )

Next, select all three cells and drag the formulas down and get the result we need. You can finish here, or you can write intermediate calculations of the position of spaces into the text division formulas themselves. It's very easy to do. We see that the calculation for the first space is in cell H2 - FIND(" ";A2;1) , and the calculation for the second space is in cell I2 - FIND(" ";A2;H2 +1) . We see that in the formula of cell I2 there is H2, we change it to the formula itself and we get a nested formula in cell I2

We look at the first formula for selecting the Last Name and see where H2 or I2 occurs here and change them to the formulas in these cells, similarly with the First Name and Last Name

Last name =PSTR(A2,1,H2 -1) we get =PSTR(A2,1,FIND(" ",A2,1) -1)
Name =PSTR(A2;H2 +1;I2 -H2) we get =PSTR(A2;FIND(" ";A2;1) +1;
FIND(" ";A2;FIND(" ";A2;1)+1)-FIND(" ";A2;1) )
Patronymic =PSTR(A2;I2 +1;50) we get =PSTR(A2; FIND(" ";A2;FIND(" ";A2;1)+1)+1;50)

Now intermediate position calculations with a space can be safely removed. This is one of the techniques when, for simplicity, intermediate data are first looked for, and then the function is nested one inside the other. Agree, if you write such a large formula at once, it’s easy to get confused and make mistakes.

We hope that this example clearly showed you how useful Excel text functions are for working with text and how they allow you to divide text automatically using formulas of the same type of data. If you liked the article, we will be grateful for clicking on +1 and I like it. Subscribe and join our VKontakte group.

Example 2: How to split text into columns in Excel using a formula

Let's consider the second example, which is also very often encountered in practice. The example is similar to the previous one, but there is much more data that needs to be divided. In this example, I will show a technique that will allow you to quickly resolve the issue and not get confused.

Let's say we have a list of numbers separated by commas, we need to split the text so that each number is in a separate cell (instead of commas, these can be any other characters, including spaces). That is, we need to break the text into words.

Let us remind you that this problem can be solved manually (without formulas) using the method we have already considered. In our case, we need to do this using formulas.

First we need to find a common division by which we will split the text. In our case, this is a comma, but for example, in the first task we divided the full name and the separator was a space. Our second example is more universal (more convenient when there is a large amount of data), so for example we could conveniently divide not only the full name into separate cells, but the whole sentence - each word into a separate cell. Actually, this question came up in the comments, so it was decided to supplement this article.

For convenience, we will indicate this separator in the adjacent column so that we do not have to write it in the formula, but simply refer to the cell. This will also allow us to use the file to solve other problems by simply changing the separator in the cells.

Now the main essence of the technique.

Step 1. In the auxiliary column we find the position of the first separator using the FIND function. I will not describe the function in detail, since we have already looked at it earlier. Let's write the formula in D1 and extend it down to all lines

FIND(B1;A1;1 )

That is, we are looking for a comma in the text, starting from position 1

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

First, let's fix the column of the desired value and text so that when dragging the formula to the right, the cell references do not move. To do this, you need to write a dollar before the column B and A - either manually, or select A1 and B1, press the F4 key three times, after which the links will become absolute, not relative.

Secondly: we will calculate the third argument - the beginning of the position as the position of the previous separator (we found it above) plus 1, that is D1+1 since we know that the second delimiter is exactly after the first delimiter and we do not need to take it into account.

Let's write down the formula and drag it down.

Step 3. We find the positions of all other separators. To do this, extend the formula for finding the second separator (step 2) to the right by the number of cells equal to the total number of separately divided values with a small margin. Let's get all the separator positions. Where the #Value error means that the values have run out and the formula no longer finds delimiters. We get the following

Step 4. We separate the first number from the text using the PSTR function.

PSTR(A1;1 ;D1-1 )

Our starting position is 1, we calculate the number of characters as the position of the first separator minus 1: D1-1 drag the formula down

Step 5. We also find the second word using the PSTR function in cell P1

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

The starting position of the second number begins after the first comma. We have the position of the first comma in cell D1, add one and get the starting position of our second number.

The number of characters is the difference between the position of the third separator and the second and minus one character, that is, E1-D1-1

Let's fix column A of the source text so that it does not move when dragging the formula to the right.

Step 6. Let's stretch the formula obtained in step 5 to the right and down and get text in separate cells.

Step 7 In principle, our problem has already been solved, but for beauty, in the same cell P1 we will write a formula that catches an error, replacing it with an empty value. You can also group and collapse auxiliary columns so that they do not get in the way. We get the final solution to the problem

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

Note. We did the first position of the separator and the first division of the word differently from others and because of this we could only extend the formula from the second values. While writing the problem, I noticed that the problem could be simplified. To do this, in column C it was necessary to enter 0 for the value of the first separator. After this we find the value of the first separator

FIND($B1,$A1,C1+1)

and the first text as

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

After this, you can immediately extend the formula to the remaining values. I leave this option as an example for downloading. In principle, the file can be used as a template. Insert data into column “A”, specify a separator in column “B”, stretch the formulas to the required number of cells and get the result.

Attention! The comments noted that since we do not have a delimiter at the end of the text, we do not count the number of characters from the last delimiter to the end of the line, so the last delimited text is missing. To solve the issue, you can either, in the first step, add an auxiliary column next to the source text, where you can combine this text with a separator. This way we will have a separator at the end of the text, which means our formulas will calculate its position and everything will work.

Or the second solution is in step 3, when we compose a formula for calculating the positions of the separators and supplement it. Check, if there is an error, then indicate a obviously large number, for example 1000.

IFERROR(FIND($B1,$A1,C1+1),1000)

The function receives as a parameter a text string in the form "Last name first name patronymic", and cuts off the first name and patronymic, leaving only the initials - in the form "Last name I. O."

This function can be used as UDF (User Defined Functions) in Excel sheet cells.
(see example in attachment)

Description of the features of the function - on the forum: programmersforum.ru/showpost.php?p=757147&postcount=6

There is no exact formal algorithm for dividing a full name into parts.
It is impossible to formally separate a surname from a given name without knowing the genealogy and language of the native speaker. You can only use a variety of heuristics.

In addition, different authorities (at different times) held different views on how initials should look in a given case.
Federal Migration Service documents are one thing, Central Bank registrars are another, and the preparation of commercial documents is another.
Thus, some services require that 2-3 complex short names of Southeast Asia are not abbreviated. But some don’t.

Yes, with the question of where the initials should be on the right or left - there is no unambiguity. No, of course there are rules of the Russian language... But the rules of business turnover of one or another structure prevail (or simply as the left hind leg of the clerk accepting documents wants)...

Many may have a question: Where does this whole zoo come from and why is it all needed? As a rule, all these Don Pedros are foreign citizens receiving a Russian passport on the basis of documents previously issued outside the jurisdiction of the Russian Federation. Legal situations are different. And quite often a transliteration into Cyrillic from the native language is written into the new Russian passport.
Russian passports from -ogly and -kyzy are now issued in Tatarstan. I met a client whose name according to his 2007 passport was Mustafa Oleg ogly...

Option Compare Text Public Function Initials(s As String , Optional ToLeft As Boolean = False ) Dim sv As Variant , sФ As String , sИ As String , sО As String , i As Long , k As Long Application.Volatile True If InStr(s , "." ) > 0 Or Len(Trim$(s)) = 0 Then Initials = s "Initials specified explicitly or empty string Exit Function End If "Normalize input string 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(s) sИ = vbNullString: sО = vbNullString: sФ = vbNullString i = UBound (sv) If 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 Select Case Right$(sv(i), 3) Case "vich" , "vna" If i >= 2 Then "Standard ending for Russian patronymics sО = СropWord(sv(i)) Else "Name like Bosan Slavich sИ = СropWord(sv(i)): sФ = sv(0) End If i = i - 1 Case Else k = InStr(sv(i), "-" ) If k > 0 Then Select Case Mid$(sv( i), k + 1) Case “ogly”, “kyzy”, “zade”, “ugli”, “uuly”, “ool” "Variant of nasab "-ogly" and "-zade" type Mahmud-ogly sО = UCase(Left$(sv(i), 1)) & "." i = i - 1 If i = 0 Then sИ = sО sО = vbNullString End If End Select ElseIf i > 2 Then Select Case sv(i - 1) Case "ibn" , "bin" , "bin" sО = UCase(Left $(sv(i), 1)) & "." "Usertal 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 If End Select End Select Select Case sv(0) Case "de" , "del" , "dos" , "sept" , "van" , "fon" , "tsu" If i >= 2 Then 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 "We haven't decided on a last name yet. sФ = StrConv(sv(0), vbProperCase) If Len(sИ) = 0 Then sИ = СropWord(sv(1)) End If End Select If ToLeft Then Initials = sИ & sО & " " & sФ Else Initials = sФ & " " & sИ & sО End Function Public Function СropWord(s As Variant ) As String If Len(s) = 1 Then СropWord = s Else ss$ = UCase(Left$(s, 1)) & "." : k = InStr(s, "-" ) If k > 0 Then ss$ = ss$ & "-" & Mid$(s, k + 1, 1) & "." СropWord = ss$ End If End Function

The attachment	Size

List in Excel can be corrected with formulas - replace first and middle names with initials, combine words from cells into a sentence, insert words into an Excel list.
We have a table where the last name, first name and patronymic are written in different cells. We need to place them in one cell. Manually rewriting the list takes a long time. But, in the Excel table, there is a special function.There are two options.
First option.
We have this list.
We need to write our full name in cell D1 in one sentence.We write the formula in this cell (D1). Click on the cell (make it active).
Go to the “Formulas” tab in the “Function Library” section, select “Text”, and select the “CONCATENATE” function.In the window that appears, we indicate the addresses of the cells that we need to combine into one sentence. It turned out like this.

Full name is written without spaces. To fix this, the formula needs to be improved.Between the cell addresses after the semicolon write" " . The result is the following formula.
=CONCATENATE(A1;" ";B1;" ";C1)
It turned out like this.
Now copy the formula down the column.
Second option.
Instead of the CONCATENATE function, you can simply press the ampersand (&) button.The formula will look like this.
=A2&B2&C1
The result is the same as in the first option. If there are no spaces between words, then insert a space (" ").
The formula will be like this.=A2&" "&B2&" "&C2
You can combine not only words, but also numbers. Can make a sentence from cell data in Excel.
You can set formulas in the required cells of the form.For example, we have a list of clients with addresses.

We need to make a proposal. We write the formula in the cell.
=A2&" "&B2&" "&C2&" "&"lives at"&" "&"g."&" "&D2&" "&"street."&" "&E2&" "&"d."&" "&F2& "."
This was the proposal.

We use this principle to draw up any proposals.
If the text in the cells is already written, but we need insert additional words before existing ones, then this can be done using the formula.We have this list.
We need to insert the word “Tenant” before the last names.In the cell of the new column we write the formula.
="Tenant"&" "&A8
Copy this formula down the column. The result is the following list.
The first column can be hidden or the value of a new column without formulas can be copied, and the first column and the second with formulas can be deleted.
Another way to add text, numbers, symbols to text in a cell, see the article " Add text to Excel text cells ".
Using formulas, you can convert a list, where the first name, middle name and last name are written in full, into list with last name and initials. For example, the cell says:
In the next column we write the following formula.
=CONCATENATE(LEFT(SPACE(A1),FIND(" ",SPACE(A1),1)),PSTR(SPACE(A1),FIND(" ",SPACE(A1),1)+1,1);" .";PSTR(SPACE(A1);FIND(" ";SPACE(A1); FIND(" ";SPACE(A1);1)+1)+1;1);."")
Happened.
If there are extra spaces between words, you can remove them. Read more about this in the article " How to remove extra spaces in Excel". Using the same methods, you can remove spaces between numbers in a formula, since extra spaces can lead to an error when calculating or the formula will not count.
You can move data in a row from the last cells to the first, reverse the line. For example, in the cells it is written: in the first cell is Ivanov, in the second - Maria. We need to write Maria in the first cell, Ivanova in the second.How to do this quickly in a large table, see the article "".