Combine Text From Two or More Cells into One Cell

We get the data in the cells of the worksheet in Excel, which is how the Excel worksheet works. We can combine multiple cell data, splitting the single-cell data into numerous cells. That is what makes Excel so flexible to use. Combining data from two or more cells into one cell is not the hardest but not the easiest job. It requires very good knowledge of Excel and systematic Excel. This article will show you how to combine text from two or more cells into one cell.

For example, we have a worksheet consisting of product names (column A) and codes (column B). The given product names are A, B, C, and D, and the codes are 2,4,6 and 8, respectively. We need to combine them into one cell (column C). We can easily combine cells in columns A and B by inserting the formula =A2&B2, =A3&B3, and so on to get a string A2, B4, C6, and D8 in column C.

Below are examples of combining text from two or more cells into one cell.

Example #1 - Using the ampersand (&) Symbol

Combine Data to Create a Full Postal Address

While collecting the data from employees, students, or some others, everybody stores the data of full name, last name, address, and additional useful information in parallel columns. Below is a sample of one of those data.

It is fine when collecting the data, but basic and intermediate level Excel users find it difficult when they want to send some post to the respective employee or student because data is scattered into multiple cells.

Usually, when they send the post to the address, they must frame the address like the one below.

"First Name" and "Last Name" at the top, they need to insert a line breaker, then again they need to combine other address information like "City," "Country," "State," and "Zip Code." Here, we need the skill to combine text from two or more cells into one cell.

We can combine cells using the built-in Excel function “CONCATENATE Excel function” and the ampersand (&) symbol. In this example, we will use only the ampersand symbol.

We must copy the above data into the worksheet.

Then, we must open an equal sign in the H2 cell and select the first name cell, i.e., the A2 cell.

Put the ampersand sign.

After selecting one value, we need space characters to separate one value from another. So, insert a space character in double quotes.

Now, we must select the second value to be combined, i.e., the last name cell, the B2 cell.

Once the "First Name" and "Last Name" are combined, we need the address in the next line, so we need to insert a line breaker in the same cell.

How do we insert the line breaker is the question now?

We need to make use of the CHAR function in Excel. Using the number 10 in the CHAR function, we will insert a line breaker. So, we will use CHAR(10).

Now, we must select "Address" and give the space character.

Similarly, we must select other cells and give each cell a one-space character.

Now, we can see the full address in one cell.

Next, we will copy and paste the formula to the below cells.

But we cannot see any line breaker here.

Once the formula is applied, we need to apply the Wrap Text format to the formula cell.

As a result, it will make the proper address format.

Example #2- Combine Cell Reference Values and Manual Values

Not only cell references, but we can also include our values with cell references. For example, look at the below data.

We need to combine the above two columns of data into one with the manual word "came to office at," and the full sentence should read like the one below.

Example: “James came to the office at 11:53:10 AM.”

Let us copy the above data to Excel and open an equal sign in cell C2. Then, the first value to be combined is cell A2.

Next, we need our manual value. So, we must insert the manual value in double quotes.

Then, we must select the final value, the "Time" cell.

As a result, we can see the full sentences.

Then, we must copy and paste the formula into other cells.

We have one problem here, i.e., the time portion is not properly appearing. The reason why we cannot see proper time is that Excel stores time in decimal serial numbers. Therefore, whenever we combine time with other cells, we need to connect them with proper formatting.

To apply the time format, we need to use TEXT  Formula in Excel with the format "hh:mm:ss AM/PM."

By using different techniques, we can combine text from two or more cells into one cell.