topic Re: Schedules in feet and inches exported to Excel problem-H in Collaboration with other software

Schedules in feet and inches exported to Excel problem-HELP!

Anonymous — Wed, 18 Oct 2006 23:41:10 GMT

I have discovered a real stumbling block to using my schedules in excel.

I model everything so I can use the schedules to order materials from.

The problem is that with certain items like linear feet of closet shelving (Which shows up in feet and inches) has the foot sign (') and inch sign (") in the dimension text making it impossible to cross multiply or do special adding within Excel.

I even tried to change the preferences (working units & Levels) to decimal feet which worked, but then the dang foot sign was still there.

I know of no way to tell Excel to convert alphabet and number combinations into numbers only. ie, 30'-6" cannot be converted within Excel to 30.5 except manually, which if that is the only way to accomplish my goal, then exporting the schedule to excel is a futile effort because manually changing the volume of numbers I have would be absurd.

Is there a setting that will post the lengths, widths, heights, etc in decimal feet without the ' and " ?

You can see a cscreen shot of my schedule where I change it to decimals and the dang foot sign is still there.

Re: Schedules in feet and inches exported to Excel problem-H

Karl Ottenstein — Thu, 19 Oct 2006 19:46:14 GMT

vincon2 wrote:
I know of no way to tell Excel to convert alphabet and number combinations into numbers only. ie, 30'-6" cannot be converted within Excel to 30.5 except manually, which if that is the only way to accomplish my goal, then exporting the schedule to excel is a futile effort because manually changing the volume of numbers I have would be absurd.

Hi Jeff,

Please (and others) sign your real name to your messages per netiquette if you don't mind... At least I know who you are. 🙂

Excel can do anything, including what you want, because you can program it (and any other MS Office app) using Visual Basic. Attached is a spreadsheet with a custom function I just wrote called FFItoFeet() that will convert the combined feet and inches string into a decimal feet number. Hope it helps. You can copy the module into any other spreadsheet for use there.

Cheers,
Karl

Re: Schedules in feet and inches exported to Excel problem-H

TomWaltz — Thu, 19 Oct 2006 20:14:51 GMT

Karl wrote:
Excel can do anything, including what you want, because you can program it (and any other MS Office app) using Visual Basic.

Karl,

I think there's a difference between what you (Karl) and you (the average person reading this) can do with Visual Basic in Excel 😉

Re: Schedules in feet and inches exported to Excel problem-H

Anonymous — Thu, 19 Oct 2006 21:00:46 GMT

Karl wrote:
vincon2 wrote:
I know of no way to tell Excel to convert alphabet and number combinations into numbers only. ie, 30'-6" cannot be converted within Excel to 30.5 except manually, which if that is the only way to accomplish my goal, then exporting the schedule to excel is a futile effort because manually changing the volume of numbers I have would be absurd.
Hi Jeff,

Please (and others) sign your real name to your messages per netiquette if you don't mind... At least I know who you are. 🙂

Excel can do anything, including what you want, because you can program it (and any other MS Office app) using Visual Basic. Attached is a spreadsheet with a custom function I just wrote called FFItoFeet() that will convert the combined feet and inches string into a decimal feet number. Hope it helps. You can copy the module into any other spreadsheet for use there.

Cheers,
Karl

Hey Karl,

As always thanks for your help. Am I supposed to put my real name some where? Id didn't realize that was bad edicate. Where am I supposed put my name?

Anyway, this is Jeff Fairey. I attended the fall HTHN training in Yosemite in April 05 and met you there. I'm waiting to until it moves to Hawaii to go again.

Thanks again Karl!

Re: Schedules in feet and inches exported to Excel problem-H

Anonymous — Thu, 19 Oct 2006 22:10:56 GMT

Karl wrote:
vincon2 wrote:
I know of no way to tell Excel to convert alphabet and number combinations into numbers only. ie, 30'-6" cannot be converted within Excel to 30.5 except manually, which if that is the only way to accomplish my goal, then exporting the schedule to excel is a futile effort because manually changing the volume of numbers I have would be absurd.
Hi Jeff,

Please (and others) sign your real name to your messages per netiquette if you don't mind... At least I know who you are. 🙂

Excel can do anything, including what you want, because you can program it (and any other MS Office app) using Visual Basic. Attached is a spreadsheet with a custom function I just wrote called FFItoFeet() that will convert the combined feet and inches string into a decimal feet number. Hope it helps. You can copy the module into any other spreadsheet for use there.

Cheers,
Karl

I can't find the formula, when I hit "F11" a chart pops up. Where do I find the formula?

Do I just paste the formal in the adjacent cell, reference the cell that needs conversion and then do a copy drag to get the formula to apply to all the related cells?

Re: Schedules in feet and inches exported to Excel problem-H

Karl Ottenstein — Fri, 20 Oct 2006 02:31:38 GMT

vincon2 wrote:
I can't find the formula, when I hit "F11" a chart pops up. Where do I find the formula?

Do I just paste the formal in the adjacent cell, reference the cell that needs conversion and then do a copy drag to get the formula to apply to all the related cells?

Not F11, but Alt-F11 (alt key). Yes, F11 is autochart. Alt-F11 brings up the Visual Basic environment.

Yes, the FFItoFeet is the new function defined in the basic module. You can use it in any cell in any worksheet in the downloaded workbook as with any other forumula - fill down typically. But, you probably want it in your own spreadsheet. That's where you need to copy the module 'Conversions' that has the program code (script) for FFItoFeet in it into your new workbook to make it available there.

Cheers,
Karl

Re: Schedules in feet and inches exported to Excel problem-H

Geof Gainer — Sat, 20 Jun 2009 19:15:45 GMT

Very handy Karl, thanks. Have you or has anyone else made a module that will convert fractional inches as well as feet? 7 3/4" will not translate using this module, for example.

Thanks.

Re: Schedules in feet and inches exported to Excel problem-H

Karl Ottenstein — Sat, 20 Jun 2009 20:01:03 GMT

Geof wrote:
Very handy Karl, thanks. Have you or has anyone else made a module that will convert fractional inches as well as feet? 7 3/4" will not translate using this module, for example.

You're welcome. I had forgotten that I had written this ... said I was on meds in the macro comments, so I guess I was a bit out of it both memory wise and because the code did not handle fractions, even though "FFI" means "feet and FRACTIONAL inches". Duh.

Attached is a revision that handles fractions properly. I think.

Also, now that I'm on a Mac, the instructions below for getting into the Visual Basic editor don't work on Mac - you need to go to the Tools menu as shown in the next few screenshots. Note that Mac Office 2004 supports Visual Basic macros such as this, so this works on both Mac and PC. But, Mac Office 2008 dropped Visual Basic support, so it will not run there. (It should run in OpenOffice, I think, but it doesn't. Not quite sure why at the moment...but came across bugs on OO Calc and don't want to spend the time working around them.)

Cheers,
Karl

Re: Schedules in feet and inches exported to Excel problem-H

Karl Ottenstein — Sat, 20 Jun 2009 20:03:23 GMT

Screenshot of how to get into the Visual Basic editor in Office 2004 for Mac. (Windows users just do alt-F11.)

Re: Schedules in feet and inches exported to Excel problem-H

Karl Ottenstein — Sat, 20 Jun 2009 20:04:59 GMT

Screenshot showing where the conversion function shows up in Mac Office 2004 (looks pretty similar to Office for Windows) ... if you need to copy the code into another Excel project.

Re: Schedules in feet and inches exported to Excel problem-H

Anonymous — Sun, 21 Jun 2009 01:03:28 GMT

Karl wrote:
Screenshot showing where the conversion function shows up in Mac Office 2004 (looks pretty similar to Office for Windows) ... if you need to copy the code into another Excel project.

Hey Karl, you should put together a High Tech Fly Fishing (HTFF) in West Yellowstone where we can train in the middle of the day and fish in the am and pm.

Please don't tell me you're not into fly fishing.

Re: Schedules in feet and inches exported to Excel problem-H

Karl Ottenstein — Sun, 21 Jun 2009 16:35:07 GMT

vincon2 wrote:
Hey Karl, you should put together a High Tech Fly Fishing (HTFF) in West Yellowstone where we can train in the middle of the day and fish in the am and pm.

Please don't tell me you're not into fly fishing.

It's a possibility when the economy comes back and people feel like spending again. 🙂 I never picked up fly fishing (not that I'm not game), but some local ArchiCAD-architects are pros (used to be guides) and I bet could be talked into being our 'facilitators'. 😉 Would likely plan multiple activities for people to choose from including hiking, rock climbing, river running, trail biking, horseback riding, Yellowstone touring/photography, etc. to draw a good crowd.

