This function converts numerical values into text, in a specified format and adds corresponding symbols (e.g. Function ConcatDelim(ConcatRange As Variant, Delimiter As Variant) As StringĬoncatDelim = ConcatDelim & Delimiter & i In Excel, CONCATENATE is combined frequently with the TEXT function. Using the & we could use the formula: =A2&","&A3&","&A4&","&A5&","&A6&","&A7&","&A8&","&A9&","&A10&","&A11īoth not very practical as the range gets bigger.Ī better solution is to create a user defined function in VBA. Concatenate with Line Breaks Using the CONCATENATE or CONCAT Function This method is the essentially the same as the & method, but instead we can use either the CONCATENATE or CONCAT functions. In this example let’s concatenate column 1 and have the list separated by commas. We can join up to 255 items that are up to 8192 characters.
Text2 (required argument) The additional text items that we wish to join. The item can be a text value, cell reference, or a number. This is a good example where creating a short user defined function in VBA could be a big help. The CONCATENATE function uses the following arguments: Text1 (required argument) This is the first item to join. But let’s say you wanted to concatenate a large number of cells into one value and have them separated by a delimiter character like a comma, this means referencing each cell individually using either CONCATENATE or & and adding “,” between each cell reference.
The item can be a text value, number, or cell reference.If you add a double quot. There are two ways of concatenating cells in Excel, either with the built-in CONCATENATE function or using &. Excel’s CONCATENATE formula joins two or more text strings into one string.