More often than not, we can find ourselves in a situation where we need to merge our text in different cells into one cell.
This can be pretty useful. For this, we can use CONCATENATE function.
Explaining the Concatenate Function
This function is one of Excel’s Text Functions. It helps you to join two or more strings into one big string.
The formula of the function is:
1 |
=CONCATENATE(text1, [text2], …) |
As seen, it has two arguments:
Text1 (required)- Here we define the first thing that we want to merge. It can be in form of the text (it has to be within quotation marks to work), a cell reference, or a number.
Text2 (required)– Then we have to define other items that we want to merge. We can merge up to 255 items, and up to 8192 characters.
Using Concatenate to Merge the Date
For our example, we will create a table in which we have three columns: Month, Day, and a Year.
We want to merge all of the cells in rows in one cell which will be our date. For this, we will use CONCATENATE function and our formula will be:
1 |
=CONCATENATE(A2,"/",B2,"/",C2) |
We are using cell references and „/“ to create our formula. Our first text field is cell A2, then we input the „/“ sign, to have the date as neat as possible. We then add cell B2 (the day), another „/“ sign, and finally, we add cell C2 (the year).
We will drag our formula to the end of the table, and we will finally have our table set.
It looks like this:
Using Concatenate to Merge the Date and Text
To merge the date that we created with random text, we will first create the text. It will be a list of two possible statuses: approved and declined. The text and the date are not related. It is, for example, purposes only.
To merge these two columns by rows, we will use CONCATENATE function again.
Our function will be:
1 |
=CONCATENATE(E2," on ",D2) |
We did not just merge these two cells but added the text (the word „on“) as well as blank spaces to make the sentence more logical.
Our table looks like this:
Make Sure to Keep the Date Format
Sometimes, it could be troublesome to merge the dates in this way as there is a possibility that the format will change. To resolve this issue and be sure that this does not occur, we will use the TEXT function for our help.
The result will be the same, but this formula is useful if you have the issues that are mentioned above.
The TEXT function looks like this:
1 |
=TEXT (value, format_text) |
It has two parameters- value (the text that we want to format) and format_text (how we want to format our text).
We will insert this formula as one of the parameters for CONCATENATE function. Our formula will be:
1 |
=CONCATENATE(E2," on ",TEXT(D2,"mm/dd/yyyy")) |
and the value in the G2 cell will be as follows:
We will expand the formula till the end of our table. Columns G and F will be the same. But remember to always use the TEXT formula when merging the date to ensure that the format will not be changed.