Pulling some data from cell?

T

tr2usa

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.
 
B

Bernard Liengme

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
 
T

tr2usa

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
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
D

Dave Peterson

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.
 
D

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.)
 
Top