Most likely, when you work with Excel workbooks, data could be in a way that does not fit your needs. And for this, you need to make changes to it so that it can become a useful structure. At times, the data of one cell into different cells need to be split. Or else, you may need to merge the data from more than one column into one.
Concatenation is a process in which you do combining the data. This process is used in Excel for names, addresses, times, or dates.
What is Concatenate in Excel?
As you have a brief overview of Concatenate, you must know that you can execute this process using two different methods:
- Merging cells
- Concatenating Cells’ Values
Merging cells lets you combine two or more cells in a single cell physically. In the end, you will get one large cell surrounded by multiple rows or columns.
On the other hand, concatenating cells in Excel lets you join the contents of cells. In simple words, concatenation is a process of merging two or more values in one cell. More likely, you can combine a part of the text that reside in different cells or add a formula calculated in the mid of some text.
Here you will get to know Excel Concatenate Strings by using multiple methods:
How to Merge Data using the CONCAT Function
A highly recommended method for combining data is by using the CONCAT function. For this, you can follow the steps given below:
- Click on a cell for combining the data.
- Now, enter =CONCAT(.
- Select the cell that needs to be combined first.
- You can add spaces, text, or commas as well. with quotation marks, you can separate the cells that needed to combine.
- Put the formula within parenthesis as, =CONCAT(A2, “Family”).
Excel CONCATENATE Strings Function
Using the Excel CONCATENATE function, you can merge around 30 text items that return the outcome as a text. Below you will see the Excel Concatenate function Syntax:
=CONCATENATE (text1, text2, [text3],…)
Concatenating the Values of Several Cells
When you need to CONCATENATE the values of two or more cells A2 and B2:
=CONCATENATE(A2, B2)
You will see the values without any delimiters. And you can use “ ” to separate the values with space.
=CONCATEANTE(A3, “ ”, B3)
Concatenating text string and cell value
The Excel Concatenate string function can even merge the text strings to make the outcome even more appealing. For instance:
=CONCATENATE(A2, “ ”, B2, “ completed”)
If you notice the above formula, you will find that a specific task is executed as in row 2. The phrase “Completed” is added here just to separate the concatenated text string. Another argument is added (“ ”) between the combined values. Using the concatenation operator the formula would be as:
=A2 & “ “ & B2 & “ completed”
Similarly, you can put a text string at starting or in the mid of the concatenation formula. For instance:
=CONCATENATE(“See ”, A2, “ ”, B2)
=“See “ & A2 & “ “ & B2
“&” Operator to Concatenate Strings in Excel
The Ampersand sign (&) is an effective way used for concatenating cells. Using this method is helpful because the ampersand sign is faster than the word “Concatenate”. For instance, when you need to concatenate two cell values using a space in between the formula would be as:
=A2&” “&B2
Things to Consider?
You always have to follow a system that ensures CONCATENATE function delivers the right outcome always.
A minimum of one “text” argument must be there in the CONCATENATE function to work it properly.
Always the outcome of CONCATENATE function is in the form of a text string, even when all the source values are integers.
In case, when the arguments of CONCATENATE function are not valid, the formula returns a #VALUE!Error.
You always have to put commas between the concatenated items as, if you need to type the phrase “please give me sandwich” your formula would be:
=CONCAT(“please”, “ ”,”give”,” “,”me”,” “,”sandwich”)
You can see that each item is separated with designated spaces and you can even add additional spaces once each text string to avoid typing them separately in the formula. Suppose you type =CONCAT(“Hello””there”), without adding a comma, you will get the results:
Hello” there
Here you can witness an extra quotation mark because there is no comma added for separating arguments.
On the other hand, if you find the “#NAME?” error rather than desired result, it clearly shows that you didn’t add some quotation marks. The “#VALUE!” error shows that some of the arguments are not valid.
