如何在Excel中按设定规则将单元格内容拆分至指定区域？

Hey there! Let's walk through how to split content from a single Excel cell into a specified range based on your custom rules, using your two examples as clear references.

This is the most straightforward approach, especially if you're on Excel 365/2021 or newer (which supports dynamic array functions).

示例1：将A1的逗号分隔序列拆至A1:A9（竖排） #

If your Excel supports the TEXTSPLIT function, just enter this formula directly in cell A1:

=TEXTSPLIT(A1, , ",", , TRUE)

• Breakdown: The empty second parameter means we won't split into columns; the third parameter "," sets commas as the row delimiter; the final TRUE ignores empty values to avoid extra blank rows. The formula will automatically spill down to fill A1:A9.

For older Excel versions (no TEXTSPLIT), use the INDEX+FILTERXML combo:
Enter this in A1, then drag down to A9:

=INDEX(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s"),ROW(A1))

示例2：按多种标点拆分至B1:F1（横排） #

Your example includes commas, semicolons, and quotes—we'll first clean up the quotes, then split using multiple delimiters:
With TEXTSPLIT, enter this in B1:

=TEXTSPLIT(SUBSTITUTE(A1, """", ""), {", ", "; "}, , , TRUE)

• SUBSTITUTE(A1, """", "") removes the double quotes from the content;
• {", ", "; "} specifies multiple delimiters (comma+space, semicolon+space);
• The formula will automatically spill right to fill B1:F1, matching the 5 split results you need.

For older Excel versions, use INDEX+FILTERXML:
Enter this in B1, then drag right to F1:

=INDEX(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"""",""),"; ","</s><s>"),", ","</s><s>")&"</s></t>","//s"),COLUMN(A1))

If you're dealing with large datasets or more intricate delimiter rules, Power Query offers more flexibility:

针对示例1（竖排拆分） #

1. Select cell A1, go to the Data tab → From Table/Range (uncheck "My table has headers" if prompted);
2. In the Power Query Editor, go to the Transform tab → Split Column → By Delimiter;
3. Choose comma as the delimiter, then select "Split into Rows" in the advanced options;
4. Click Close & Load—the results will be imported to a new column (you can move it to the A1:A9 range if needed).

针对示例2（横排多分隔符拆分） #

1. Enter the Power Query Editor the same way, select the target column;
2. Go to Split Column → By Delimiter, choose "Custom", enter , , then add ; as a second delimiter in the advanced options (select "Any of the delimiters");
3. Click Replace Values to remove double quotes;
4. Close & Load the results to the range starting at B1.

If you need to run this split regularly, a simple VBA macro can do it with one click:

示例1的宏（竖排拆分） #

Sub SplitToVerticalRange()
    Dim sourceContent As String
    Dim splitResult As Variant
    
    ' Get content from A1
    sourceContent = Range("A1").Value
    ' Split by commas
    splitResult = Split(sourceContent, ",")
    ' Write transposed results to A1:A9
    Range("A1:A" & UBound(splitResult) + 1).Value = Application.Transpose(splitResult)
End Sub

示例2的宏（横排多分隔符拆分） #

Sub SplitToHorizontalRange()
    Dim sourceContent As String
    Dim cleanedContent As String
    Dim splitResult As Variant
    
    sourceContent = Range("A1").Value
    ' Unify delimiters to commas and remove quotes
    cleanedContent = Replace(sourceContent, "; ", ",")
    cleanedContent = Replace(cleanedContent, ", ", ",")
    cleanedContent = Replace(cleanedContent, """", "")
    ' Split and write results starting at B1
    splitResult = Split(cleanedContent, ",")
    Range("B1").Resize(1, UBound(splitResult) + 1).Value = splitResult
End Sub

How to use: #

1. Press Alt+F11 to open the VBA Editor;
2. Right-click your workbook → Insert → Module;
3. Paste the relevant macro code, then press F5 to run it.

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