Change this line:

With your testdata in A1, select B1:C1 and type this:
to
With your testdata in A1, select B1:D1 and type this:

(you want to select 3 cells to get all 3 elements of the h*w*l.)

Dave Peterson wrote:
>
> I'm not sure how pulling 7"x40" would help find the volume of that hour glass
> vase, but you could use this UDF to get the product of the last expression in
> your string:
>
> Option Explicit
> Function myMultiply(myStr As String) As Variant
> Dim iCtr As Long
> Dim myExpression As String
> Dim myChar As String
> Dim TempVal As Variant
>
> myStr = Trim(myStr) 'get rid of leading/trailing spaces
>
> myExpression = ""
> For iCtr = Len(myStr) To 1 Step -1
> myChar = Mid(myStr, iCtr, 1)
> Select Case LCase(myChar)
> Case Is = " "
> Exit For
> Case Is = "x"
> myChar = "*"
> Case "0" To "9"
> 'ok
> Case Else
> myChar = ""
> End Select
> myExpression = myChar & myExpression
> Next iCtr
>
> TempVal = Application.Evaluate(myExpression)
>
> If IsError(TempVal) Then
> myMultiply = CVErr(xlErrNum)
> Else
> myMultiply = TempVal
> End If
>
> End Function
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Short course:
>
> Open your workbook.
> Hit alt-f11 to get to the VBE (where macros/UDF's live)
> hit ctrl-R to view the project explorer
> Find your workbook.
> should look like: VBAProject (yourfilename.xls)
>
> right click on the project name
> Insert, then Module
> You should see the code window pop up on the right hand side
>
> Paste the code in there.
>
> Now go back to excel.
> Into a test cell and type:
> =myMultiply(a1)
> Where A1 contains one of the strings.
>
> ============
> An alternative would be to extract the values into adjacent cells.
>
> Put this in a general module (just like the other UDF):
>
> Option Explicit
> Function myElements(myStr As String) As Variant
> Dim iCtr As Long
> Dim myExpression As String
> Dim myChar As String
> Dim mySplit As Variant
> Dim ElementCount As Long
> Dim LastElement As Long
>
> myStr = Trim(myStr) 'get rid of leading/trailing spaces
>
> myExpression = ""
> For iCtr = Len(myStr) To 1 Step -1
> myChar = Mid(myStr, iCtr, 1)
> Select Case LCase(myChar)
> Case Is = " "
> Exit For
> Case Is = "x"
> myChar = "*"
> Case "0" To "9"
> 'ok
> Case Else
> myChar = ""
> End Select
> myExpression = myChar & myExpression
> Next iCtr
>
> mySplit = Split97(myExpression, "*")
>
> ElementCount = UBound(mySplit) - LBound(mySplit) + 1
> LastElement = UBound(mySplit)
> ReDim Preserve mySplit(1 To Application.Caller.Cells.Count)
>
> If UBound(mySplit) < ElementCount Then
> mySplit = CVErr(xlErrRef)
> Else
> For iCtr = LastElement + 1 To UBound(mySplit)
> mySplit(iCtr) = ""
> Next iCtr
> End If
>
> myElements = mySplit
>
> End Function
>
> Function Split97(sStr As String, sdelim As String) As Variant
> 'from Tom Ogilvy
> Split97 = Evaluate("{""" & _
> Application.Substitute(sStr, sdelim, """,""") & """}")
> End Function
>
> With your testdata in A1, select B1:C1 and type this:
> =myElements(a1)
> but hit ctrl-shift-enter.
>
> This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> correctly, excel will wrap curly brackets {} around your formula. (don't type
> them yourself.)
>
> Then you can use those cells in any formula you want.
>
> tr2usa@yahoo.com wrote:
> >
> > Genie Bottle Black,Large,Ceramic dia38"x12"
> > Hour Glass Vase,Beige,Large dia7"x40"h
> > Sculpture,Small Ring,Black 5"x15"x22"h
> > Tall Vase, White,Large, 18"x18"x67"h
> > Abstract Vase,Small,Black 5"x10"x23"h
> > Abstract Vase,Medium,Black 5"x10"x33"h
> > Abstract Vase,Medium,Colors 5"x10"x33"h
> > Stone Figure 1 on Stand,Ceramic 12x12x29h
> > Stone Figure 2 on Stand,Ceramic 12x12x27h
> > Stone Figure 3 on Stand,Ceramic 12x12x36h
> > Display Cube White, Wood 20"x20"x21"h
>
> --
>
> Dave Peterson

--

Dave Peterson