The CONCAT function (short for "concatenate") in Excel joins separate text and cell values to return one string of text. CONCAT is useful to join your Excel data with words to create phrases and whole sentences. This can be helpful when you need to include data in human-readable sentences, or when you need to join multiple pieces of data in order to reformat it into new types of data.
The CONCAT function takes at least one argument (values separated by commas), but can take many more.
=CONCAT(argument_1, [argument_2], [argument_3]...)Each argument is treated as a text value, and they are combined into one text string. Only one argument is required, but the function can accept up to 253 arguments. It can accept strings of text (enclosed in quotes), single cell references, or a range of cells as arguments. Any numerical arguments will simply be converted to text values. The maximum length of text that the CONCAT function can return is 32,767 characters, which is the limit of characters that a cell can hold in Excel.
Say you have the following table displaying data about your pets, and you want to return a human-readable sentence about the data.
| A | B | |
|---|---|---|
| 1 | Pet | Quantity |
| 2 | cats | 3 |
| 3 | dogs | 2 |
| 4 |
=CONCAT("We have ", B2, " ", A2, " and ", B3, " ", A3)This function converts the numerical values to text and returns "We have 3 cats and 2 dogs".
Notice that we have to manually add spaces in our strings and between cell references. If we were to forget them and write =CONCAT("We have", B2, A2, "and", B3, A3) then we would get the following result: "We have3catsand2dogs".
To concatenate multiple values and automatically insert a common character between each argument (like a space), you can use Excel's TEXTJOIN function.
If you don't want to use the CONCAT function, you can manually concatenate values together with the "&" symbol.
Using the same pet table example above, you can write the following:
="We have " & B2 & " " & A2 & " and " & B3 & " " & A3This returns the same result: "We have 3 cats and 2 dogs".
In Excel you may be presented with all kinds of data in varying formats, so the CONCAT function can help you manipulate these values into the format you need. Say, for example, that you want a list of times in the hh:mm format, such as 10:30. The problem is that you're given two separate columns: one column with the hour and one column with the minute.
| A | B | |
|---|---|---|
| 1 | Hour | Minute |
| 2 | 10 | 22 |
| 3 | 11 | 25 |
| 4 | 12 | 51 |
By concatenating the hours, a colon, and the minutes, you can change the data to the desired format. You can write the following function:
=CONCAT(A2, ":", B2)If you write the function and copy it for each row, you'll get the values "10:22", "11:25", and "12:51". Note that these results will be in text format rather than in time format - everything CONCAT returns will be considered a string of text.
You already know the CONCAT function in Excel can take text strings and individual cell references as arguments, but it can also take whole ranges of cells. If the range contains any blank cells, CONCAT will simply ignore the blanks. Take the following example with official state birds:
=CONCAT(A1:A4)This CONCAT function returns the following string of text: "MinnesotaIowaSouth DakotaWisconsin".
| A | B | |
|---|---|---|
| 1 | Minnesota | Loon |
| 2 | Iowa | Goldfinch |
| 3 | South Dakota | Pheasant |
| 4 | Wisconsin | Robin |
If an argument has multiple rows and multiple columns, Excel will read from left to right, then from top to bottom.
=CONCAT(A1:B3)This CONCAT formula will return the following text: "MinnesotaLoonIowaGoldfinchSouth DakotaPheasant".
Again, notice that CONCAT does not include a space (besides the space already in South Dakota) or any kind of separator between the arguments as it joins them together. The TEXTJOIN function can do this automatically.