K

Re: Schedules in feet and inches exported to Excel problem-H

Anonymous — Sun, 21 Jun 2009 18:02:10 GMT

Karl wrote:
vincon2 wrote:
Hey Karl, you should put together a High Tech Fly Fishing (HTFF) in West Yellowstone where we can train in the middle of the day and fish in the am and pm.

Please don't tell me you're not into fly fishing.
It's a possibility when the economy comes back and people feel like spending again. 🙂 I never picked up fly fishing (not that I'm not game), but some local ArchiCAD-architects are pros (used to be guides) and I bet could be talked into being our 'facilitators'. 😉 Would likely plan multiple activities for people to choose from including hiking, rock climbing, river running, trail biking, horseback riding, Yellowstone touring/photography, etc. to draw a good crowd.

K

I'm available to help if you ever decide to do this, just drop me a line

Re: Schedules in feet and inches exported to Excel problem-H

Geof Gainer — Mon, 22 Jun 2009 17:36:54 GMT

Um... fly fishing or no, nice revision. Thanks.

Re: Schedules in feet and inches exported to Excel problem-H

Karl Ottenstein — Mon, 22 Jun 2009 18:41:44 GMT

Geof wrote:
Um... fly fishing or no, nice revision. Thanks.

You're welcome, Geof. Now, to ponder why only people with the name Geof / Jeff are interested in this... 😉

Cheers,
Karl

Re: Schedules in feet and inches exported to Excel problem-H

Anonymous — Sun, 05 Jul 2009 14:29:28 GMT

Very nice Karl,

I had this same issue a couple of years ago. After a google search I found this,

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

It's a handy little Excel addin to convert from feet & inches to decimal inches and vice versa. It's a free download with a downloadable example that shows how it works.

Doug

Re: Schedules in feet and inches exported to Excel problem-H

Gus — Thu, 18 Jun 2015 12:53:19 GMT

Does this work with Office 2013?
I've never used visual basic before, but I have been following some tutorials on how to cut and paste this stuff to use the modules. However, I keep getting errors, specifically about an "ambiguous name?

On a side note, maybe there is an easier way to do this now. I'm basically trying to do an automatic take-off from schedules I created for all the members of a deck addition design