Excel批量提取单元格物料成分至相邻列的技术问询

Hey Ben, let's tackle this problem efficiently—since you've got 5000 rows, we need solutions that work at scale. Here are two solid approaches depending on your Excel version:

This formula uses dynamic arrays and FILTERXML to handle all your varied input formats, converting them to the standard "X% MaterialName" format in one go.

Paste this formula into cell B1, then drag it down to cover all your rows:

=TEXTJOIN(" ", TRUE, IFERROR(
    LEFT(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"/"," "),"+"," "),"P"," P"),"C"," C"),"N"," N","W"," W")&"</s></t>","//s"), 
    LEN(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"/"," "),"+"," "),"P"," P"),"C"," C"),"N"," N","W"," W")&"</s></t>","//s"))-1
) &"% "& LOOKUP(RIGHT(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"/"," "),"+"," "),"P"," P"),"C"," C"),"N"," N","W"," W")&"</s></t>","//s")), 
    {"C","N","P","W"}, {"Cotton","Nylon","Polyester","Wool"}
), ""))

How it works: #

1. Unify separators: Uses nested SUBSTITUTE to replace /, +, and add spaces before material abbreviations (P/C/W/N), turning all input formats into space-separated items.
2. Split the string: FILTERXML breaks the processed string into individual material components (e.g., "50 P", "40 C").
3. Format each component: Extracts the numeric value, adds a %, then uses LOOKUP to map abbreviations to full material names.
4. Combine results: TEXTJOIN puts all formatted components together into a single string.

For 5000 rows, a VBA macro will run faster than dragging formulas, and it's easier to extend if you add more material abbreviations later (like the "E" in your example, which we'll map to Elastane).

Step-by-step: #

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 ConvertMaterialComposition()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim originalText As String
    Dim processedText As String
    Dim regex As Object
    Dim matches As Object
    Dim match As Object
    Dim materialMap As Object
    
    ' Set your worksheet (change "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Create a map of abbreviations to full material names
    Set materialMap = CreateObject("Scripting.Dictionary")
    materialMap("P") = "Polyester"
    materialMap("C") = "Cotton"
    materialMap("N") = "Nylon"
    materialMap("W") = "Wool"
    materialMap("E") = "Elastane" ' Add more entries here if needed
    
    ' Set up regex to find all number-abbreviation pairs
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "(\d+)([PCNWE])" ' Add more abbreviations inside the brackets if needed
    regex.Global = True
    
    ' Loop through each row
    For i = 1 To lastRow
        originalText = ws.Cells(i, "A").Value
        processedText = ""
        
        If originalText <> "" Then
            Set matches = regex.Execute(originalText)
            For Each match In matches
                ' Build the standard format string
                processedText = processedText & match.SubMatches(0) & "% " & materialMap(match.SubMatches(1)) & " "
            Next match
            ' Trim extra space at the end
            processedText = Trim(processedText)
        End If
        
        ' Write the result to column B
        ws.Cells(i, "B").Value = processedText
    Next i
    
    MsgBox "Material composition conversion complete!"
End Sub

4. Press F5 to run the macro, or assign it to a button for easy access.

Key benefits: #

• Faster for large datasets: Processes 5000 rows in seconds.
• Easy to extend: Just add new key-value pairs to the materialMap dictionary if you get more abbreviations.
• Works with all Excel versions: No need for 365's dynamic array features.

Important Notes: #

• Always back up your data before running macros or applying bulk formulas.
• For the VBA method, save your workbook as an .xlsm (Macro-Enabled Workbook) to keep the macro for future use.
• If you have other material abbreviations not listed, just add them to the formula's LOOKUP array or the VBA's materialMap dictionary.

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