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
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.
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
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)
Related
Lastly, here are some additional areas you may want to check out.
