How To Get The Best Out of The Concatenate Function

The following is a guest post from Puneet at Excel Champs:

Text functions averagely cover  10%-15% of our Excel usage on a day to day basis. Text functions help us to present a numeric value in a simple way. If you check the below example, a single sentence is describing the performance for the September month. But we can't say there is something wrong in presenting results in tabular form. It is just a matter of requirement. Excel has lots of useful functions in this Text Function family (Upper Function, Lower Function, Proper Function etc....).

CONCATENATE Function

A function to combine text, a function to put the text together. Yes, this is the CONCATENATE Function. A Function which exactly do the same for text as SUM Function does with numbers (Giving results in a single number).

How Does It Work?

It takes the text one by one in arguments & convert them into a single string.

Syntax: CONCATENATE(text1, text2, ...text255) 

text1 is the first item to join in concatenation which is also a required argument. 

text2 ... is the additional text which you can add. You can add up to 255 items & up to 8192 characters in a single function(for Excel 2007 & Later). Each entry must be separated by a comma.

If we concatenate two strings like "Hello" & "World", then it would be like =CONCATENATE("Hello ","World"). Now if you notice we had a space between the two strings, which is quite useful while converting them into a single string. The above example is drawn by using absolute input for arguments, you can also use cell reference to add arguments.  =CONCATENATE(A1," ",B1).

How Can It Be Useful?

It can help you at a number of times.

• Writing a conclusion for a tabular data, which will update with the changes in your data. So that, you don't have to write it again & again.
• Converting two or more strings into a single string. So that it can make a meaning to you.

Example

Here we have an example where we are capturing the population of the major cities in the world in a string and function will be.

HURDLES

Yes, we have. But you don't have to worry.

• Using commas to differentiate one string from another is confusing sometimes. If you skip putting a comma between the arguments, it can lead to a different result. =CONCATENATE("Excel""Champs") results in Excel"Champs. You can also put ampersand(&) instead of Comma(,).
• The space between string is also a point to take care. You can enter space within the string "Hello " or by inserting it separately "Hello"," ".
• If you use concatenation to join a numeric value, that value will always show in general format. That means if you have a number with 10 decimals, you will get it in the same decimal format in your string(Total growth in this month is 0.0523652). But this problem is not long lasting, you can kill it with TEXT Function & make your number just like you want.

What else to do?

Sometimes using a function like CONCATENATE is time-consuming, if you have to put it one by one different string to create a new one. You can also use a shorthand trick to get results like CONCATENATE, just ampersand(&) to join the text as do in CONCATENATE. It will give you a simple & a fast hand to join text.

Download a sample file here to see examples:

Sample File

Thanks to Puneet for providing us with this explanation of concatenate.