How to make initials in excel. Combining first and last names. This video shows in detail how to split text into columns

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.

Of course, you can write a formula that will extract the necessary data from your 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? It is much easier and more convenient to use ready-made functions from the VBA-Excel add-in.

Find last name in full name

After installing the add-on, a new function 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

A similar function for the 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 last function in this series is the function =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 the conversion of text of the form Last Name First Name Patronymic to the text Last Name I. O. You can solve it using the following formula.

How to split text into columns into two, three or more cells in Excel 2007. Usually, as an example, they use a breakdown of cells with a full name so that the data is separate.

How to split text into columns in Excel

At work, I had to divide the time in the cells of an excel table, example: 0:00-15:00. And between the values ​​the separator is a semicolon 0:00-15:00;16:00-17:00;22:00-24:00. This data is in the table by day, in cells there is a different number of values ​​between the separator. It was necessary to divide the time for each day, put the time in a column under the date, divide the time into the beginning and end, calculate the difference and add everything together...

Stage one. Divide time in cells

Select a range of cells, on the ribbon click Data-Text by Columns, the Text Wizard will appear.

We don’t change anything, if your switch is in the position: with separator, click next.

Uncheck the "tab" checkbox and check the "other" checkbox

and enter a semicolon [; ]. Click either further, although there’s nothing special to see there, or rather, click ready. The question will be asked: replace the contents of the cells? The answer is yes!

We were able to split the cell into as many values ​​as there were between [ ; ]. Let's add empty lines under the cell with the date, in the number of cells to the right of column C.

Two cells, two rows. Select cells, copy,

and under the first cell, right-click “Paste Special” - “Transpose”.

The data will be transferred from row to column, let's do the same for the remaining cells.

It turned out to divide the text in the cell, arrange it by day, in a column.

Stage two. How to divide a cell into a start and end time in Excel

Let's get the time difference by using the text wizard again. Let's continue split text in cells for start and end times. Here the separator is the minus sign.

Let's move the column out of our table by copying and pasting the cells next to each other. We work with the wizard: Data-Text by columns, the first step does not change anything, in the second step in the other [ - ] window we put a minus sign, click ready.

Stage three

We put the formula: end time minus start time,

cell format All formats [h]:mm,

Let's drag the formula down to the end. We got the time in hours. In the next cell we write: =K4*60 Enter. Let's get the time in minutes.

True, with seconds... I couldn’t find a better way than copying a column, pasting it into Notepad, pressing CTRL+H (Find and Replace), in find wrote:00

and left the replacement blank...

I clicked replace all... The text without:00 was copied from notepad and pasted into the table.

I did it easier with the number of hours, copied it into a notepad and pasted it into the table. One problem: 24 hours are worth 24:00:00.

Not a problem, since we know how to split a cell in Excel, then we can use Copy&Paste here, but with a trick - in the cell before 24:00 put an apostrophe ' -single quote "24:00"

and paste it everywhere. You can only add minutes with an autosum; we get the sum of hours by dividing the sum of minutes by 60, and the number of days we divide the resulting number of hours by 24....

In my example, something out of 31 days worked for 28 days, out of 744 hours -673 hours and out of 44640 minutes -40411 minutes... It doesn’t matter what it is, the main thing is that in this example you learned how to split text into columns in a cell in Excel, How can you use a notepad in a non-standard way and change the format of cells by adding an apostrophe to them?

This video shows in detail how to split text into columns:

We recommend watching the video in full screen mode, select 1080 HD in the quality settings, don’t forget subscribe to YouTube channel, there you will find a lot of interesting videos that come out quite often. Enjoy watching!

In columns, so that the first name, middle name and last name are in different cells. How to split text into columns in Excel?

So, there are two ways to divide text into columns.

1st way to divide text into columns. Text Wizard

Select the column with the text you want to split. Then select Data - Text by Columns in the top panel . The Text Wizard (parsing) window will appear.

If you want to separate the text by spaces or other characters, select “delimited”.

In the menu that opens, select a space or the desired character.

Select the text format or click “done”. The text is divided!

If all data is the same length, you can split by a fixed width (see picture 2)

2nd method. Split text in Excel using formulas. PSTR function

If you need to divide text into columns using a formula, use the functions =PSTR() and =FIND().

In order to find Last name enter the formula in cell B2

