EXCEL FUNCTION-CONCAT

Table of Contents

INTRODUCTION

CONCAT function is the latest successor of the older CONCATENATE FUNCTION almost performing the same task but in a more efficient manner.

CONCAT FUNCTION is present under the TEXT FUNCTION CATEGORY of the functions.

CONCAT FUNCTION combines the TEXTS, ARRAY OF TEXTS  or the multiple ranges in Excel.

CONCAT FUNCTION is not available for the older versions and is available for Excel 2019 onward and Microsoft 365 subscribers only as of now.

PURPOSE OF CONCAT FUNCTION IN EXCEL

CONCATENATE FUNCTION joins the separate text strings or multiple ranges containing the values into one combined text.

PREREQUISITES TO LEARN CONCAT FUNCTION

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

  •  Basic understanding of how to use a formula or function.
  •  Basic understanding of rows and columns in Excel.
  • Some information about the financial terms is an advantage for the use of such formulas.
  •  Of course, Excel software.

Helpful links for the prerequisites mentioned above

What Excel does? How to use formula in Excel?

SYNTAX: CONCAT FUNCTION

The Syntax for the function is

=CONCAT ( TEXT1 , TEXT 2, …) OR

=CONCAT(STARTING CELL : ENDING CELL) as a range.

TEXT1 ANY TEXT FRAGMENT

TEXT2 ANY TEXT FRAGMENT

… Upto 253 number of Text fragments can be there.

STARTING CELL: ENDING CELL A range of cells.

It’ll become more clear in the Example Section.

EXAMPLES: CONCAT FUNCTION IN EXCEL

EXAMPLE 1: COMBINING TEXTS

Let us try to understand the CONCAT FUNCTION by trying various examples.

EXAMPLE 1:COMBING TEXTS

We have texts in cell D5 E5 AND F5 and we need to combine them and put result in G5.

We can simply do that by following the steps

  • Select the cell G5 and put the formula as
  • =CONCAT(D5,E5,F5) and click ENTER.
  • The result will appear in the cell
  • The process is shown in the picture below.
USING CONCAT FUNCTION IN EXCEL

EXAMPLE 2: COMBINING TEXTS WITH SPACES, COMMAS

In the previous example, we saw that the function did its job and combined all the text but without any spaces and it looked odd. Let us revise the function used and make it proper by having spaces in between.

STEPS TO COMBINE TEXTS WITH SPACES, COMMAS IN EXCEL

We have texts in cell D5 E5 AND F5 and we need to combine them and put result in G5 and we want to have spaces in between the words which are being attached.

We can simply do that by following the steps

  • Select the cell G5 and put the formula as
  • =CONCAT(D5,” “,E5,” “,F5) and click ENTER.
  • The result will appear in the cell and this time there will be small spaces in between the words.
  • So we saw, that we can insert the snippets of any spaces, commas or any text in between the different values.
  • SIMILARLY IF WE WANT TO ADD COMMAS , THE FORMULA USED WILL BE AS FOLLOWS.
  • =CONCAT(D5,”,”,E5,”,”,F5) and click ENTER.
  • The process is shown in the picture below.
USING CONCAT FUNCTION TO COMBINE TEXTS INCLUDING SPACES BETWEEN WORDS

EXAMPLE 3: COMBINING TEXTS IN RANGES

This example will show the strength of the CONCAT FUNCTION, something which CONCATENATE FUNCTION can’t do.

Suppose, we have a complete range of cells with many text fragments put in the different cells and we need to combine them. Of course, we can try it with concatenate.

Would that be easy if number of fragments is like 50 or 100. It would be a tedious task to put so  many arguments.

But as we already learnt that CONCAT FUNCTION takes the input as a range too.

We just need to put the formula as

=CONCAT(starting cell of the range:ending cell of the range)

and we are done.

Let us take an example with 100 cells containing the number from 1 to 100. 

STEPS TO COMBINE THE TEXT IN RANGES OR ARRAYS

  • Select the cell, where we want the output
  • Enter the fomula =CONCAT( : )
  • Press Enter.
  • The result will appear in the cell.
  • The result is shown in the picture below.
USING RANGE IN CONCAT FUNCTION

EXAMPLE 4: COMBINING TEXT WITH RANGES AND SPECIAL CHARACTERS

Let us take an example to try the complex usage in which we would be using the range as well as text with special characters etc.

Suppose we have a form in which we accept the input from the use as First Name and Last Name.

In addition to this, we take the input for the address as House Number, Area, Country .

The output should be as Mr. First Name Last Name resides in House Number Area Country and his email id is FirstName@gyankosh.net  Where gyankosh.net is common and all employees has the same format of email ids.

STEPS TO COMBINE TEXT WITH RANGES AND SPECIAL CHARACTERS.

  • Get the input from the users in different cells and mark the column names for the ease.
  • Choose the output cell and put the following function.
  • =CONCAT(“Mr. “,E7,” “,F7,” resides in  “,G7:I7,” and his email id is “,E7,”@gyankosh.net”)

The function starts with the insertion of a text snippet in the “”. After that takes the cell E7 followed by a space again in the “”. 

After the sapce comes F7 again follwed by the text snipped RESIDED IN followed  by a range G7 to I7 [To show this F7 and I7 cells contain a space after the text which will create a gap between the words].

After the range is the text snippet again AND HIS EMAIL ID IS  followed by the first name again E7 followed by the text snippet @GYANKOSH.NET.  

Similarly we can create any statement using CONCAT FUNCTION.

EXAMPLE OF USING CONCAT FUNCTION FOR RANGE, SPECIAL CHARACTER AND TEXT SNIPPETS

In this article, we learnt the different ways to learn All new CONCAT FUNCTION in EXCEL.

FAQs

WHAT IS THE DIFFERENCE BETWEEN CONCAT AND CONCATENATE FUNCTIONS IN EXCEL

The major difference between the CONCAT FUNCTION and CONCATENATE FUNCTION is the ability to deal with the ranges or arrays.

Concat can take the input as a complete range say A1:A1000 . We can see that we could write the formula in just few characters.

But if we want to use the same in CONCATENATE, the first problem is we won’t be able to use as it is limited to around 253 inputs. Even if we are allowed somehow, it is tough to write 1000 entries.

Concat can also accept the various combinations of range, cell, value etc. whereas Concatenate lacks the same.



WHY IS CONCAT NOT WORKING IN EXCEL?

CONCAT FUNCTION is a new improved version of older CONCATENATE FUNCTION.

This function is available for Excel 2019 and later versions.

Check your version. If your version is older, perhaps it is not available for you.

Kindly update your copy or you need to buy a new version.



HOW TO CONCATENATE A SPACE BETWEEN TWO WORDS IN EXCEL?

Simply add a space term as ” “ in between the two terms where you want the space.

For example =CONCAT(“HE”,” “,LOVES”,” “,”ME”) or =CONCATENATE(“HE”,” “,LOVES”,” “,”ME”) or references [ cell addresses ] can also be used.

You can also include the space in the word itself.

For Example, =CONCAT(“He “,”Loves “,”Me”) or =CONCATENATE(“He “,”Loves “,”Me”).

The space is already taken with the word in this.

HOW TO CONCATENATE DOUBLE QUOTES IN EXCEL?

You can simply concatenate double quotes in Excel. Don’t get confused.

The point is that outer DOUBLE QUOTES are meant for the function and inner ones for concatenating them.

For Example =CONCAT(“”HE””,” IS “,” GOOD”).