Combine Text From Different Cells in Excel
If you are working in a job that requires data entry and analysis, you will often come across the need to combine text from different columns in to a single column in an Excel worksheet. For example, let us assume that you have a list of addresses with City, Street and Postal code laying in different columns. In order to prepare a mailing list, you will have to combine the City, Street and Postal Code entries into a single entry. Similarly, you may be required to generate a list of Full Names, data comprising of First Name, Middle Name and Last Names laying in different Excel Columns. Both the above tasks can be easily accomplished using the “&” Operator and also by using the Concatenate Function.
1. Combine Text From Different Cells in Excel Using “&” Operator
The simplest way to combine text from different Cells into a single Cell in Excel is to make use of the “&” Operator.
- First Select the Location where you want the combined Text to appear. In our case, we have selected Cell D3, as it lies immediately to the right of Columns containing the “First” and “Second” Names (See image below).
2. Now, to combine the Text from Cell B3 and Cell C3, type =B3&C3 in Cell D3 and hit the Enter Key on the keyboard of your computer. Once you hit the Enter Key, you will immediately see the combined data (B3+C3) in Cell D3. However, as you can see in the image below, there is no space between the First and Second name.
3. To Add Space between the First and the Second Names, type = B3&” “&C3 in Cell D3 and press the Enter key on the keyboard of your computer.
You will now see a space between the First and Second Names (See image below).
Note: Any character that you write within the quotes will be rendered in the final output. This means that you can easily add space, comma, or anything else to the final output. Also, there is no limit on the number of ampersands (&) that you can use in Microsoft Excel. You can use as many ampersands (&) as required to combine data from an unlimited number of Cells.
Combine Text From Different Cells in Excel Using Concatenate Function
Another popular Excel Function that can be used to combine text strings from different cells into a single cell is known as “Concatenate”. The “Concatenate” Function in Excel joins text strings from different Cells into a single Cell and the Syntax of the Concatenate function is as follows: CONCATENATE (text1, text2, … text_n) text1, text2, etc. can be text strings, numbers or single-cell references. To demonstrate the use of Concatenate Function, let us again take the simple case of Excel Worksheet having First and Second Names in different Columns. 1. First select the Location where you want the combined text data to appear. In our case, we have chosen Cell C4. 2. To combine the “First” and “Second” Names, type =Concatenate(A4,” “,B4) in Cell C4 and hit the Enter key on the keyboard of your computer.
Note: In the above formula we are inserting a space between the First and Second names by including ” “ in the above formula. 3. Once you combine the data in the first cell, you can quickly combine all the remaining cells by dragging the formula down for all the remaining Cells.
Note: The Concatenate Function can be used to combine up to 255 strings containing a maximum of 8,192 characters.
How to Transpose Columns to Rows in Excel Easy Way to Remove Duplicate Entries in Excel
