如何创建Excel自定义函数concall实现两列内容逐行拼接换行？

Let’s build exactly the function you need to automate that tedious manual concatenation! This uses VBA, the go-to tool for creating custom functions in Excel for tasks like this.

Step 1: Open the VBA Editor #

Press Alt + F11 to launch the VBA Editor. Then right-click your workbook in the Project Explorer (left pane) → select Insert → Module to create a new code module.

Step 2: Paste the UDF Code #

Copy and paste this code into the new module:

Function concall(Range1 As Range, Range2 As Range) As String
    Dim resultText As String
    Dim maxRows As Integer
    Dim i As Integer
    
    ' Handle cases where ranges have different row counts by using the shorter length
    maxRows = Application.WorksheetFunction.Min(Range1.Rows.Count, Range2.Rows.Count)
    
    ' Loop through each row to build the concatenated output
    For i = 1 To maxRows
        resultText = resultText & Range1.Cells(i, 1).Value & " – " & Range2.Cells(i, 1).Value & vbNewLine
    Next i
    
    ' Remove the extra trailing newline character
    concall = Left(resultText, Len(resultText) - Len(vbNewLine))
End Function

Step 3: Use the Function in Your Worksheet #

Head back to Excel, and in any empty cell, enter:

=concall(A1:A4, C1:C4)

Swap A1:A4 and C1:C4 with your actual target ranges. The result will display exactly as you want, with each concatenated pair on a new line:

A – Alpha
B – Beta
C – Gamma
D – Delta

Quick Notes: #

• Save your workbook as an Excel Macro-Enabled Workbook (.xlsm) to ensure the function stays available when you reopen the file.
• If your two ranges have different row counts, the function will only process up to the shorter range’s length to avoid errors.

内容的提问来源于stack exchange，提问作者Melko