How to Combine Text Strings in Excel – Shiksha Online

How to Combine Text Strings in Excel – Shiksha Online

6 mins read1.5K Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Jan 10, 2024 10:30 IST

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.

How to Combine Text Strings in Excel

Content 

Concatenate Function in Excel

Concatenate in Excel combines text strings from multiple cells 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 combine up to 30 text elements 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.

Introduction to MS Excel – A Beginner’s Guide
Introduction to MS Excel – A Beginner’s Guide
Microsoft Excel has been around for decades and is one of the most desired skills if you are considering a career in data science or analysis. But there are still...read more

MIN and MAX Functions in MS Excel
MIN and MAX Functions in MS Excel
Learn about MIN and MAX functions in MS Excel in this blog.
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 I aim to create their full names in column E.

Concatenate Function in Excel 1

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.

Concatenate Function in Excel 2

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

Concatenate Function in Excel 3

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

Concatenate Function in Excel 4

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

Concatenate Function in Excel 5
Tutorial – VLOOKUP in Excel – Shiksha Online
Tutorial – VLOOKUP in Excel – Shiksha Online
VLOOKUP, or vertical lookup, is one of Excel’s most commonly used functions for data analysis. This function belongs to the Search and Reference group, which has other search functions in...read more
Average Function in Excel – Formula and Examples
Average Function in Excel – Formula and Examples
The AVERAGE function is a popular Excel function used for various purposes, such as calculating average sales, average student scores, average monthly expenses, etc. It provides a quick and efficient...read more
COUNT Function in Excel – Shiksha Online
COUNT Function in Excel – Shiksha Online
Read on the article to understand how COUNT, COUNTA, and COUNTIF functions work in MS Excel.

& Function in Excel  

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

Microsoft has created an operator that performs the same tasks because the CONCATENATE function is used quite frequently and is a simple function. 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. Combining one or more cells with text allows you to reproduce complex situations understandably. 

We will use & to join the text strings in the same data set. 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

& Function in Excel 1.1

Press enter to get the full name.

& Function in Excel 1.2

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

& Function in Excel 1.3
TRIM Function in Excel – Shiksha Online
TRIM Function in Excel – Shiksha Online
If you are dealing with datasets from Excel, you know that you have to deal with characters that you’d rather have removed, such as unnecessary spaces. This happens especially if...read more
Sum Function in Excel – Learn Through Examples
Sum Function in Excel – Learn Through Examples
In continuation to our previous tutorial on MIN and MAX functions in Excel, we move on to another important basic function, the SUM function. Learn about the SUM function in...read more

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. For example, if you want to separate different parts with blanks 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&””

& Function in Excel 2.1

In this formula,

E2: Full name of the employee

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

& Function in Excel 2.2

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

& Function in Excel 2.3

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

FAQs - How to Combine Text Strings in Excel

Can I combine text strings from non-adjacent cells in Excel?

Yes, you can combine text strings from non-adjacent cells in Excel by specifying each cell reference within the CONCATENATE function or '&' operator. For example, "=A1&B1&D1" will combine text from cells A1, B1, and D1.

Is there a limit to the number of text strings I can combine in Excel?

Excel has a limit on the number of characters in a cell (usually 32,767 characters). So, the limit for combining text strings depends on your worksheet's total character count and available space.

How do I concatenate text strings in programming languages like Python or JavaScript?

In programming languages, you can concatenate text strings using the appropriate syntax. In Python, you can use the '+' operator (e.g., "text1 + text2"). In JavaScript, you can use the '+' operator or template literals (e.g., text1 + text2 or ${text1} ${text2}).

How do I add spaces or other characters between text strings when combining them?

To add spaces or other characters between text strings, use the CONCATENATE function or '&' operator to include those characters within quotation marks. For example, "=A1&" "&B1" will add a space between the text in cells A1 and B1.

Can I combine text strings with conditional statements in Excel?

Yes, you can combine text strings with conditional statements in Excel. You can use functions like IF, CONCATENATE, and TEXTJOIN to create dynamic text strings based on conditions within your worksheet.

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments