MacGyver used it. The Apollo 13 crew also used it. Always in a difficult situation, when you need to link two things, people pick up tape. You may be surprised, but Excel has a built-in function that does the same. This is a function CONCATENATE (CLUTCH).
Function CONCATENATE (CONCATENATE) allows you to link two or more pieces of text in one cell. Despite the long name, it is very easy to use and works the same in all versions of Excel as well as other spreadsheets such as Google Sheets.
Note: If you have never used Excel functions before, you can refer to the section Formulas and Functions See our Excel Tutorial for Beginners for a series of tutorials on this topic.
Linking names
Suppose we have a table with contact information where first and last names are in different columns. We want to link them and get the full name for each person. In the picture below you see the names in a column B, and last names in the column A. Our formula will be in a cell E2.
Before we start entering the formula, understand an important point: the function STsEPIT will only bind what you specify and nothing else. If you want punctuation marks, spaces, or something else to appear in the cell, add them to the function arguments.
In this example, we want a space between the names (to avoid something like – JosephineCarter), so we will need to add a space to the arguments. Thus, we will have three arguments:
- B2 (First Name) – name
- “” – space character in quotation marks
- A2 (Last Name) — surname
Now that the arguments are defined, we can write to the cell E2 here is the formula:
=CONCATENATE(B2," ",A2)
=СЦЕПИТЬ(B2;" ";A2)
As with any other Excel function, syntax is important. Remember to start with an equal sign (=) and put delimiters (comma or semicolon) between the arguments.
Note: put a comma or a semicolon between the arguments – depends on what country you live in and what version of Excel you use.
That’s all! When you press Enter, the full name will appear: Josephine Carter.
Now, by dragging the autofill handle, copy the formula to all cells up to E11. As a result, the full name will appear for each person.
If you want to complicate the task, then try using the function STsEPIT link city and state in a column Fto look like the picture below:
Associating numbers and text
Using functions STsEPIT You can even link numbers and text. Let’s imagine that we use Excel to store inventory records for a store. Now we have 25 apples (apples), but the number “25” and the word “apples” are stored in different cells. Let’s try to link them in one cell to get something like this:
We need to link three elements:
- F17 (Number in stock) — quantity
- “” – space character in quotation marks
- F16 (Product) – name
Enter the following formula in a cell E19:
=CONCATENATE(F17," ",F16)
=СЦЕПИТЬ(F17;" ";F16)
Let’s make it harder! Let’s say we want to get: We have 25 apples (We have 25 apples). To do this, you need to add one more argument – the phrase “We have”:
=CONCATENATE("We have ",F17," ",F16)
=СЦЕПИТЬ("We have ";F17;" ";F16)
You can add even more arguments if you wish to create a more complex expression. The main thing to remember is that the syntax of the formula must be extremely accurate, otherwise it may not work. It’s easy to make a mistake in a big formula!