Excel技术问询：如何合并多列单元格为单个单元格及单列多值合并为一个单元格

Hey Scott, let's break down how to merge multiple Excel cells (including your specific case of combining 7 rows from one column into a single cell) into one—here are three solid methods depending on your Excel version and workflow needs:

1. Excel Formula Approach (Quick & No Tools Required) #

This is perfect for one-off tasks, especially if you're using a modern Excel version:

• For Excel 365/2021: Use the TEXTJOIN function, which is built for exactly this scenario.
  Formula example (to merge cells A1 through A7):

  =TEXTJOIN(", ", TRUE, A1:A7)
  

  • Breakdown:
    • ", ": The separator between values (replace with CHAR(10) if you want line breaks, then enable Wrap Text on the target cell).
    • TRUE: Ignores empty cells in the range.
    • A1:A7: The range of cells you want to merge.
• For older Excel versions (2019 or earlier): Use concatenation with & (though it's less efficient for longer ranges):

  =A1&", "&A2&", "&A3&", "&A4&", "&A5&", "&A6&", "&A7
  

  Alternatively, the PHONETIC function works for text-only values, but it won't handle numbers correctly:

  =PHONETIC(A1:A7)
  

2. Power Query (Reusable & Automated Workflows) #

If you need to repeat this task or work with larger datasets, Power Query is the way to go—it’s non-destructive and easy to update:

• Step-by-step:
  1. Select the column you want to merge (e.g., column A). Go to the Data tab → click From Table/Range (check "My table has headers" if your column has a title).
  2. In the Power Query Editor, select your column, then go to the Transform tab → click Merge Columns.
  3. Choose your preferred separator (comma, line break, etc.), name the new merged column, and click OK.
  4. Go to Home → Close & Load to export the merged result to a new worksheet or existing location.

3. VBA Macro (Custom Bulk Operations) #

If you’re comfortable with a bit of code, a VBA macro can automate this for any range you specify:

• How to use:
  1. Press Alt + F11 to open the VBA Editor.
  2. Right-click your workbook in the Project Explorer → Insert → Module.
  3. Paste this code into the module:

  Sub MergeColumnToSingleCell()
      Dim mergeRange As Range
      Dim mergedResult As String
      Dim cell As Range
      
      ' Set the range you want to merge (update this to your target range)
      Set mergeRange = ThisWorkbook.Sheets("Sheet1").Range("A1:A7")
      
      mergedResult = ""
      ' Loop through each cell in the range
      For Each cell In mergeRange
          If cell.Value <> "" Then
              mergedResult = mergedResult & cell.Value & ", " ' Adjust separator here
          End If
      Next cell
      
      ' Remove the trailing separator
      If Len(mergedResult) > 0 Then
          mergedResult = Left(mergedResult, Len(mergedResult) - 2)
      End If
      
      ' Paste the result into your target cell (update to your desired location)
      ThisWorkbook.Sheets("Sheet1").Range("B1").Value = mergedResult
  End Sub
  

  4. Adjust the mergeRange and target cell references to match your workbook, then press F5 to run the macro.

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