Today I am going to show you a cool trick to use Concatenate to join text and values in Microsoft Excel. To start, I have this simple list of first name, last name, and age. The goal is to be able to paste this into an email, into a word document, or something similar. Instead of just showing the raw data, it would look more like “John Smith is 33 years old.” The excel function that would do this for you is called Concatenate.
In order to join text and numbers, you would use the =CONCATENATE() function. Inside the parenthesis, the referenced cells can contain formulas or other functions. You can add commas or quotation marks to add text or spaces.
For example, the Excel cells we are using are A1 containing the first name, B1 containing the last name, and C1 containing the age. If we input this into the Concatenate() function it would look like this: =CONCATENATE(A1, “ “, B1, “ is ”, C1, “ years old.”). Make sure to account for any spacing that would need to go in between text or values. As you can see between cells A1 and B1 I added a space using quotation marks, which would create a space between the first and last name.
Then you can paste this into the function bar in Excel, and you can see how Excel will select the three cells referenced. Once you hit return, Excel will automatically create the sentence for you. In our example, it will look like “John Smith is 33 years old.”
If you have a list, all you need to do is use auto fill and drag the function down. This will automatically fill out the rest of your list.
You can utilize this in all kinds of situations, whether you want to make something that’s easier to read, or maybe you want to join some values. For more information, you can check out my YouTube video that shows how to use Concatenate.
If this helped you out, please take a moment to browse my website and check out my YouTube channel @In30Minutes for other helpful tips and tricks. For more information on how to get the most out of excel, check out my book Excel Basics: in 30 Minutes, which covers other basic functions of Excel. As well as the Excel cheat sheets and intermediate level cheat sheet which specifically covers Concatenates.