The Concatenate Formula
Definition
This is one of the best formulas to use in excel. It’s so easy you won’t believe it. The concatenate formula combines two cells into one. If you have the word “one” is in cell A1 and the word “three” in cell A2, then the concatenate formula will make a new word called “onetwo”.
You can also use the "&" instead of typing the formula. For example = a1&a2, would give you a combination of the content in a1 and a2
Example
Cell A |
Cell B |
Formula |
Answer |
one |
two |
=CONCATENATE(A2,B2) |
onetwo |
1 |
2 |
=CONCATENATE(A3,B3) |
12 |
March |
Income statement |
=CONCATENATE(A4,B4) |
MarchIncome statement |
April |
|
=CONCATENATE(A5,B5) |
April |
telephone expenses |
Centre ABC |
=CONCATENATE(A6,B6) |
telephone expensesCentre ABC |
So cell A and cell B are combined
Common uses for concatenate
So where do we use this:
- When you have lots of departments with the same account codes, you can concatenate the code and the department name and then you have unique cells. This can now be used very effectively with the sumif formula to get reports per department,
- When you want to put a better description next to a line, for instance when you have telephone expenses for more than one person. You can do a concatenate to combine the name and the word “telephone expense”.=concatenate(“Telephone Expenses for “,a1). Note that after the word “telephone expense for”, you must put a space (“ “), otherwise the name will be right next to word without any spaces
The formula function bar
Things you can do with the concatenate formula
- Combine withthe sumif formula to get more categories when doing reporting...contact us for more info
- Get more unique cells by combining cells...contact us for more info
<-----Back to index
