I think that this works...
It removes any leading, trailing, double embedded spaces. Makes the string all
lower case. Removes all the double quotes. And it looks for a single " x " in
the string.
Then it splits the resulting string into pieces based on the single space
character between each piece.
Then it starts looking at each piece.
If it's close to the X (say the X is the 17th position in the string), then only
look at the stuff in the 15&16 and 18&19 positions (it has to be within 2
positions to be considered).
Then it looks at those and builds a string -- if those things look like digits
or a /. I start each string with a 0 (just to make things simpler later).
So I'd build two strings (height and width):
0 3
or
0 6 7/8
Then replace each space with plus
0+3
or
0+6+7/8
and see if they can be evaluated by excel.
Then dump them back to the worksheet.
If you want to try...
Option Explicit
Function GetDimensions(myStr As String) As Variant
Dim mySplit As Variant
Dim iCtr As Long
Dim lCtr As Long
Dim JustXPos As Variant
'Dim HowManyPieces As Long
Dim OutStr As String
Dim HowManyXs As Long
Dim FoundANonNumber As Boolean
Dim myExp As String
Dim HeightExpression As String
Dim WidthExpression As String
Dim HeightVal As Double
Dim WidthVal As Double
Dim myResults As Variant
'remove any leading/trailing/doubled up spaces.
'and make it lower case
myStr = LCase(Application.Trim(myStr))
'remove the " marks
myStr = Replace(myStr, Chr(34), "")
HowManyXs = (Len(" " & myStr & " ") _
- Len(Replace(" " & myStr & " ", "x ", ""))) / Len(" x ")
If HowManyXs <> 1 Then
myResults = Array("Wrong number of X's", "Error")
Else
'do the work!
'create an array based on the string
mySplit = Split(myStr, " ")
'mysplit's first element is item 0
'but =match()'s first element is item 1
'so I subtracted to be on the same wavelength
JustXPos = Application.Match("x", mySplit, 0) - 1
myExp = "0"
For iCtr = LBound(mySplit) To UBound(mySplit)
'if we're at the X, then we're done with the
'height and want to start building the width
If iCtr = JustXPos Then
'Done with before the X.
HeightExpression = myExp
'get ready for after the X
myExp = "0"
End If
If Abs(iCtr - JustXPos) > 2 Then
'not close enough to the X
'skip it
Else
FoundANonNumber = False
For lCtr = 1 To Len(mySplit(iCtr))
If Mid(mySplit(iCtr), lCtr, 1) Like "[0123456789/]" Then
'keep it
Else
FoundANonNumber = True
Exit For
End If
Next lCtr
If FoundANonNumber Then
myExp = myExp & " 0"
Else
myExp = myExp & " " & mySplit(iCtr)
End If
End If
Next iCtr
'whew, finished with all the elements
'put the after X stuff in the Width
WidthExpression = myExp
'Change those expressions to something that looks
'like math
HeightExpression = Replace(HeightExpression, " ", "+")
WidthExpression = Replace(WidthExpression, " ", "+")
'just in case they aren't leagal math expressions
'start at 0
HeightVal = 0
WidthVal = 0
On Error Resume Next
HeightVal = Application.Evaluate(HeightExpression)
WidthVal = Application.Evaluate(WidthExpression)
On Error GoTo 0
myResults = Array(HeightVal, WidthVal)
End If
If Application.Caller.Rows.Count = 1 Then
'horizontal cells
GetDimensions = myResults
Else
'vertical cells
GetDimensions = Application.Transpose(myResults)
End If
End Function
========
If you're new to macros:
Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html
David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm
(General, Regular and Standard modules all describe the same thing.)
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.
Put your test string in A1
Select B1:C1 (exactly two cells!)
and type this:
=getdimensions(a1)
But hit ctrl-shift-enter instead of just enter.
Excel will put the formula in {}'s -- don't type them yourself.
It should look something like:
={getdimensions(A1)}
You can also use two vertical cells if you want (like A2:A3).
===========
It's not fool-proof. You can break it with something like:
654321-test 10/4 1/2" x 80 3/4" text
10/4 is 2.5
1/2 is .5
so you'll see 3 in the result.
Tony D wrote:
Thanks very much in advance for any solutions that can be offered.
I have the following in cell A1: 123456-text 3" x 6 7/8" etc etc
I have the following in cell A2: 654321-test 104 1/2" x 80 3/4" etc
etc etc blah blah blah
I would like to extract from A1 the value 3 and 6.875 into another
location (ie. 3 in B1 and 6.875 in C1)
I would like to extract from A2 the value 104.5 and 80.75 into another
location (ie. 104.5 in B2 and 80.75 in C2)
The length is always shown in inches (indicated as such by the single
quotation symbol). The length could be a whole number of fractionalas
illustrated above.
