Enter a cell reference without quotes, or an actual text string within double quotes. Method 2: To insert single quotes around the cell values, use this formula: ''' & A1 & '''. Method 1: Use below formula into the blank cell: CHAR (34) & A1 & CHAR (34) Considering the value that you want to enclose in quotes is present in A1 cell. These two were the methods we found to be the simplest and most applicable to all. Enter the first text string that you want to include in the concatenation. To enclose all specified cells in quotes, the following simple formulas may help you. Remark If you want to directly use texts instead of cell references in the formula, please enclose the text strings with double quotation marks. Explanation CONCATENATE function: CONCATENATE function is used to join text together. There are plenty of other ways in Excel, like using VB script, or the TEXTJOIN function (available in newer versions and Office 365). CONCATENATE (B3,',',C3,',',D3,',',E3) Press Enter key, then the cells are combined with comma separated. It’s important to note though, that these are not the only two ways to do this. In this tutorial, we showed you two ways in which you can combine first and last names into a single column in Excel. So, even if you delete the original two columns containing first and last names respectively, the combined column remains unaffected. Since this method doesn’t involve a formula, it doesn’t depend on the original columns. In the same way, you can also set the pattern to have the last name followed by a comma, followed by the first name. Finally, we concatenated the value in C2 (the last name). We followed this by a dot operator and space. Here, we specified that if cell B2 is not blank (that means if a middle name exists), we want just the first character from the cell B2. The LEFT() function takes the specified number of characters starting from the left side of the text in a cell. If you want to have the first name, followed by the middle name’s initial, followed by the last name, then your formula in D2 can be =CONCATENATE(A2,IF(B2=””,” “,” “&LEFT(B2,1)&”.”),C2).
![concatenate rows in excel with comma concatenate rows in excel with comma](https://www.spreadsheetsmadeeasy.com/static/fee80eb1f530b6400a291cbe5f1dbd40/40ffe/join-cells-in-excel-feature-image.png)
If you have a first name, last name, and a middle name in cells A2, B2, and C2 respectively, your formula in D2 can be =CONCATENATE(A2,” “,B2,” “,C2).You can replace the separating character with any character you need.So for this example, C3 would have the formula: =CONCATENATE(B2,”, “,A2) Also, replace the first cell reference with the second cell reference and vice-versa. For example, to combine strings from cells A2, B2 and C2 separating the values with a comma and a space, here's the formula to. To concatenate strings with comma, put one of the following separators between the text arguments: Comma without space: ',' Comma and space: ', '. Finally, the SUBSTITUTE function is used to replace each space ( ). However, that does not mean you cannot include it directly in a formula.
![concatenate rows in excel with comma concatenate rows in excel with comma](https://i.stack.imgur.com/n61bW.png)