=PSTR(A2,1,FIND(" ",A2,1)-1)

Where the Find function will find the number of characters before the space, and the =PSTR() function will return this number of characters.

To obtain Name the formula gets more complicated (you need to let the functions know where the name starts i.e. the 1st space ends), so enter in cell C3

=PSTR(A2,FIND(" ";A2,1)+1;FIND(" ";A2;FIND(" ";A2,1)+1)-FIND(" ";A2,1))

To find Surname to cell D3 enter the formula

=PSTR(A2,I2+1,50) we get =PSTR(A2,FIND(" ",A2,FIND(" ",A2,1)+1)+1,50)

It looks a little difficult, but if you look at it you will see that everything is not difficult, especially with the example:

How to link cells First Name, Middle Name, Last Name

In order to concatenate data in cells, use the = CONCATENATE () function or a structure using & (ampersand) - the concatenation symbol, for example, enter the formula in cell D3

=B2&" "&C2

Very often this question is asked

Excel allows you to split text in column cells into several substrings and place them in separate columns. In this case, a special separator is used for the breakdown, which is selected by the user for each specific case.

For example, consider the CSV file data. Each substring in it is separated by a semicolon and placed in double quotes. Paste them anywhere in your workbook:

"Full name"; "Date of birth"; "Gender"; "City of residence"; "Profession"
"Ivanov Ivan Ivanovich";"03/27/1985";"Male";"Voronezh";"Electrician"
"Petrov Petrovich";"12.03.1976";"Male";"Moscow";"Geologist"
"Kukhtina Elizaveta Fedorovna";"12/11/1994";"Female";"Kazan";"Doctor"

Before breaking down the data, you need to select the desired range, and then click on the “Text by Columns” icon in the “Working with Data” area on the “Data” tab ribbon.

The text distribution wizard window will be displayed on the screen:

The whole process consists of three steps.

At the first stage, you need to select the data format for division, namely, indicate whether the text contains delimiters, or each future column has its own fixed width, i.e. text length. The last option will not be considered, since it is no different from the first except for the next step, where you need to independently select the width of the field on the ruler.

Select Delimited and click Next.

The next step is to select a separator character. It can be any of the characters, including letters and numbers.

Please note that if the separators are consecutive, then each of them forms a new column, i.e. 2 such symbols create 1 empty column, 3 - 2, etc. To get rid of this often necessary action, just check the “Count consecutive delimiters as one” box.

A situation may arise where the substring to create a column contains a delimiter character that is not a separator character. In such cases, the strings must be enclosed in single or double quotes (this is present in our example source data), and in the wizard window you must select which line delimiter to apply.

Be aware that delimiters and delimiters are removed from the content of the delimited data.

At any of the steps above, you can click Finish to allow Excel to finish separating the text in the column cells. But if you want to control the entire process, then continue to click “Next”.

The final step is to assign formatting to the new columns and indicate where to place them.

Only a few formats (data types) are provided, and distributed text can only be placed on the current sheet.

If the site materials helped you, please support the project so that we can develop it further.

You do not have sufficient rights to comment.

Here we will look at how to split text in an Excel cell into multiple cells. For example, a sentence from the first cell should be spread according to words into several other cells, full name, date, etc.
For example, we have a list with last names and first names in one column. We need to write last names in one column and first names in another column. For example, to determine the gender of people on a list. Read more about this in the article “In Excel, determine gender by name.”
Select the cells in which you want to separate the text or the entire column.
Go to the “Data” tab in the “Working with Data” section and select the “Text by Columns” function. In the “Text Wizard” window that appears, in the “Source data format” line, indicate “With delimiters.” Click the “Next” button.

Here we indicate in the line “The separator character is” - “space”. We put a “space” because in our list the first and last names are written separated by a space. If the first and last name (other words) were written separated by commas, then we would indicate the delimiter “comma”.

In this window, in the “Data Parsing Sample” section, you can immediately see what columns we will get. Click the “Next” button.
Here, in the “Place in:” section, we indicate the range of columns into which the new data will fit.

Attention!

The columns in this range must be empty, otherwise the information in them will be erased and replaced with new ones. If there are no empty columns, you can pre-insert columns. How to do this, see the article “How to add a row, column in Excel”.
You can specify the format of the Column Cells. For example, if you divide a column into days and months, year, then you can specify the “Column Data Format” - “date”.
Click the “Finish” button. It turned out like this.
This way you can split text from one cell into more columns. For example, here we divided the date separately into columns - day, month, year.

