Buzz
To create a User Defined Function (meaning a custom function defined by the user) for the problem at hand, you just need to create a VBA code. Then, add this code to the Excel application, and you can use the custom function to concatenate cells in a column in Excel.
Function for concatenating cells in a column, joining strings in an Excel column
Concatenate cells in a column, join strings in an Excel column
* Preparation steps:
- Ensure your computer has a common version of Excel: Excel 2016, Excel 2013.
- Have an Excel file with existing data for easy use of the concatenate function.
As mentioned in the introduction, if you need to merge cells in a column, for example, from cell C3 to C11 in column C with codes TH001 to TH009, and you want to concatenate these cells to get: TH001#TH001#TH002#TH003#TH004#TH005#TH006#TH007#TH008#TH009. Here, you want to add the '#' symbol to separate the cells when concatenating. So, how do you create a custom function for this task?
Step 1: On the Excel window -> press and hold Alt + F11 (on some laptops it might be Alt + Fn + F11) to open Microsoft Visual Basic:
Step 2: Right-click on VBAProject as shown below -> then select Insert -> and choose Module:
Copy the code below -> then Paste it into the newly appeared window:
Function concatenate(rng As Range, delimiter As String)
Dim i, result
For i = 1 To rng.Rows.Count
If rng(i, 1) <> '' Then
result = result & delimiter & rng(i, 1)
End If
Next i
concatenate = result
End Function
Step 3: Afterward, save (Ctrl + S) and close this interface window. Next, return to the Excel application to use the concatenate formula to merge cells in a column, concatenate strings in a column.
In the result cell, enter the formula = concatenate(C3:C11,'#') -> then press Enter (if you want to separate with spaces instead of #, replace # with 'space' in the formula).
Excel processes and returns the successfully concatenated result of cells in a column as shown below:
So, we have just guided you through a handy trick to Concatenate cells in a column, join strings in an Excel column that is useful yet not too complex for you to execute. This concatenation is simple; you just need to copy the pre-created VBA code we provided, then add it to the Microsoft Visual Basic of Excel, and you can use the concatenate function for cells in a column in Excel.
Furthermore, for a deeper understanding of string concatenation in Excel, Mytour invites you to refer to the article Concatenating Strings in Excel that we have previously introduced and shared. Wish you success!
Additionally, to concatenate strings between columns, you can use the ConcateNate function. Refer to the detailed syntax and steps to use the ConcateNate function to concatenate multiple data in multiple columns into one string.
