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

[Anonymous]

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.

[Karl Ottenstein]

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

[TomWaltz]

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 😉

[Anonymous]

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!

[Anonymous]

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?

[Karl Ottenstein]

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

[Geof Gainer]

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.

[Karl Ottenstein]

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

[Karl Ottenstein]

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

[Karl Ottenstein]

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.