Combine text from different cells

by on September 11, 2017

Combine text from different cells

Have you ever thought about or find yourself in a situation to combine text from different cells. In MS Excel, Text in different cells can be combined with the help of more than one function. We will lay down a step by step procedure to combine text from different cells. For example, a name list with the first name in one cell and the second name in an another can be combined. The Names list is provided below.

Combine text from different cells

Ampersand Method

Ampersand Method of joining or combining method is extremely easy. The structure to combine the text in this method follow this process=FirstName&” “&Second Name. So the formula, in this case, will be =A3&” “&B3, Hit Enter button and you are done. Copy and apply the formula to other names as well.

Combine text from different cells

Concatenate Method

Concatenate method like the first method joins/combine several text strings into one text string. In our case, the formula will look like =Concatenate(A3,” “,B3). Copy and apply the formula down the rows.

Flash Fill Method

This method is different than the above two. It’s a two step function. The first step is to write the full name by hand (Mike Tyson in our example). The second step is to apply the flash fill function from the data tab.

 Combine text from different cells

Combine Text with Numbers

The next question that can arise is how about joining Text with Numbers? In the below screen, our objective is to have a combined statement like “Bill Payable in: 45 Days”.

You can join Text and numbers in many ways in excel. In the below examples, we will evaluate different methods to get the job done and then it will be up to you to choose the one that is easy for you. we used & operation factor or ampersand. Be careful of where to use the ampersand sign and where to use double quote and space. Practice as much as you can and nothing is impossible to learn or mastering it.
Combine text from different cells

Instead of using the ampersand, we replaced some part of the formula with the text function. Text function converts a value to text in a specific number format. As we will use text function in methods below, you will understand how the text function works.

Combine text from different cells

Concatenate Method of Combining Text and Numbers

Click on the fx to open the functions list. Double click on the concatenate function to open the Function Argument dialog box. Each text will be separated by two double quotes separated by space.The advantage of this method over other is that you can see the output before even hitting the ok button.

Combine text from different cells

Text Combine with Percentage

Once more the basic formula is the same as was the case in Joining Text with numbers. The minor change is here we are using the Percentage format in Text function. Hit Enter and results will be updated.

Combine text from different cells

Concatenate Method to Combine text with Percentage

Similar to concatenate method used to combine text with numbers, but instead of using second text directly, we use Text(C11,”0.00%). So the whole formula will be:

=CONCATENATE(B11,” “,TEXT(C11,”0.00%”))

Combine text from different cells

Text with Date

The only change here is Date format is used in Text function. Use the Text(C17,”ddd mmm dd yyyy”) in the text3. The complete excel formula in case of Concatenate method for joining text with Date is =CONCATENATE(B17,” “,TEXT(C17,”ddd mmm dd yyyy”))

Combine text from different cells

Text with Currency

In order to join Text with currency, we used dollar function for currency. If you want to use the concatenate method then the whole formula is =CONCATENATE(B24,” “,DOLLAR(C24,0)).

Combine text from different cells

Download the excel file here

Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *