Parse Cell Around "Dash" in Data

S

saraqpost

Though I understand Excel is a math system, and would see .1 and .10 as
the same number, my users do not.

The user gets data from suppliers, that shows:

Feet.Inches x Feet.Inches
12.1 x 8.10
9 x 7.5
11.10 x 10.9

(and so on)

Seeing these numbers as Feet dot (.) Inches, he wants to know the
Square Yardage value of the piece of carpet.

The problem is that 12.1 and 12.10 is the same number to Excel, but
it's 12 feet 1 inch in one case and 12 feet 10 inches in the other.

I can get the Integer, always, with the Int function, but I can't
figure out how to get the inches. If I can convert each column (length
and width being the 2 columns) to inches, I can get square yards by
using the formula (with cell references):

Inches (of Length) * Inches (of Width) / 1296

Any ideas?

thanks
 
S

saraqpost

Sorry - forgot about the "Dash"
I was thinking that if I told the user to put the data in as TEXT, 12-1
and 11-10, then maybe I could parse after the dash to get the inches
and use that to calc?

Thanks -
sara
 
B

Bernie Deitrick

Sara,

Excel will think the numbers with dashes are dates. You just can't win sometimes ;-)

The best solution is to use a formula: IF your values are strings (and that is an important IF), and
are in cells A2 and B2, then use this in cell C2:

=VALUE(LEFT(A2,FIND(".",A2)-1))+ VALUE(MID(A2,FIND(".",A2)+1,2))/12
and this in cell D2:
=VALUE(LEFT(B2,FIND(".",B2)-1))+ VALUE(MID(B2,FIND(".",B2)+1,2))/12

And then multiply them togeteher and divide by 9 to get square yards.

HTH,
Bernie
MS Excel MVP
 
S

saraqpost

Just wanted to let everyone know that the user came by and thanked me
for my help (and yours!) this morning. He spent 9 hours over the
weekend entering data and raved about the time our formula saved him.

Thanks again to all!
Sara
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top