Using CONCAT and CONCATENATE in Microsoft Excel

While the CONCATENATE and CONCAT functions seem like they might be the same thing, there are small differences between the two functions. CONCAT is new for the 2016 version of Excel, so if you’re using 2013 or earlier this article may not apply.

CONCATENATE – This function combines two or more strings together to form one. Very useful when dealing with heavy blocks of text data.

Example:

The formula in this example combined the first name and the last name of each of the people in our list. Each unique piece of the concatenation was separated with a comma, and notice we had to insert an extra space character (” “) to provide the space in between ‘John’ and ‘Doe’.

CONCATENATE, option B:

Some people prefer to combine text strings using an Ampersand (“&”), which works the same way. Combine each of the cells you want to add together along with any additional punctuation or spacing:

concatenate

CONCAT – This function is new beginning in the 2016 version of Excel. It provides a very simple update to the same formula, and that is the ability to select an entire range of strings all at once in order to concatenate them.

Example: Combining Five Values together

Notice in this example I’m combining five letters together to form one large string. With CONCATENATE, I have to separate each string with a comma. With CONCAT, I can select the whole range at once to get the same result.

Depending on what type of data you’re combining together (or how much), this could mean a lot more work putting the formula together. To recreate the First Name / Last Name example, you’d have to add in spaces to the First or Last Name boxes, or create a separate cell that only had a space character (” “) so that the values were properly combined together.

CONCAT Formula

How to Compare Two Lists in Excel

When analyzing data you often have to compare two lists to see if

  • They both contain the same data
  • You’re missing data from one of the lists
  • There are any duplicates or misspellings
  • etc..

If you’re looking to compare two lists for equality, you can use simple comparison operators.

compare lists excel

By turning column C into a helper column, we can compare the lists in A and B by simply typing

  1. =A2=B2
    

and auto-filling that formula down the column. As you can see this works for text, numbers, and dates the same way.

If you need to check to see if certain values exist or not in the other list, I’d suggest using ISNUMBER and MATCH together to find your answers.

Let’s separate the two columns for a second and add some additional values to List #2:

compare lists excel_2

We’re going to do a check to see which values in List 2 are in List 1.

We’ll create a helper column in Column D and first use the MATCH function to return the ROW that a match exists on, if any.

compare lists excel_3

  1. =MATCH(C2,A:A,0)

Excel searches for each of the values in column C inside of column A. The numbers returned are the ROW in column A where a match was found, while #N/A simply means there was no match found.

By wrapping this function in the ISNUMBER function, we can simplify this to only return TRUE (if a row number is found) or FALSE (error, no match).

compare lists excel_4

  1. =ISNUMBER(MATCH(C2,A:A,0))

We can now see exactly which values exist in both List 1 and List 2.