Pulling some data from cell?

I have 7k items in my list and I would like to pull demensions from
descriptions to calculate cubic meter. Which formula helps me out?
example: (Three Noodle Chair,Rattan 38x48x31"h)
Thanks for the help.

Please give us more examples
Are ALL the numeric values 2 digits?
Is the letter 'x' used other than within the dimension part?
If you wish, please send me a file with 500 items - my private email not to
the group.
A mixture of FIND and MID should work here.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

<[email protected]> wrote in message
news:[email protected]...
>I have 7k items in my list and I would like to pull demensions from
> descriptions to calculate cubic meter. Which formula helps me out?
> example: (Three Noodle Chair,Rattan 38x48x31"h)
> Thanks for the help.
>

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

On 27 Dec 2005 09:19:03 -0800, [email protected] wrote:

>I have 7k items in my list and I would like to pull demensions from
>descriptions to calculate cubic meter. Which formula helps me out?
>example: (Three Noodle Chair,Rattan 38x48x31"h)
>Thanks for the help.

Not sure exactly what you want.

If you want to "pull" the last "phrase", e.g. 38x48x31"h, then the following
formula:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),255)

Since you want to do calculations, though, you may want to split things up and
either calculate the volume of a cube or a cylinder, depending on whether there
are two or three numbers.


--ron

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.





[email protected] 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

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.
>
> [email protected] 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

Thank you very much for fast responses. Your answers reduced my work a
lot.

Dear Dave,
I used your second module worked perfect. Thank you.