Concatenation of Strings is done by the two concatenate methods provided to us by excel which is the & operator and concatenate inbuilt function, for example, we use =”Anand”&”Singh” in a cell will give us AnandSingh as a result and similarly we use =Concatenate (“Anand”,”Singh”) will give the same result as above.
Excel Concatenate Strings (Table of Contents)
Excel Concatenate String
In our earlier articles, we have seen so many examples of working with data structure and organization of data. Usually, we don’t get the structured data in place, often times we need to work on data manipulation and arrange the things in order. One such thing is combining or concatenating strings of multiple cells into one in excel.
A typical example of concatenating strings is “combining first name and last name together to create a full name”, this is the common examples we have seen using “Right”, “Left”, and “Mid” functions both in excel and VBA.
In this article, we will show you how to concatenate strings of multiple cells into one beyond the traditional example of First Name & Last Name.
Concatenate Formula in Excel
In Excel to work with concatenating cells, we have a built-in formula i.e. “CONCATENATE”. This function helps us to combine multiple cells data into one. It not only combines cell value, but we can also mention our own values to the function.
Let’s look at the syntax of the CONCATENATE Formula
The syntax is very easy to understand. Text1 is nothing but what is the first string we need to concatenate in excel, Text2 is what is the second string we need to concatenate with Text1. Like this, we can combine 255 values together.
Examples of Concatenate Strings in Excel
Below are the examples of Concatenating strings in excel.
Example #1
Look at the simple example of concatenating values into one. For example, in cell A1 I have “Good” and in cell A2 I have “Morning”.
In cell A4 we need to combine these two and create full sentence as “Good Morning”. Open concatenate formula in cell A4.
Select cell A1 as the first Text1.
As the second argument (Text2) select cell A2.
We have only two values to combine. So close the bracket and hit the enter key.
Oh, hold on…
We are lacking one thing here i.e. text separator. After the first value, we need “space” character to separate the two words.
In the above, we have selected two values so concatenate function combined these two values together. So while applying the formula after selecting the Text1 we need to insert space character as the second value to be concatenated before selecting Text2.
4.9 (1,353 ratings)
After selecting Text1 in the Text2 argument we need to supply the space character in double quotes as ” “.
Once the space character inserted we will get the result as follows.
Example #2
Use the Ampersand (&) Symbol as the Alternative to CONCATENATE Formula
Why do you want to use the CONCATENATE function where we can use AMPERSAND (&) symbol as an alternative?
You can use just ampersand symbol to concatenate values. For the same example of combining “Good Morning” we can use the formula as below.
After each value, we need to put the ampersand (&) symbol. This is the most commonly used method to concatenate values in excel, CONCATENATE formula is not popular among excel user thanks to ampersand symbol.
Example #3
CONCATENATE Cell Values with Manual Values
We can enter own values with cell values as well much like inserting space character. For example, look at the below data set.
We have a zone-wise sales value here. In the sentence column, we need to create a sentence like this –
“Total Sale of East Zone is 1500”
In the above sentence, we have only two values available with cells i.e. bold values. Remaining values we need to add as our own.
Put an equal sign in cell C2 to start the concatenation. As part of our sentence, our first value to be concatenated is “Total Sale of “(including space after).
Next value is our cell reference.
The third sentence to be concatenated is “Zone is “.
And the final value is cell reference.
Press Enter key to get the answer.
Drag the formula to get the concatenate values in other cells as well.
Example #4
CONCATENATE Date Values
Now, look at one more example of concatenating date values. Below is the data.
We need to combine these two values with our own sentence. Below is the sample of the same.
“Ramu Joined on 12-Jan-2018“.
Open equal sign in cell C2 and select A2 cell.
Next is our own sentence, so enter the text in double quotes.
The final cell is to concatenate is Date, so select B2 cell.
Press Enter Key.
Oh, hang on!!!! We supposed to get 12-Jan-2018 instead we got 43112.
This is the situation everybody faces whoever has learned the basic level of concatenation. When we are concatenating Date, Time, and Numbers with Formatting we need to apply the formatting with the TEXT function. So, while selecting the DATE cell select with the TEXT function.
Mention the format as “DD-MMM-YYYY”.
Press Enter key to get the answer.
The reason why we need to apply TEXT function to given format as a date because excel stores date & time as serial numbers. Whenever we combine we need to give formatting to them.
Drag the formula to get the concatenate values in other cells as well.
Recommended Articles
This has been a guide to Concatenate Strings in Excel. Here we discuss how to concatenate strings using its formula along with examples and explanations. You can learn more about excel functions from the following articles –
- Extract Number from String Excel
- How to do the Opposite of Concatenate in Excel?
- Excel CONCATENATE Formula
- How to Calculate in Excel Sheet?
- Guide to Concatenate Excel Columns
- Concatenate Strings in VBA
- Substring Functions in Excel
- VLOOKUP Basic Tutorial
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
