Feet and fractional inches to decimal

[4thorns]

Can someone please show me the steps to adjust the formula in cell B1 to give me decimal inches based on cell A1? The formula in cell C1 works fine but it's referencing B1 which has a bogus result. Studying it I've learned that it doesn't want the dash or spaces between feet and inches but I'd like to have the dash there but no spaces. Any help would be greatly appreciated.

Thanks,

Doug

 

[zelgar]

Here's a formula that will work based on how cell A1 currently looks:

=(LEFT(A1,FIND("'",A1)-1)*12)+(MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",A1)-FIND(" ",A1))+(MID(A1,FIND(" ",A1),LEN(A1)-FIND("/",A1)))/(MID(A1,FIND("/",A1)+1,LEN(A1)-FIND("/",A1)-1)))/12

 

[iv63]

=LEFT(A1,SEARCH("'",A1)-1)*12+MID(A1,SEARCH("-",A1,2)+1,LEN(A1)-SEARCH("-",A1,2)-1)
iv

 

[4thorns]

Thanks for the replies. I tweaked the original formula this morning and got it to work. Unfortunately things got hectic so I wasn't able to reply.
Here is a screenshot of the result. I still haven't figured out how it's giving me the fraction as a decimal. I'll have to study it some more.


iv, I found that formula on the net but it requires a different input format. I needed to keep my input the same as we've always done it.

Zelgar, I tried your formula but it gives me a value error. With Time I could probably figure it ot but for now I'm gonna go with what works(even tho I'm not quite sure why it works yet!)

Thanks again and if you happen to figure out where the decimal fraction is coming from feel free to let me know.

Doug

 

[Hoaokapohaku]

You might look at

http://josh.com/InchCalc/index.htm.

It seems to address the problem with varying input formats. I didn't analyze the formulas provided by others in detail, but a quick glance tells me that correct results will depend on the input format. It's very difficult to debug these kinds of formulas to be unbreakable unless you use some kind of input validation scheme that prevents input variance.

 

[4thorns]

iv, I feel like and idiot. That formula is just about a clone of mine. Should have tried it before I commented.

 

[4thorns]

Hoaokapohaku, I used to use Josh's Add-In. The problem was that when I transferred a file from home to work the add-in at work didn't assume control. The formulas all contained the path to my home computer. There may have been an easy way to fix it but I opted for using an in cell formula. As far as the input method, we only have one and it needs to be consistent. If it's input wrong, cells that reference it show a value error. Works as a quality control if you will.

Doug

 

[Hoaokapohaku]

Doug,

I haven't used Josh's add-in, so I really don't know if it is any good or not, only that the advertising seemed to indicate it addresses your problem. But assuming it does if you can keep everything straight when you change between the two machines, there is a simple fix. You could manually do it by using the edit links from the menu and edit the link to the incorrect path to the correct one. It's pretty easy, a half dozen mouse clicks or so. But if it were my spreadsheet, I'd write a little VBA procedure that does the same thing and does it automatically whenever the workbook is opened.

 

[4thorns]

I wish I had the time to study VBA. I actually copied and pasted some script for the very problem in the original post. Problem is I'm unsure how to use it. I've taught myself several things in the past with the help of the internet and forums like this but it takes a lot of time. VBA has been next on my list for a while and hopefully I'll get a chance to take a stab at it. Seems like it shouldn't be to tough.

Doug

 

[Hoaokapohaku]

If you are interested in the VBA way to solve your problem with using Josh's add-in, I can post something. Just didn't want to bother with it if you weren't interested in that kind of solution. So, are you interested in VBA only generally? Or, to solve the add-in problem?

 

[4thorns]

I'd hate to ask you to put a bunch of time into a script and then not have time to study it. The way I've done it (With Archicad and the gdl language to script 2d & 3d CAD objects) was to start with a simple script, pick it apart piece by piece to understand the lingo and what each piece of code was doing. That's why it's so @#$% time consuming. I don't ask for help until I'm 6 hours (or more) into something and ready to throw my computer into a dumpster!! As far as the original problem here I'm satisfied with what I have for the moment. Maybe if you have some links to basic tutorials you could post them. Again, time is a rare commodity for me right now but I wouldn't mind learning some VBA as I get a chance.

Thanks for the replies,
Doug

 

[Hoaokapohaku]

4throns - I know there are some tutorials out there. I forget where, but I'm sure google remembers. I think one of the simplest ways to learn, and how I got started, is just use the macro recorder to record an often repeated task. Then look at the VBA code generated and you will likely understand what it means since it is just recording the steps you took using the excel user interface. The recorder will record many unnecessary commands. Learn to clean up the code so that it does only what you need in the most efficient way possible. As you figure out how to do that, no doubt you will learn to do other things that can't be done through excel's interface. It just snowballs from there. The more you learn, the more you become aware of more possibilities and the more capable you become at coding solutions.

There are MANY websites that provide excellent info on VBA. Personally, I find ozgrid so valuable that I use it as my primary source of support information and search for answers there before anywhere else. For instance, if you want to know how to make a macro run automatically every time a workbook opens, google, "excel vba ozgrid run macro when workbook opens," and the first thing listed in google will be simple instructions on ozgrid.