If you want to directly use texts instead of cell references in the formula, please enclose text strings with double quotation marks. Here =SUBSTITUTE(TRIM(B3&" "&C3&" "&D3&" "&E3)," ",", ") will replace space with comma and space in the combined text string (the combination of B3, C3, D3 and E3). SUBSTITUTE function: the SUBSTITUTE function is used to replace text with the specific text. Here TRIM(B3&" "&C3&" "&D3&" "&E3) will combine cells with spaces and then clear the extra spaces, this formula will stop the trailing or leading comma. TRIM function: the TRIM function removes all extra spaces from the text string. Press Enter key, then drag the fill handle down to apply this formula to the cells you need to combine, then the cells are combined with comma separated. Generic formula: =SUBSTITUTE(TRIM(text_1&" "&text_2&" "&…&" "&text_n)," ",", ")įor instance, you want to combine the cell B3, C3, D3 and E3 into one cell with commas, please use below formula in a blank cell. Formula 3 Combine cells with comma by combining SUBSTITUTE and TRIM functions If there are blank cells which are used to be combined, there will be extra comma in the combined cell by using above 2 formulas (& and CONCATENATE) as below screenshot shown. If you want to directly use texts instead of cell references in the formula, please enclose the text strings with double quotation marks. =CONCATENATE(B3,",",C3,",",D3,",",E3)ĬONCATENATE function: CONCATENATE function is used to join text together.
Generic formula: CONCATENATE(text_1,",",text_2,",",…",",text_n)įor instance, you want to combine the cell B3, C3, D3 and E3 into one cell with commas, please use below formula in a blank cell. If you want to directly use texts instead of cell references in the formula, please embrace the text strings with double quotation marks.įormula 2 Combine cells with comma by using CONCATENATE function & : using this connector to combine cells and commas. Press Enter key, then the cells are combined with comma separated. Generic formula: text_1&","&text_2&","&…&"," &text_nĪrguments Text_1, text_2, text_n: the cells or text strings you want to combine with comma.įor instance, you want to combine the cells of each row across column B, column C, column D and column E into one cell with commas, please use below formula in a blank cell. Here this tutorial provides some formulas to join multiple cells into one cell with comma as separator as below screenshot shown, and also explains how the formulas work in Excel.įormula 1 Combine cells with comma by using &
0 kommentar(er)
