Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse Cell Around "Dash" in Data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse Cell Around "Dash" in Data - More info
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse Cell Around "Dash" in Data - More info
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 wrote in message ups.com... 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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse Cell Around "Dash" in Data
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 Jim Jackson wrote: I think my first attempt went to "Cyber-Trash" Format the Measurement input cells to "TEXT" so the ".10" etc will display properly. The input must include at least a ".0" even if no inches are involved. FEET - =IF(MID(AB13,1,10)=".",LEFT(AB13,FIND(".",AB13)-1),AB13) INCHES - =IF(MID(AB13,FIND(".",AB13),1)=".",MID(AB13,FIND(" .",AB13,1)+1,10),AB13) -- Best wishes, Jim "Jim Jackson" wrote: To my horror I realized too late that the formulas only work where the "." is used with a number higher than "0" following it. It works for the feet/inches combination but only for that. Sorry. -- Best wishes, Jim "Jim Jackson" wrote: Input Feet Inches 3.1 3 1 Where cell under "Feet" is =LEFT(A13,FIND(".",A13,1)-1) And cell under "Inches" is =MID(AB13,FIND(".",AB13,1)+1,10) The "10" is just set large enough to handle however many digits might follow the ".". If 2 is the most to expect, you may change it to "2" or anywhere above that number. Your formula can then consider the 3 as feet and the 1 as inches for calculation. -- Best wishes, Jim " wrote: 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding data only if adjacent to cell labeled "male"/"female" | Excel Worksheet Functions | |||
Replace "dash" with "space" | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
What does the double dash or "--" do when using it in a function? (and other questions) | Excel Worksheet Functions | |||
Adding "New" "Insert" "Delete" into a workbook to change from data 1 to data 2 etc | Excel Programming |