CODEWITHSUNDEEP
vbaexcel
Lost
Lost

Reputation: 23

Evaluating the content of a cell for a formula

Let's say I have a column of values like:

    A
 3' x 5'
 4' x 6'
 5' x 8' 
 6' x 9'
10' x 14'
2' 3 x 8'

How can I split these values up as integers so I can do operations on them?

For the first few values I obviously want to multiply 3 x 5, 4 x 6, etc. but it gets tricky for values like the last shown above. In that case, it would need to be 2.25 x 8 (.25 == 3/12 inches). Is this possible?

Upvotes: 1

Views: 76

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Give this a try:

Public Function area(s As String) As Variant
    Dim ary, ti As String, leng(0 To 1) As Double, i As Long
    Dim bry
    ti = Chr(39)
    ary = Split(Replace(s, " ", ""), "x")
    For i = 0 To 1
        If Right(ary(i), 1) = ti Then
            leng(i) = CDbl(Mid(ary(i), 1, Len(ary(i)) - 1))
        Else
            bry = Split(ary(i), ti)
            leng(i) = CDbl(bry(0)) + CDbl(bry(1)) / 12
        End If
    Next i
    area = leng(0) * leng(1)
End Function

Upvotes: 3

CRondao
CRondao

Reputation: 1903

First make sure you use TRIM to remove spaces in the beginning and at the end, then use:

=(LEFT(A1;FIND("'";A1)-1)+("0" & MID(A1;FIND("'";A1)+2;FIND("x";A1)-FIND("'";A1)-2))/12)*MID(A1;FIND("x";A1)+1;LEN(A1)-FIND("x";A1)-1)

Depending on your regional settings you may need to replace ";" by ","

Upvotes: 3

Related Questions