The CONCATENATE function is used to join the contents of cells together. Here’s a couple of examples of how to use it:
Example 1
In the example above, D2 contains the formula =CONCATENATE(A1,” “,B1,” lives in “,C1)
This joins the text in A1 to a space to the text in B1 to the words “lives in” (with a space before and after) to the text in C1
Example 2
In the example below, we have customer’s credit card numbers which, for some reason have been stored in 4 separate columns. The formula in column E displays the credit card number as 4 blocks of 4 digits with a space between each block (which is the way we expect to see credit card numbers displayed)
The formula in E1 is =CONCATENATE(A1, ” “,B1,” “,C1,” “,D1)
Mike, I prefer the & rather than spelling “Concatenate” (which I usually get wrong! so an alternative to the above would be…
=A1&” “&B1&” “&C1&” “&D1
Where I find teh function really useful is where a spreadsheet has some fixed narrative, but a different “result”, for example..
“Sales for the month were…” so you could have something like…
=”Sales for the month were £”&C24 (where C24 is the cell contanining the “real” Sales value)
I cover both CONCATENATE and & in my courses. Did you know that in 2007 and 2010, CONCATENATE can have up to 255 arguments whereas in Excel 2003 you can only have 30 arguments.
The fixed narrative example is good too. The formula below comes from a loan calculator example I built
=B33 & ” payments of ” & TEXT(B39,”£0.00″) & ” will be taken from your account starting on ” & TEXT(B35,”dd mmmm yyyy”)
gives, for example… 24 payments of £30 will be taken from your account starting on 20 March 2013
Yes.. and, of course, you’ve added the key additional function of TEXT to ensure that the numerical parts are formatted correctly!