CONCAT worksheet function for Excel

CONCAT worksheet function for Excel

CONCAT worksheet function for Excel - Excel Effects

The CONCAT worksheet function for Excel allows you to combine multiple ranges of text strings. CONCAT is short for concatenate.

Overview of CONCAT worksheet function

So, the main key word in the purpose of CONCAT is ranges, which means, CONCAT works as an array function. In other words, you can check values one-by-one within a formula to determine if you want to concatenate them or not.

Syntax: CONCAT(text1,…text254)

Version: 2019 or higher

Example of using CONCAT

Example usage of CONCAT - Excel Effects

In contrast to the above example, you can just use =A3&” “&B3. Additionally, =CONCAT(A3,” “,B3) works.

Sample problem with CONCAT worksheet function

So, here is somewhat of a real-world problem to whet your appetite.

Concat function excel example - Excel Effects

In the above image, we want to find out which stores have a value for a particular item. If a store has a value, then we want to display the store number(s) in a separate cell.

For example, if store 1 has 10 chargers, and store 4 has 30 chargers, then the result we want to display is: 1,4. Remember, we are only displaying stores that containg a value.


Solution to CONCAT function example - Excel Effects

Above image represents our solution. Overall, the formula is fairly small and simple. So, we use CONCAT with MID and IF to solve the problem. Below is our formula. Feel free to copy it.

Formula: =MID(CONCAT(IF(B3:H3<>””,”,”&B$2:H$2,””)),2,20)


Lastly, here are some additional areas you may want to check out.

Education home Excel functions


Affiliate stuff – Move up and excel with Excel