How to extract numbers for calculation when entering a string of text into a cell

Function calPP(inputStr As String) As Double
    Dim dimensions() As String
    dimensions = Split(inputStr, "*")
    If UBound(dimensions) < 2 Then
        MsgBox "Input format error, please make sure the input contains 3 numbers separated by '*'."
        B1 = 0
        Exit Function
    End If
    Dim od As Double, id As Double, ln As Double
    od = CDbl(Replace(dimensions(0), "mm", ""))
    id = CDbl(Replace(dimensions(1), "mm", ""))
    ln = CDbl(Replace(dimensions(2), "mm", ""))
    Dim volume As Double, mass As Double
    volume = (od - id) * ln * 8.96
    mass = volume / 1000
   calPP = mass
End Function

Why can't the code get results


Cell A1 Key in OD9.525*0.8mm*850mmL

formula like this ((9.525-0.8)*850*8.96)/1000

Cell B1 Get result is 66.4496

You need to remove "OD" from first dimension and "L" from units of last dimension.

Use REPLACE if these are generic values.

Temporary code:

Function calPP(inputStr As String) As Double
    Dim dimensions() As String
    dimensions = Split(inputStr, "*")
    If UBound(dimensions) < 2 Then
        MsgBox "Input format error, please make sure the input contains 3 numbers separated by '*'."
        B1 = 0
        Exit Function
    End If
    Dim od As Double, id As Double, ln As Double
    od = CDbl(Replace(Right(dimensions(0), Len(dimensions(0)) - 2), "mm", ""))
    id = CDbl(Replace(dimensions(1), "mm", ""))
    ln = CDbl(Replace(dimensions(2), "mmL", ""))
    Dim volume As Double, mass As Double
    volume = (od - id) * ln * 8.96
    mass = volume / 1000
   calPP = mass
End Function

Remove "OD" and the letter "L" from cell A1
or insert "L" into your formula for ln.

If you do that you get the correct result