Extracting Values from text embedded in cell

[Tony D]

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 fractional as
illustrated above.

 

[Gord Dibben]

etc etc etc blah blah blah means what?

Do you have multiples of terms like 3" x 6 7/8" in A1?

Provide a real sample of what is in A1 or A2 without the etc etc blah blah
part.

In the meantime for working with feet and inches see Chip Pearson's site.

http://www.cpearson.com/excel/FeetInches.htm

http://www.cpearson.com/excel/FractionalArithmetic.aspx


Gord Dibben MS Excel MVP

 

[Mr. T]

Text follows the measurement. In other words, need the value of the
measurement that is embedded in essentially a cell that is text.

Example:
A1 = 123456-text 3" x 6 7/8" text
A2= 654321-test 104 1/2" x 80 3/4" text

Need a single formula that will apply globally to extract out the
width and height. I have provided examples that capture whole numbers
(ie. 3) and fractional numbers that can be single digit, double digit
and triple digit.
Thank you.

 

[Tony D]

Text follows the measurement. In other words, need the value of the
measurement that is embedded in essentially a cell that is text.

Example:
A1 = 123456-text 3" x 6 7/8" text
A2= 654321-test 104 1/2" x 80 3/4" text

Need a single formula that will apply globally to extract out the
width and height. I have provided examples that capture whole numbers
(ie. 3) and fractional numbers that can be single digit, double digit
and triple digit.
Thank you.

 

[Dave Peterson]

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]

Wow! This is far more complicated than I thought when I was attempting
for a solution.

Dave-->Your solution works to extract the first value, however, what
can I do to get the second value? The " x " is separating a width and
a height. I would like to extract those values into separate cells.

Thank you so much for your reply!!!

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.

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 fractional as
illustrated above.

 

[Dave Peterson]

It worked fine with the two examples you shared.

What was the text where it failed?

Wow! This is far more complicated than I thought when I was attempting
for a solution.

Dave-->Your solution works to extract the first value, however, what
can I do to get the second value? The " x " is separating a width and
a height. I would like to extract those values into separate cells.

Thank you so much for your reply!!!

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.

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 fractional as
illustrated above.

 

[Don Guillett]

Try this

Sub GetNumbersAndDecimalsfromFractionInText()
For Each c In Range("a1:a2")
p1 = InStr(1, c, " ")
'MsgBox p1
p2 = InStr(p1, c, "x")
'MsgBox p2
'MsgBox Mid(c, p1, p2 - p1 - 2)
c.Offset(, 1) = Mid(c, p1, p2 - p1 - 2)
c.Offset(, 1).NumberFormat = "0.000"
p3 = InStr(p2, c, "x") + 1
'MsgBox p3
p4 = InStr(p2, c, Chr(34))
'MsgBox p4
'MsgBox Mid(c, p3, p4 - p3)
c.Offset(, 2) = Mid(c, p3, p4 - p3)
c.Offset(, 2).NumberFormat = "0.000"
Next c
End Sub

 

[Mr. T]

Dave-->I didn't read your instructions carefully about selecting two
cells next to the cell with the text (I only entered the array in one
cell immediately to the right). Thank you so much for this solution! I
would never have figured this out on my own, as much as I'd like to
think I'm an advanced Excel user.

It worked fine with the two examples you shared.

What was the text where it failed?

Wow! This is far more complicated than I thought when I was attempting
for a solution.

Dave-->Your solution works to extract the first value, however, what
can I do to get the second value?  The " x " is separating a width and
a height.  I would like to extract those values into separate cells.

Thank you so much for your reply!!!

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.

 

[Mr. T]

Don-->Your solution is awesome as well - thank you very much!!!

 

[Dave Peterson]

If you ever get a chance to help at the design phase of an excel project,
remember that each cell should hold one unit of information (whatever that
means!).

I hate when things start out bad <vbg>.

Dave-->I didn't read your instructions carefully about selecting two
cells next to the cell with the text (I only entered the array in one
cell immediately to the right). Thank you so much for this solution! I
would never have figured this out on my own, as much as I'd like to
think I'm an advanced Excel user.

It worked fine with the two examples you shared.

What was the text where it failed?

Wow! This is far more complicated than I thought when I was attempting
for a solution.

Dave-->Your solution works to extract the first value, however, what
can I do to get the second value? The " x " is separating a width and
a height. I would like to extract those values into separate cells.

Thank you so much for your reply!!!

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 fractional as
illustrated above.

Dave Peterson