Excel provides the user with a large number of useful and convenient features. An experienced user can use a spreadsheet file to create a powerful system with automatic calculations, substitutions, statistics and totals, drawing charts and graphs. In this article, we will consider one of the very useful features - the "Link" function in Excel.
Link Function
Spreadsheets allow you to perform calculations with data. For this, formulas are used that perform calculations — arithmetic operations of varying complexity. For more complex data operations, functions are used. Their advantage is that they work not only with numeric data, but also with text values, logical parameters, date and time, etc.
The Link function in Excel is one of those designed to work with string values of cells. The user needs it if it is necessary to combine several text fields into one. Very often it is used to combine the last name, first name and patronymic, which are stored in different columns, into a single field, full name. But there are many applications of this option, except for this standard case.
If you are using a non-Russian version of the program, then in English the "Link" function in Excel is called "Concatenate". Otherwise, everything described below corresponds to the English version - syntax, application and examples.
Application
The operation has the following syntax: LINK ([text2]; [text2]; ...). The number of string attributes can be arbitrarily large; the main thing is that the resulting text matches the requirement for the length of the resulting field. These attributes can be either predefined values or data on an Excel worksheet. However, the data types of the elements to be combined do not have to be textual.
To enter attributes, use the function insertion form. To do this, enter the equal sign in the desired cell and click the button with the fx icon. In the window that opens, select the desired item and click OK.
A new window for entering arguments appears. You write text elements directly in the fields, cell links can be written in the fields or just select the ones you need in the table itself:
Function parameters can also be entered directly into the cell contents window. In our example, the operation string will look like this:
= CLICK (D2; "."; E2)
As a result, we will get the text 21.09 in the selected cell, consisting of the data of the cell D2 - the number 21, the point and the data of the cell E2 - the string "09". The information in the E2 field is a number, but formatted as text, since it is important that the figure 0 is in the first place. If this number is converted to a number format, this zero will disappear and only the number 9 will remain.
As you can see, now we concatenated the data of two cells, one of which was a number, and the second was text, and we added a separate dot character, getting the line created by the merger of these values in the resulting field.
Examples
Here are a few more examples of the Link function in Excel. First of all, finish what we started above and turn the information in cell G2 into a full-format date of birth of the client. To do this, we need to combine in one line the birthday, month and year, contained, respectively, in cells D2, E2 and F2. Between these values it is necessary to put periods.
In the arguments window, 2 more fields will be added, as a result it will look like this:
The function string will look like:
= CLICK (D2; "."; E2; "."; F2).
As you can see, specially entered values — in our example, these are dots — are indicated by quotation marks in a string.
As a result of the expression, in cell G2 we get the date 09/21/1990. We fill in the remaining fields of the column with a similar expression using the option to copy information, that is, by pulling the cross in the lower right corner of G2 to the end of the table.
By analogy, in the same table, using the "Link" function in Excel, we collect data on customer names and get their full name in one column from the divided parts. Use the expression = LINK (A2; ""; B2; ""; C2). Please note that there are spaces as separators of the individual parts of the name. They must always be remembered in such operations.
The resulting table is shown in the picture:
Inverse function
Sometimes the user needs to perform the opposite task: to divide the value in the cell into components according to some criterion. There is no reverse function to CLICK in Excel, but there are several simple ways to achieve the desired result.
The first is the use of the “Data by Columns” option in the “Working with Data” panel of the “Data” tab. When you call this feature, a separation wizard appears in which you set the parameters:
- How to divide: based on the separating characters or the number of characters.
- If with separating signs, then which ones to consider as such.
- You determine the parameters of each of the received columns, and in which columns to write the values.
The second way is to use the VBA Split command. Its syntax is:
Split (Expression, [Separator], [Number of lines], [Comparison]).
The Expression attribute is required - this is a string to split. "Separator" shows the character by which the source text is divided into parts, by default it is a space. "Number of rows" defines the maximum number of resulting values. "Comparison" - a method that determines the sensitivity to the register of characters.
Other string merging options
The analogue of the CONNECT function in Excel is the ampersand sign - &. In our example, it is applied in this way:
= A2 & "" & B2 & "" & C2.
As a result, we get the same values as before:
In Excel version 2016, a new STEP () function has appeared. Its difference is that it has only one attribute - a range of cells. You can combine field values by highlighting a range, rather than entering each cell separately.
The "Link" function in Excel is extremely useful in working with the program. Based on existing data, new fields are created by combining them in the desired order and combination.