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 Excel FunctionCONCATENATE Excel FunctionThe concatenate function in Excel is used to connect or concatenate two or more characters, strings, or numbers. The concatenate function is a better alternative to using the & operator to connect two variables.” 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 the H2 cell and select the first name cell, i.e., A2 cell.
Put the ampersand sign.
After one value is selected, we need space characters 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 the CHAR function in excelUse Of The CHAR Function In ExcelThe character function in Excel, also known as the char function, identifies the character based on the number or integer accepted by the computer language. For example, the number for character "A" is 65, so if we use =char(65), we get A.. 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 the 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, but we can also include our own values with cell referencesCell ReferencesCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.. 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 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.”
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 a downloadable excel template. You may learn more about excel from the following articles –