Combine Text From Two or More Cells into One Cell

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.

Combine-Text-From-Two-or-More-Cells-into-One-Cell

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Combine Text From Two or More Cells into One Cell (wallstreetmojo.com)

Examples

Below are the examples to Combine text from two or more cells into one cell.

You can download this Combine Text to One Cell Excel Template here – Combine Text to One Cell Excel Template

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.

Combine Text Example 1

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.

Combine Text Example 1-1

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 Excel Function” and also using the ampersand (&) symbol. In this example, I will use only the ampersand symbol.

Copy the above data into the worksheet.

Combine Text Example 1

Open equal sign in the H2 cell and select the first name cell, i.e., A2 cell.

Combine Text Example 1-2

Put the ampersand sign.

Combine Text Example 1-3

After one value is selected, we need space characters to separate one value from another. So insert space character in double-quotes.

Combine Text Example 1-4

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

Combine Text Example 1-5

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 the CHAR function in excel. Using number 10 in the CHAR function will insert Line Breaker. So use CHAR(10).

Combine Text Example 1-6

Now select Address and give the space character.

Combine Text Example 1-7

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

Combine Text Example 1-8

Now you can see the full address in one cell.

Combine Text Example 1-9

Copy and paste the formula to below cells as well.

Combine Text Example 1-10

But we cannot see any line breaker here, is it?

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

Combine Text Example 1-11

This will make the proper address format.

Combine Text Example 1-12

Example #2- Combine Cell Reference Values and Manual Values

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

Combine Text Example 2

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.

CT Example 2-1

Next, we need our manual value so insert the manual value in double-quotes.

CT Example 2-2

Then select the final value as time cell.

CT Example 2-3

Here, you can see the full sentences.

CT Example 2-4

Copy and paste the formula to other cells.

CT Example 2-5

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 them with proper formatting.

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

CT Example 2-6

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

Recommended Articles

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 a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>