如何用VBA或公式从Excel单元格提取毫升、升对应数值？

Dealing with messy, unstructured text in Excel to pull out numeric values tied to ml or Ltr units is tricky—regular formulas can't keep up with all the formatting variations. Below are two reliable, practical solutions to get the job done:

1. VBA Custom Function (Great for Quick Cell-by-Cell Use) #

A custom VBA function using regular expressions will handle almost any text format you throw at it. Here's how to set it up:

• Open your Excel file, press Alt + F11 to launch the VBA Editor.
• Right-click your workbook in the Project Explorer > Insert > Module.
• Paste this code into the module:

Function GetVolume(cellText As String) As String
    Dim regex As Object
    Dim matches As Object
    Dim result As String
    
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "(\d+\.?\d*)\s?(ml|Ltr)" ' Matches numbers (decimals included) followed by ml/Ltr
    regex.Global = True ' Find all matches in the text
    
    Set matches = regex.Execute(cellText)
    result = ""
    
    For Each match In matches
        If result = "" Then
            result = match.SubMatches(0)
        Else
            result = result & ", " & match.SubMatches(0)
        End If
    Next match
    
    GetVolume = result
End Function

• Go back to Excel, and in a blank cell, use =GetVolume(A1) (replace A1 with your target cell). It will return all matching numeric values separated by commas.

For your sample texts:

• BAGDOL/1 (U-POL DOLPHIN GLAZE Brushable Stopper Bag 440ml) → returns 440
• RLT/1KIT (U-POL RAPTOR TINTABLE 750ml & 250ml STANDARD HARD...) → returns 750, 250

2. Power Query (Perfect for Bulk Processing) #

If you have a large dataset and prefer not to use VBA, Power Query is your friend:

• Select your data range > Go to the Data tab > From Table/Range (make sure your data has headers).
• In the Power Query Editor, go to Add Column > Custom Column.
• Paste this formula into the custom column editor:

Text.Combine(List.Transform(Text.Split([YourColumnName], " "), each if Text.Contains(_, "ml") or Text.Contains(_, "Ltr") then Text.Select(_, {"0".."9", "."}) else null), ", ")

(Replace YourColumnName with the actual name of your text column.)

• This splits the text by spaces, checks each segment for ml/Ltr, extracts the numeric part, then combines all matches into a single string.
• Click Close & Load to bring the cleaned data back to Excel.

Both methods handle the unstructured text formats you've shown—no more manual extraction!

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