How to Combine Text From Two Or More Cells into One Cell?
We get the data in the cells of the worksheet in excel and that is how the nature of the excel worksheet. We can combine multiple cell data into one and we can split the single-cell data into multiple cells. This is what makes the excel so flexible to use. Combining data of two or more cells into one cell is not the hardest but definitely not the easiest job, it requires very good knowledge of excel and systematic of excel. In this article, we will show you how to combine text from two or more cells into one cell.
Below are the examples to Combine text from two or more cells into one cell.
Example #1 – Using the ampersand (&) Symbol
Combine Data to Create Full Postal Address
Wherever you go while collecting the data from employees, students, or from some other everybody stores the data of full name, last name, address and other useful information in parallel columns. Below is the sample of one of those data.
This is fine at the time of collecting the data but basic & intermediate level excel users find it difficult when they want to send some kind of post to the respective employee or student because data is scattered into multiple cells.
Usually, when they send the post to the address they have required to frame the address like the below.
First Name & Last Name at the top, then they need to insert line breaker, then again they need to combine other address information like City, Country, State, and Zipcode. Here is where you need the skill to combine text from two or more cells into one cell.
We can combine cells by using excel built-in function “CONCATENATE” and also using the ampersand (&) symbol. In this example, I will use only the ampersand symbol.
Copy the above data into the worksheet.
Open equal sign in H2 cell and select the first name cell i.e. A2 cell.
Put the ampersand sign.
After one value is selected we need space character to separate one value from another. So insert space character in double-quotes.
Now select the second value to be combined i.e. last name cell i.e. B2 cell.
Once the First Name & Last Name is combined we need the address in the next line, so in the same cell, we need to insert a line breaker.
How do we insert the line breaker is the question now?
We need to make use of CHAR function in excel. Using number 10 in the CHAR function will insert Line Breaker. So use CHAR(10).
Now select Address and give the space character.
Similarly, select other cells and give each cell a one space character.
Now you can see the full address in one cell.
Copy and paste the formula to below cells as well.
But we cannot see any line breaker here, is it?
Once the formula is applied we need to apply Wrap Text format to the formula cell.
This will make the proper address format.
Example #2- Combine Cell Reference Values and Manual Values
Not only cell reference we can also include our own values with cell references. For example look at the below data.
We need to combine the above two columns data into one with the manual word “came to office at” and the full sentence should read like the below.
Example: “James came to the office at 11:53:10 AM”.
Let’s copy the above data to excel and open an equal sign in cell C2. The first value to be combined is cell A2.
Next, we need our manual value so insert the manual value in double-quotes.
Then select the final value as time cell.
Here, you can see the full sentences
Copy and paste the formula to other cells.
We have got one problem here i.e. the time portion is not appearing properly. The reason why we cannot see proper time because of excel stores time in decimal serial numbers. Whenever we combine time with other cells we need to combine with proper formatting.
To apply time format we need to use TEXT function with the format as “hh:mm:ss AM/PM”.
Like this, by using different techniques we can combine text from two or more cells into one cell.
This has been a guide to Combine Text From Two or More Cells into One Cell. Here we discuss how to combine text from two or more cells into one cell in excel with examples and downloadable excel template. You may learn more about excel from the following articles –