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.
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.
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 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.
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.
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.
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.
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:
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”))
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)).
Download the excel file here