how to concatenate rows in excel with comma

If you want the Excel CONCATENATE function to work, it needs a minimum of one “text” argument.You can have a maximum of 255 strings concatenated in one CONCATENATE formula that has a sum of 8,192 characters.The formula will return a #VALUE! Error whenever there is at least one invalid argument in the CONCATENATE function.Remember to follow these simple rules so you can make sure that your CONCATENATE formulas will always provide the right results: Things To Remember When Using Excel’s CONCATENATE In between the values combined is space (” “) added to make the result display “Project 1” instead of “Project1.” You can also naturally include a text string in the middle or at the start of your Concatenate formula: There is a space added before the term “completed,” as this works to have the concatenated text strings separated.

You can have it used to concatenate different text strings so that the result will become more meaningful.Īs an example, the formula =CONCATENATE(A1, ” “, B1, ” completed”) tells the user that a specific project is complete, like in the 2nd row of the screenshot below. You don’t have any reason to limit the Excel CONCATENATE function to only joining the values of cells. Concatenating a cell value and text string

You can have the concatenated values separated with other delimiters like a slash, space, or comma. If you want to have the values separated with space, have ” ” entered in the second argument as what you can see in the 3rd row of this screenshot: It should be like in the 2nd row of this screenshot. Take note that you need to knit the values together without using any delimiter. If you want to combine the values that cells A1 and B1 have, here is the most straightforward formula to CONCATENATE: Whenever you want to remove the data source without creating an effect on the resulting text strings, you can use the option “Paste special – values only” if you want to have the formulas converted to their values. =CONCATENATE(“Today is “,TEXT(TODAY(), “dd-mmm-yy”)) If you want to return the result with a formula that your users can understand, you can have it concatenated with a text string to explain the actual value.Īs an example, this is the formula that you can use to go back to the date today: Have a formula-calculated value and text string concatenated In Excel 2019 and Excel 2016, Excel Mobile and Excel Online CONCATENATE got replaced with the function CONCAT that has the same syntax.Įven if they kept the CONCATENATE function for backward compatibility, they recommend using CONCAT as Microsoft doesn’t promise that CONCATENATE will become available in Excel’s future versions. Whenever the text is a cell reference, formula-driven value, or text string, here are some examples of how you can use Excel’s CONCATENATE function. The CONCATENATE function in ExcelĮxcel has the CONCATENATE FUNCTION designed to join various text pieces together or have values of several cells combined into one cell. This tutorial will tackle the two crucial ways to concatenate Excel strings through the Excel & operator and the CONCATENATE function. Here is a screenshot that demonstrates how these methods are different: Whenever you work on merging cells, you are “physically” working on integrating more than one cell into a single cell.īecause of that, it will allow you to have a larger cell that gets displayed across more than one column or row in your worksheet. In the middle of some of the text, you may also insert a value that is calculated by formula. People usually use this method to connect some text pieces in various cells that are technically referred to as simply strings or text strings. In Excel, concatenation involves the process of having two or more values joined together. Whenever you use Excel to concatenate cells, you will only have those cells’ contents combine. There are essentially a couple of ways on how you can combine the information in Excel spreadsheets. Our tutorial will explore the different string concatenation techniques in Excel that will allow you to select the most suitable method for your worksheets. It has been driven to display the desired format of the times and dates, to mention a few. It allows the text to combine with a value that the formula. Some of the common examples would require concatenation in Excel to join address parts and names. You may also want to do the opposite, which involves combining data from more than two columns into just one column. Usually, you would want to have the content of a cell split into individual cells. In Excel, the information in the workbooks may not always have a structure that fits your needs. Our tutorial on the Concatenate function will let you learn about the different ways you can use the Concatenate function, as well as the “&” operator, to have rows, cells, columns, ranges, and text strings concatenated in Excel.

How to concatenate rows in excel with comma