Schedules in feet and inches exported to Excel problem-HELP!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2006-10-19 01:41 AM
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.
- Labels:
-
Data management

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2006-10-19 09:46 PM
vincon2 wrote:Hi Jeff,
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.
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2006-10-19 10:14 PM
Karl wrote:Karl,
Excel can do anything, including what you want, because you can program it (and any other MS Office app) using Visual Basic.
I think there's a difference between what you (Karl) and you (the average person reading this) can do with Visual Basic in Excel
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2006-10-19 11:00 PM
Karl wrote:Hey Karl,vincon2 wrote:Hi Jeff,
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.
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
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2006-10-20 12:10 AM
Karl wrote:I can't find the formula, when I hit "F11" a chart pops up. Where do I find the formula?vincon2 wrote:Hi Jeff,
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.
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
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2006-10-20 04:31 AM
vincon2 wrote:Not F11, but Alt-F11 (alt key). Yes, F11 is autochart. Alt-F11 brings up the Visual Basic environment.
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?
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2009-06-20 09:15 PM
Thanks.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2009-06-20 10:01 PM
Geof wrote: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.
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.
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2009-06-20 10:03 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2009-06-20 10:04 PM