How to Combine Text Strings in Excel – Shiksha Online

The article focuses on using CONCATENATE and & functions to combine text strings.

Microsoft Excel facilitates complicated calculations and data-heavy evaluations. While analyzing data, you may want to combine several elements into a single result. Excel’s CONCATENATE and & functions make it easy by incorporating different results and contents into a cell. In this article, we will explain how these functions work to combine text strings.

Content 

• Concatenate Function in Excel
  • Example 1 – Use of Concatenate Function
• & Function in Excel
  • Example 2 – Use of “&” Function
  • Example 3 – Use of “&” Function

Concatenate Function in Excel

Concatenate in Excel combine text strings ​​from multiple cells into one cell or joins different pieces of text into one cell. This function is mainly used when the data is unstructured in Excel and when we want to combine the data from two or more columns into one column or one row. Concatenate is very useful for structuring the data according to our requirements. You can select the cells you want to merge and apply the formula. It combines text strings and is a built-in function in Excel.  

Concatenate can join up to 30 text elements and combine them to create a text.

Category: Text Functions

Use: The CONCATENATE function in MS Excel joins two or more text strings into one.

Syntax: 

=CONCATENATE(arg1;arg2;…;arg_n)

This function has two arguments:

Text1 – This is the first item to join and is a required argument. The item can be a text value, cell reference, or number.

Text2 – The additional text items that we wish to join. Text 2 is also a mandatory argument. We can join up to 255 items, up to 8192 characters.

By default, Excel calls parameters within a function “text” because the formula is primarily designed to join multiple elements into text. However, you can combine different formats

Text: Text is written in Excel between quotation marks. Thus, the program knows how to process the information.

Numbers: the numbers can be entered directly into the formula. 

Cell references: If you want to pass the contents of a cell to the function, insert the unmerged cell reference as an argument in the formula. You can insert the reference relative (no mark) or absolute (with a dollar sign as a mark).

Functions: you can insert other functions in CONCATENATE. The result of these functions is linked to the rest of the elements. We will use the SUMIF function in Example 3.

Example 1 – Use of Concatenate Function

You have an employee’s first name in column C and their last name in column D. You want column E to contain their full name, but it’s time-consuming to retype it in each cell.

In the screenshot below, I am working with employee data. I have the first and last names in separate columns, but the goal is to create their full names in column E.

I have the names data in Columns B and C. In Cell E2, I will write the formula – 

=CONCATENATE(B2,” “,C2)

In this formula –

B2 = First instance of the first name

“”= Space (add a space to separate the words)

C2= First instance of the last name

The CONCAT function in Excel 2016 or later produces precisely the same result as the CONCATENATE function. Just replace CONCATENATE with CONCAT in the formulas shown above.

Press Enter. You will see that Cell E2 now has the complete name from the data fetched from B2 and C2.

Double click on the green square appearing at the end of the cell. 

The formula applies to the cells you select, and you will get the full names.

& Function in Excel  

Another way to join content from different cells into one without using the CONCATENATE function is by using the “&” character (&). 

Because the CONCATENATE function is used quite frequently and is a simple function, Microsoft has created an operator that performs the same tasks. You already know the operators from other processes. For example, you can use the plus symbol instead of the SUM function.  

Here is how you can do it –

• Select the cell where you want the result to appear 
• Start typing = and select the first cell you want to combine
• Type & and use quotation marks with a space enclosed
• Select the next cell you want to combine and press enter

Example 2 – Use of “&” Function

The value of cell A1 (in this case, the reference is absolute; it does not change, even if the formula is moved) is linked with a text. So that the word doesn’t stick directly to the value, the textual element begins with a blank space.

This way, you can generate entire sentences. By combining one or more cells with text, you can reproduce complex situations understandably. 

In the same data set, we will use & to join the text strings. Here we use the formula –

=C2&” “&D2

Where,

C2 = First instance of the first name

“”= Space (add a space to separate the words)

D2= First instance of the last name

Press enter to get the full name.

Drag the box to fill in the rest of the cells.

Example 3 – Use of “&” Function

We will use the same data set as above to understand this. In our list, we have the names and countries of the employees. Now we add these scores based on the name we want to calculate. In this process, we must use the SUMIF function as mentioned above to combine text strings.

Each element inside the function’s parentheses is pasted directly to the previous one. If you want to separate different parts with blanks, for example, to form correct sentences, you must also pass these blanks to the function as arguments. Therefore, it also inserts the white space between quotes in the function.

We want to have a complete statement about an employee’s nationality in this example. 

In Cell F2, we will fill in our formula.

=E2&” is from “&D2&””

In this formula,

E2: Full name of the employee

Is from: the text string that we want to add to our available dataset

Similarly, you can double click or drag the formula to the below cells to apply the formula and get the result for other employees.

The above examples should have helped you to understand how to combine text strings using Ampersand and & functions. Do try joining cells on your datasets.

Top Trending Articles:

Data Analyst Interview Questions | Data Science Interview Questions | Machine Learning Applications | Big Data vs Machine Learning | Data Scientist vs Data Analyst | How to Become a Data Analyst | Data Science vs. Big Data vs. Data Analytics | What is Data Science | What is a Data Scientist | What is Data Analyst