To be able to manually create a User Defined Function (ie self-created user-defined function) to solve the problem below, you just need to create a VBA fragment, then add this paragraph to the Excel application, you can use the self-created function to join cells in a column in Excel.
Function join cells in a column, concatenate strings in a column in Excel
Concatenate cells in 1 column, concatenate strings in 1 Excel column
* Steps to prepare:
– Computers with commonly used versions of Excel today: Excel 2016, Excel 2013.
– There is already an Excel file with data to indicate the use of the string join function.
As the beginning says, if you want to join cells in a column together, for example cells C3 to C11 of column C are codes from TH001 to TH009 respectively and you need to join the cells together to: TH001#TH001#TH002#TH003#TH004#TH005#TH006#TH007#TH008 #TH009. Here you want to put an additional “#” sign to separate when joining cells together. So with a problem like this, how to create a self-created function?
Step 1: At the Excel -> window interface, press and hold Alt + F11 (in some laptops will be Alt + Fn + F11) to open Microsoft Visual Basic up:
Step 2: You right-click on VBAProject as shown below -> then select Insert -> and select Module:
You Copy the code below -> then Paste (Paste) into the window that just appeared:
Function (As Range area, string between As String)
Dim i, kq
For i = 1 To the area. Rows.Count
If vung(i, 1) <> “” Then
kq = kq & middle string & region(i, 1)
End If
Next i
say = kq
End Function
Step 3: Then you save (Ctrl+S) and close this view window. Next, you go back to the Excel application to use the noi formula to concatenate cells in a column, concatenating strings in 1 column.
In the resulting box, enter the formula = noi(C3:C11 ,”#”) -> then press Enter (you enter the # sign to separate with a # or if you want to separate with a space, the # sign you replace with a “space” is fine).
Excel processes and returns the result of joining cells in 1 column successfully as shown below:
So we have just shown you a good trick to join cells in 1 column, join strings in 1 column Excel is very useful but not too complicated for you to do. This join is simple, you just need to copy the VBA that we have already created, then add it to Microsoft Visual Basic of Excel and you can use the function of joining cells in 1 column in Excel.
In addition, for you to understand more about the string join function in Excel, Software invites you to refer to the article
https://thuthuat.taimienphi.vn/noi-cac-o-trong-1-cot-noi-chuoi-trong-1-cot-excel-23820n.aspx
Besides, to join strings between columns together, you can use the ConcateNate function to do it, refer to the syntax and how to do it in detail