The most commonly asked question during the junior level hiring for MIS / Data Analyst profile is “How to combine two or more strings together into one string?” or “How to combine two or more cells value into one cell using function in Excel?” And, if you are novice, then you might scratch your head in such situation and perhaps end up with doing manually if have less data and not familiar with any built-in function.
Why this is one of the hot and top listed interview question? That is because, merging two or more strings into one, is a very common job in Excel. And, this is the basic requirement a user who is working on Excel, must be proficient into.
In computer programming language, concatenation is known for combining complete strings together.
What it does?
The Excel CONCATENATE function is a part of TEXT family and used for merging two or more text string into one string.
The function can be used in many situations like; merging first name and last name from different cells into one cell, adding some default values before or after particular text string etc.
Syntax & Arguments:
text1 – (Required) The first string that you wish to merge. The item can be text string, number, or cell reference.
[text2], …, [text255] – (Optional) The additional text strings which are to be combined. It can be text strings, numbers, or cell references to the location in the worksheet. The maximum 255 text arguments up to a total of 8,192 characters, can be supplied to the function Excel 2007 and later versions. However, in Excel 2003, up to 30 text arguments can be supplied only.
- At least 1 text argument is required by function to return result
- Text strings are required to enclosed in double quotes (“ “) when supplied directly to the argument
- Commas are used to separate adjoining text strings
- To add spaces between two or more words to separate the different fields, spaces should be supplied in separate arguments enclosed in double quotes
- Function returns number output when Date or Time supplied to the argument
- Function returns #VALUE! error in case of invalid argument or range supplied to the argument.
- Function returns #NAME? when quotation marks are missing from a text argument
In the following example, we have taken two words which are located in different cells in the worksheet. The simple CONCATENATE function is used to combine the values from different cells together into one cell.
Note that the above output in cell D3, doesn’t have any space between the words and both string coupled together without any delimiter which is not readable. That is because, the function join exactly what it is asked to join. It does not include any space or any delimiter between words automatically.
To add a space in between, we have to simply add another argument which holds the space in double quotes.
In the next example, we have the situation where we have the username in one column and the domain name in another column. And, we have to get them as proper email address.
Note that the following table does not have the “@” symbol which separates the user name (local part) and the domain name. So, we have added that with the help of CONCATENATE function.
In the above example, the “@” symbol is enclosed in the quotation marks. So, in such situations, you can add any symbol or character as it is very simple to do it.
Now, you can drag or use the fill handle to copy the function to the remaining cells in the range.
Concatenating Text String and Date Together
In the next example, we have a date in one cell which we will add it along with other text strings located in different cells.
The above example shows that when the date is supplied in the argument, the function returns number instead date. Because Excel stores dates and times as numbers and just because of cell formatting, it displayed as date and time format.
Thus, if any of the argument contains date or time, the argument must be supplied in TEXT function that is used to convert the any value to text in a specific format.
Note that, TEXT function is nested inside CONCATENATE which converted date serial number into date format and combine with other strings.
And if you want to display the current time, you can use NOW function along with CONCATENATE and can format time with the help of TEXT function.
Note that, you can also format the text using function according to your requirement or the way you want the final output to be.