For greater clarity, you can highlight some rows in an Excel table and highlight columns (for example, totals) with a different color, with a dividing line. For more information, see the article "Row dividing line in an Excel table."
You can divide the cell diagonally and write text in each part. Read how to do this in the article "How to split a cell in Excel".
It is more convenient to work in a table where zeros are not visible in the cells. How to remove, hide, replace them, read the article "

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 a UDF (User Defined Function) in Excel worksheet 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 certainty. 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 "оглы" , "кызы" , "заде" "бей, бек, заде, зуль, ибн, кызы, оглы, оль, паша, уль, хан, шах, эд, эль 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 Then "Standard ending for Russian patronymics sО = СropWord(sv(i)) Else "Name type 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" , "uul" , "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" , "ben" , "bin" sО = UCase(Left$(sv(i), 1)) & "." " Usertal Alisher bin Suleiman i = i - 2 End Select Else " Ben Eduard 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" , "von" , "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 "More did not define the last name 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) & "." CropWord = ss$ End If End Function

Attachment Size

I had a hundred cases when it was necessary to get a last name or first name from a list of employees. At the same time, the list consisted of at least a couple of hundred people and it was very sad to do it manually. It’s good that Excel has all the necessary tools that allow you to optimize this process and solve the problem in a few minutes. Excel text formulas allow you to solve similar problems. Now I will explain everything to you in detail using the example of several actors kindly provided by the first page of Google.

Excel text formulas we need

To solve this problem, we need Excel itself and the following standard text functions:

SEARCH - allows you to find text or meaning in text

LENGTH - counts the number of characters in a cell with text

RIGHT - returns a specified number of characters from the end of the text

Our especially smart reader may have already guessed how to solve our problem, but I will still explain to everyone step by step how I would need it :)

To solve our problem we need:

Calculate the length of text in a full name using the LENGTH function

Find the number of the character with which the surname begins using the SEARCH function

Calculate the length of the last name as the difference between the results of points 1 and 2

Output the surname using the RIGHT function and the length of the surname from the third paragraph.

Sounds like a plan for action. Let's get started!

DLST function

This Excel text function is one of the simplest. It just needs to specify a cell and it gives the number of characters in it. You can see for yourself with an example:

SEARCH function

The search function looks for the number of a character or text for us. Considering that all last names begin with different letters, we will look for a space before the middle name and last name. The next character after this will be the beginning of the patronymic or surname.

It is important to note that the space or search text in this function is written in quotation marks. In particular, it will be something like this =SEARCH(" "; 'cell with text').

Given that the last name is after the second space, we will use two searches, one of them nested to find the first space, as in the example below:

RIGHT function

We are all ready to extract the last name from the full name and solve our problem. To do this, we specify the cell with the actor’s full name in the function arguments and calculate the number of characters as the difference between the first and second actions. As a result, we put the solution together.

Earlier we 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; starting_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; starting_position; number_characters), where

text is the same full name text, in our example it is cell A2;

initial_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; starting_position; number_characters), where

the text is the same full name text, in our example it is cell A2;

initial_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 would be grateful for clicking +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 separator is exactly located after the first separator and we do not need to take it into account.

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

Step 3. 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. 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 stretch 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 the 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 the 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. Make a check; if there is an error, then indicate a obviously large number, for example 1000.

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

Excel for Office 365 Excel for Office 365 for Mac Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 Excel Online Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones Excel Mobile Excel Starter 2010 Less

Let's say you want to create one fully qualified name column by concatenating two other columns, first name and last name. To combine first and last names, use the CONCATENATE function or the ampersand (&) operator.

Important: This function in Excel 2016Excel Mobile and Excel Online has been replaced by the UNION function. Although the CONCATENATE function is still available for backwards compatibility, COMBINES should be used from now on. This is because CONCATENATE may not be available in future versions of Excel.

Example

Copy the following onto a blank sheet of paper.

Surname Vladimir Formula Description (result) Combines the above names separated by a space (Belova) Combines the above names, separating them with a semicolon (Novikov, Pavel) CONCATENATE(A2;" ";B2)