Google Sheet公式需求：拆分逗号分隔数值并逐行排列

Got it, let's break down how to get those comma-separated values from A1 into individual cells starting at A2. I'll cover a few methods depending on whether you need a one-time fix, dynamic updates, or automated processing:

Method 1: Text to Columns (Manual, One-Time Operation) #

This is the quickest option if you don't need the split cells to update when A1 changes:

• Select cell A1 with your comma-separated values.
• Head to the Data tab in the Excel ribbon.
• Click Text to Columns.
• Walk through the wizard:
  1. Pick Delimited and click Next.
  2. Check the Comma box (uncheck "Space" if your values have spaces after commas like your example). Click Next.
  3. Under "Destination", type $A$2 to avoid overwriting A1. Click Finish.
• Your values will drop perfectly into A2 through A6.

Method 2: Formula (Dynamic, Auto-Updates) #

If you want the split cells to refresh automatically when A1 changes, use these formulas based on your Excel version:

For Excel 365/2021 (Modern Versions) #

The TEXTSPLIT function makes this trivial—it even spills values automatically:

• In cell A2, enter:

  =TEXTSPLIT(A1, ", ")
  

• No need to drag anything! The formula will populate A2 to A6 on its own.

For Older Excel Versions (No TEXTSPLIT) #

Use this string function combo to extract each value individually:

• In cell A2:

  =TRIM(MID(SUBSTITUTE(A1, ", ", REPT(" ", LEN(A1))), (ROW()-2)*LEN(A1)+1, LEN(A1)))
  

• Drag the formula down from A2 to A6. The ROW()-2 adjusts for each row, and TRIM cleans up any extra spaces.

Method 3: VBA Macro (Automated/Batch Processing) #

If you need to do this for multiple cells or want to streamline the process, use a simple VBA macro:

1. Press Alt + F11 to open the VBA Editor.
2. Insert a new module (Right-click your workbook > Insert > Module).
3. Paste this code:

   Sub SplitValuesToCells()
       Dim valueArray As Variant
       ' Split A1's content using ", " as the delimiter
       valueArray = Split(Range("A1").Value, ", ")
       ' Paste split values starting at A2 (transpose to make it vertical)
       Range("A2").Resize(UBound(valueArray) + 1, 1).Value = Application.Transpose(valueArray)
   End Sub
   

4. Press F5 to run it, or assign it to a button for quick access.

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