CONCATENATE
CONCATENATE is a Function that people are often intimidated by, I think because it sounds sort of complicated. It's actually very easy to use and it can be really useful, so don't be put off!
We’ve covered formatting and how to cut out unnecessary characters, but what if you need to add something to your data?
Imagine that once again you have a list of names, but this time the forenames and surnames are in separate columns. You want the names in the surname, forename format as before, so you’ll need to combine the columns.
Once again the basic formula is:
=CONCATENATE(ARGUMENTS)
In the arguments you’re going to enter the reference for each cell you want to include – separate each cell reference with a comma. For example:
(A1,B1)
If we stick with this, we’re going to get exactly what we asked for – A combination of the 2 cells exactly as they are.
Clearly that’s not what we want, but fortunately Excel allows us to add in extra arguments that are not cell references. In this case we want to include a comma and a space between the names. Any arguments you want to include that are not cell references must be in inverted commas, so for our purposes the arguments section of the formula would look like this:
(A1, “, ”,B1)
We’re literally asking Excel to include “, ” between the names. The result will be this:
There’s no limit to the number of arguments you can include, so knock yourself out. On occasion I've found it useful to combine the contents of five or six cells – Just do whatever works for you!
You'll probably find that you'll use this function in conjunction with others. TRIM, LEFT and RIGHT & UPPER, LOWER and PROPER are functions that I often use together to clean up data. If you have data that's poorly laid out, consider how you can use these functions to get it looking the way you want it.
I hope you've found this page useful; I’d appreciate it if you’d take the time to let me know how you rate it.
If you’re still struggling with the CONCATENATE Function you might find it helpful to have another look at the Getting started with Excel formulas page.
Looking for more? Return to Excel Formulas or the Office Software homepage
