My friend you are a life saver! I feel like a weight has been lifted. I was doing a BOM for a project and I had over 400 length to add in that went to the 32nd and it was taken me forever to convert each one with my calculator. Thank you again.
Thank you so much! This is great! I was told we'd be going metric when I was in grammar school and we still haven't done it. I guess we'd have to get rid of all our rulers and that would be too wasteful :)
Dear Dan- this is a very exciting function! However Eevolve in Revit exports it as 15' - 3/16" i.e. there is a space on either side of the '-' symbol. Im digging through the .bas file with text editor, to figure out if I can add the spaces on either side, since the function doesnt seem to recognize ' - ' and I am working with almost a thousand values that need to be converted from the ' - " format to just feet. Would be a REAL life saver. Ill try to figure it out but on the off chance you see this, any advice would be deeply appreciated. I am a pretty bad programmer, but am trying my best :) Edit: Holy crap i did it- im so dumb to I cant believe it. Anyways for anyone else with same problem- open .bas file with text edit find this line: Public Const FtInchSeparator As String = "-" "-" --> " - " and it works! Bless you for this dan, I will have to do this SO MUCH IN THE FUTURE this is a massive time saver and I suck at coding. Couldnt have done it without ya cheers.
Hey good stuff! Must have been satisfying to figure that out and get it working :) Yeah I created those public variables expressly to deal with these scenarios actually, but it isn't mentioned in the video as I only added that feature after recording the video. Anyway glad you found it useful! Cheers Dan
Thank you for the file! However I have been getting this error message: Compile Error: Sub or Function not defined. I apologize but I'm not program savvy but can follow step by stop procedures. Appreciate the assistance and thank you in-advance!!
In Excel? Watch the video and use the function I shared. In general: use a calculator and google it! There's a million tutorials and videos out there explaining how to do this
This is an absolute gem! Thank you so much for sharing this knowledge. I have been using this code for about 3 years now and it works like a charm. Although, recently it stopped working and has been giving me #NAME? error. I double checked the spelling of the function and tried to relink the .bas file, but it still does not work. Could you please guide me with this issue? Could it be any updates from Microsoft excel? Thanks again!
You can choose to display the result to however many decimal places you like, just as you would with any other Excel function. Google it; there's no shortage of information on how to achieve this.
Thanks for the information 👍 😀 It's just what I've been searching for to use in my Geodesic Dome Gore Pattern Program that I'm working on. Now I need to figure out how to send the coordinates to a plotter and print out the patterns
So helpful!! My first experience using vba, but I think there’s an error when I save it- I try to get back into the file and the formulas stop working. Anyone have any ideas?
I trust you've since resolved this issue; sounds like you didn't add the module to your workbook correctly, or you haven't enabled macros. For the former; I demonstrate how to do this in the video, but if you're stuck I'd suggest googling a tutorial on how to add a VBA module to a workbook. For the later, again google how to enable macros.
The function's not intended to be used like this, and in fact I don't understand what you're even trying to achieve; you'll need to be more specific. If you're trying to add two lengths together to find the total length, use =StringToFt(I5)+StringToFt(I6) If you're trying to multiply two lengths together to give an area, use =StringToFt(I5)*StringToFt(I6)
Excel has a built-in SUM function; use that. If you don't know how to use just Google it, as it sounds like the problem you're trying to solve here has nothing to do with the feet and inches functionality
Very helpful! Very clear explanation Thank you for your efforts. I could download the "m_FeetInches.bas" code & install. Together with the Excel function "Convert" it's very easy to convert Ft,Inch,fractions to the metric system. I couldn't find your code for FtToString. So, I (we 'metrics') can not yet easily convert [mm] to [ft,inch,fractions]. But who cares? This 'handicap' can be considered as a positive contribution in helping our Imperial-system friends to migrate to the metric system. Again, thanks for your efforts!!
@@engineerstoolkit4900 Dear Sir/Ma'am, Thank you for your reply. I'm fine, I don't really need to convert metric to Imperial. I do need to convert from Imperial to metric and your files work fine for that purpose. Cheers!
Follow the instructions in the video carefully. This has worked for thousands of people already, so you're doing something wrong at your end. I've also already answered this question twice in the comments here; check out my response to the comment from @Vidya Shree
someone can help me do something to converte milimeters in feet and inche (ex: 1'-2" = 3556mm) each one in differents columns ft and in / mm. i need that to do a budgget. thanks
1 inch = 25.4mm 1 foot = 12 inches So to convert inches to millimeters, multiply by 25.4 To convert feet to millimeters, multiply by 12 (to convert from feet to inches) and then multiply again by 25.4 (to convert from inches to millimeters). So for your example, you have 1' and 2", so your total would be 1*12*25.4 + 2*25.4 = 355.6mm (not 3556mm, as you've written). Another example to make it clear: 3'-6 3/4" = 3*12*25.4 + 6*25.4 + 3/4*25.4 = 1,085.85mm That's quite a lot of math to get a single length isn't it? Yep, that's why the imperial system should be abandoned.
Really a very awesome, it is very helpful, Thanks a lot dan, but once i reopened the file its not working, i have no idea why, please help me out of this, thanks in advance
Skip to 4:52 in the video. You open the Visual Basic window using [Alt]+[F11] on the keyboard. If this is your first time adding a .bas file to a workbook, then I'd recommend you do a Google search for instructions if you get stuck again; there are plenty of tutorials out there
This is fantastic!! Thank you so much! I was wondering would you be able to have this still work or a modified version if the numbers didn't have the inches sign?
Hey bud, sorry for the slow reply. It's certainly possible, but it creates other problems that make it a bad idea and not worth coding, in my opinion. Omission of the inch symbols leaves the input ambiguous. If I give an input as simply 9 for example, should this be interpreted as 9 inches or 9 feet? It's necessary to define the input explicitly by inclusion of these symbols in order for it to be accurately and reliably interpreted. It's cumbersome having to include these symbols, yes, but that's the price you pay for having a measurement system that combines two different units of measure (feet and inches) to express the same concept (length).
Please can you help me sir, I have a datas like both feet inches and only inches. For example:- 12’-5 7/8” and 11 3/4”. For feet with inches value as converted with clear output, but only inches value i.e. 11 3/4” is not working,,,,
it didnt work for me. I copied the code and it didnt change the value nor did it give me an error. Length Cell= 8'-2" (FORMAT) TO Decimal Cell= stringtoft(d3) and does not provide conversion, it just stays that way.
The function works fine, so you've done something wrong. Check the comments below; I've helped by people with common mistakes already. Also, rather than copying the code, download the .bas file, as the format on my blog doesn't lend itself well to copy/paste. A function will always provide an error code, or will halt on a particular line of code. I can't provide more specific help of you don't state either of these. I'm hiking in the backcountry at the moment, so you'll need to figure it out from the comments below.
A program I use outputs my lengths with spaces on each side of the separator "-" is. How can I alter the script to include the spaces as part of the separator. Example: 92' - 4 9/16"
Hi there :) Need your help! I've downloaded and imported the vba code, but when I try to run the function it gives me a message saying use format 15'-5 3/16''. I've also gotten the “Invalid input; not numeric” message and found the info below in you're blog, but I'm not sure what that means. Exit Function End If
These are error messages that I've coded in there to try to give meaningful feedback if you put bad data in. The first one is from the StringToFt function, which is expecting you to input a String of text representing a dimension in the format 15'-5 3/16". You'll get this error message if you try entering a number or some string that isn't in the right format. The second one is from the FtToString telling you that you have entered something that isn't a Value. You need to enter a number in to it, representing your dimension in decimal feet.
Thank You! And to my fellow citizens of America professing the superiority of the Imperial system.....Why are you here looking for this function? If we used the Metric system the function would look like =(B1*B2) which last I checked is already in Excel. Assuming of course you had your cell format correct. I know the whole metrix prefix thingy gets confusing.....it's much easier to express a yoctometer as a fraction, duh. By the way....how big is your hard drive? And when you get that big ol' bottle of soda.... 'Merica huh? Your the type of "Merican that make Americans look stupid to other nations. And for those of you ready to jump all over me for because I wrote "your the" instead of "you're the) I did that on purpose. The "Mericans didn't even notice. That or you somehow have a grasp on grammar but just can't figure ou thow to multiply and divide by 1, 10, 100 etc. It IS tough.
Yep, I've built in functionality to accommodate this. Open VBA (Alt+F11), and then - within the m_FeetInches module - take a look at the code at the start and you'll find some in there for changing the separator between the inch and inch fractions, which you can customise. So change this line: Public Const InchInchFractionSeparator As String = " " To this: Public Const InchInchFractionSeparator As String = "*"
Thanks for the post and formula. I was just thinking of how much easier the metric system would be at work. With this, convert inches to feet, =fttostring and I am good to go. I was concatenating several cells before. Again, Thanks!
There's a few possible causes of this, in order of likelihood: 1) The VBA module containing the relevant code hasn't been added to your workbook. Download the .bas file I provided, and import it to your Workbook. If you don't know how to do this, Google it. 2) You've misspelled the name of the functions (FtToString and StringToFt). 3) You've imported the Module twice, so that you have two identical copies of the relevant functions. You must have only one unambiguous name for every VBA function, module, subroutine etc. You can't have two with the same name 4) You've named the Module in the VBA project exactly the same as the name of one of the functions. This causes a similar problem as above. Hope that helps.
With Excel open, hit Ctrl+F11 on the keyboard. If that isn't what you needed, google it; this kind of question has been answered a million times before.
Are you sure it's not at your end? If I play this video at 20% volume on my laptop with headphones then it's perfectly audible; any louder is uncomfortable. I'm wearing high quality over-ear headphones, but it's also fine with my earbuds. I've got some other videos besides this one that I made and yes the audio on them is shit because I didn't have a dedicated mic and recorded them with my built-in laptop mic. Those definitely need headphones to be able to hear. But for this video I actually went out and purchased an external mic specifically for this purpose, and was speaking right in to it. I think you're just going to have to put more effort in at your end, as I put this together for free with what I had available just to try to help people. Either that or you can buy me a nicer mic and I'll redo it for you ;) Sounds like you got it to work though, and found it useful, so glad to hear you got there. Thanks for the message!
Good job. I need to show like this, i also write 2'-3" & multiple with 5'-6" then showing result. I dont write string to feet & feet to string. Pls make a new vba code for this function. If it is possible
This doesn't need new VBA code; you can already do it with what I've already shared. You can achieve all of this in a single cell, but - to make it clear what we're doing - I'll first describe it using multiple cells: Lets say in Cell A1 you have 2'-3" And in Cell A2 you have 5'-6" First turn the two Strings in to Values, let's say using Cells B1 and B2: B1=StringToFt(A1) B2=StringToFt(A2) Then you multiply the result together, lets say in cell B3: B3 = B1*B2 Then finally you convert it back to a string, let's say in Cell B4: B4=FtToString(B3) Ok, so how to achieve this in one go? You nest all of these operations together: B4=FtToString(StringToFt(A1)*StringToFt(A2)) That will give you the result in a single operation, with the answer given in square feet. If you want the answer in square inches, then you divide each dimension by 12 before you multiply them together: B4=FtToString(StringToFt(A1)/12*StringToFt(A2)/12) Messy? Yes, it is; this is the price you pay for having multiple units of measurement (inches, feet, yards, miles...) combined in a single system. You could re-write the VBA code to make the input cleaner in order to achieve this, for example: B4=TimeToSwitchToMetric(A1,A2) But if you really want this then you're going to have to write it yourself
Great presentation and description of the difficulties of Feet and Inches with Excel. But complete WORTHLESS, because no information about how he created the =strings that do the work.
Ha easy there fella. Remember the Internet isn't just a faceless void in to which to rant and rage; there's people at the other end mate. I think what you meant to say is "I'm having some trouble trying to use this, can I please have some help?" Yeah, of course you can :) It sounds like you haven't figured out how to install the .bas module file that contains the code I wrote that will allow you to use these functions. There's a link to download this in the description, as I mention in the video. There's also a ton of descriptions online of how to add .bas files to a workbook. But if you still can't figure that out, feel free to come back and have another go at politely asking for help and I'll step you through it 👍
@@engineerstoolkit4900 Thanks. I might be able to dig through the installation, but I can't find the download location url. Google searches from here don't show the http:
On my blog the download link is given as a hyperlink in the text under the section called "DOWNLOAD". Again, you'll also find it in the description I wrote at the bottom of this RU-vid video. But for convenience, here it is again: www.dropbox.com/s/0lvgf0779ywstxl/m_FeetInches.bas?dl=0 Hope that helps get you going
This video mentions that the actual code is available via your blog. What is your blog? I googled Engineer's toolkit but I'm not finding it or anything else relevant. Thanks,
Absolutely perfect. Quick question: any way to round StringToFt()? I get some numbers like 19.66666667 and would like to trim it to 19.7 Thank you very much!
Yes, Excel already has this functionality built in to it. If you're just trying to DISPLAY the truncated version of the number, then use Number Format: exceljet.net/custom-number-formats The precise value remains stored, but it just doesn't display all the additional decimal places. This is generally preferred. If you really want to round the value itself to 19.700000 then use ROUND. For example, =ROUND(StringToFt(A1),1) rounds to 19.70000.
I'm getting a VB Debug error on this line, "Value = Fix(CDec(Value * 12 * Dnmtr) + 0.5) / 12 / Dnmtr". It is highlighting "CDec". I'm not sure why this is happening. I'm running Excel on a Mac, but that shouldn't be a reason.
@@deffhaus mac's don't have the cdec function. I'm told that removing it fixed the problem, but would caution that this may result in erratic rounding errors (eg 1.5 rounds up to 2, but 3.5 rounds down to 3 kind of thing)
It looks like Google changed the security settings for file sharing, resulting in me getting lots of emails requesting permission to view the files. I've moved them to Dropbox now, and updated the links, so hopefully that works now
Sorry I don't understand; you'll need to explain more clearly what you're trying to do. It sounds like you're trying to multiple Feet and Inches together to get an Area of Foot-Inches, which is mixing units, which isn't a good idea! Areas should be expressed in Square Inches or Square Feet; not Foot-Inches! Perhaps this will help: * If you have a value in Cell A1 that is in inches and you'd like to use my function to express it in Feet and Inches then you'd need to first divide it by 12 to convert it to Feet, as my function asssumes this to be the units of the input value. E.g. if you have 27.5 in Cell A1, you'd use =FtToString(A1/12) to give 2'-3 1/2" * If you have a value of 2'-3 1/2" in Cell A1 and you want to use my function to convert it to a length value in inches, you'd need to subsequently multiply it by 12 to convert it to Inches, as my function will otherwise be expressing the value in Feet. E.g. if you have 2'-3 1/2" in Cell A1, you'd use =StringToFt(A1)*12 to give 27.5
Yes; at the start of the code I define two variables: Public Const FtInchSeparator As String = "-" Public Const InchInchFractionSeparator As String = " " These are the characters that separate the feet, inches, and inch fraction parts of the string. Just change the character between the quotation marks to whatever you need. In your case, you need to change for this line of code to: Public Const FtInchSeparator As String = " " EDITED COMMENT: I initially responded to say this wasn't possible, as an initial version of the code I wrote wasn't flexible enough to achieve this, but I forgot that I'd revised the code to enable this functionality back in December of 2017, so it should already by possible.
I'm not clear exactly what you are looking for here; your question doesn't really make much sense in the context of Excel, which is typically used to express values in cells. 6" x 6" is not a value; it's an equation. The result of that equation is an area; 36in². Regardless, whatever you're trying to do here, my functions will be of no help because the problem they are intended to solve is expressing a length in terms of mixed units of feet and inches. It's not intended (or needed) for expressing areas, which do not involve mixed units.
I think the problem's at your end mate; the link posted in the description works fine for me, and apparently for everyone else too. If you're really struggling the code is also pasted in plain text in my blog, also in the description.
Ha yeah, I assume you're joking here mate with a flagrant strawman argument. You'll notice my complaint was about the imperial system of measurement, not the base-12 number system. They're two different things. I'm well aware of the advantages a base-12 number system would have provided...but as I'm sure you're aware we don't live in a world that has embraced the base-12 number system, as is evidenced by the fact that we only have 9 characters to express numbers, not 11 as would be required to really implement the base-12 number system effectively. If you want to start campaigning for changing the way we count I'll gladly say "yep, you were right" when the world comes around to your point of view. That might take a while though, so hopefully we can at least start using the metric system in the interim. Oh, and I trust you realize I'm not European.
@@engineerstoolkit4900 Feet and inches may not be true base 12 with only 9 digits, but its still a hands down superior system of measurement in real world applications compared to the metric system. First of all, mm cm and meters match up very poorly with most common building material lengths and always lead to painful mental arithmetic during construction compared to feet and inches. I agree that most other imperial units like cubic yards are needlessly dumb, but doing mental arithmetic in base 10 when your out in the field without the luxery of a pen paper and calculator really brings out the unspoken shortcomings of the metric system. Base 10 is a terrible system, even computers have issues with it (1 + 2 == 3 ; false). Only someone whos never sank a 45 toe nail in their lives would seriously think that 0.5 + 0.334 = 0.884 is a superior measuring system to 6" + 4" = 10".
@@pinkhead6857890 - *"First of all, mm cm and meters match up very poorly with most common building material lengths and always lead to painful mental arithmetic during construction compared to feet and inches."* This isn't the fault of the metric system. This is the fault of American building material manufacturers who are too pig-headed to use sensible metric units when making stuff. This isn't a problem here in Australia, as our manufacturers use metric, so we don't have to do any painful mental arithmetic during construction. (Well, except when we need to use something made in America.. then we have to do painful mental arithmetic too. But those instances are thankfully rare!)😀
Hello and thanks for this useful tool! One thing i'd like to ask..can you change to show negatives instead of parenthesis? I saw you changed this code before and just wondering if there is a way to undo this. Thanks again!
Hey, sorry but I don't think it's worth changing the code back as there was a good reason to implement it like this. The '-' symbol is usually used to separate feet and inches as in 6'-3 5/8", so it significantly complicates the coding (which works by searching for these characters), if you have one symbol serving two purposes. You could have a go yourself of course, if you have some programming skills, but I think when you start digging in to this you'll realize it's not a good approach. I'd also add that 1) It's visually hard to spot negative values this way 2) I've never seen anyone express a length in this format as a negative, so you should probably try to avoid doing this if others are to review your work, as it'll likely cause confusion.
@@engineerstoolkit4900 Thanks for the clarification and makes sense what you are saying. Your code still comes in very handy for some of the things I need to do so Thanks again!
Yes, but you don't need my functions to do this. If you have the text "1.2,m" in call A1 and you only want to extract the numbers from the left side (including the decimal point) then you'd use =LEFT(A1,3)*1 This pulls the left three characters (the 1 and 2 and the decimal point between them), and then multiplies by 1 to force Excel to convert this to a value. Alternatively use Flash Fill; if you google this you'll see examples.
@@engineerstoolkit4900 Thank you, I ended up figuring this out after playing text to columns. I can't believe it took me so long to do figure it out. I appreciate your response. I didn't know there is a second way to do this.
These are awesome functions. Thank you so much. So thoroughly written and well thought out. I work in architectural engineering and often take my measurements with a tape measure in inches. Any plans on tweaking the functions to convert decimal inches to display as feet and inches :-). No worries if your not. I found an interim solution by using the convert function in Excel to convert my decimal inches into decimal feet. Then I used your function to convert the decimal feet to display as feet and inches. Using these functions certainly saves me time, so cool!
Hey thanks for the feedback, glad you're finding them useful! I might be misunderstanding what you're looking for here, but isn't conversion from inches to feet simply a matter of dividing by 12? So if you have decimal inches in cell A1 you'd use =FtToString(A1/12) This is how I achieve it, and it's easy enough to not warrant a separate function I think?
@@engineerstoolkit4900 Thanks Dan, your absolutely right. What an oversight by myself. Sometimes you don't see the things right in front of you. This makes the functions even better. I only have to use one column in Excel to get them to display in feet and inches...really great work, very impressive.
@@kirkcrowther1075 Yeah when you start thinking in terms of VBA it's easy to forget about the simple spreadsheet functions like that; it's happened to me on several occasions!
Sorry I have no idea. I know that others have reported issues with the CDEC function when used on Mac's, but - once this is removed - it reportedly works fine on a Mac. Personally I've only ever used windows-based platforms.
Thanks for sharing this. Unfortunately I couldn't get it to work on my machine... I'm getting this error: Compile error: Sub or Function not defined and "CDEC" gets highlighted.
Hmmm the compile error is telling you the CDec function isn't being recognized, which is odd, because it's a native function in VBA. What version of Excel are you running? You're not trying to run this on a Mac or something?
@@engineerstoolkit4900 Yes, I'm running it on a Mac. :) I ended up deleting that row and everything works fine. I'm assuming it "only" affects the rounding method.
Cool beans, glad you got it working. From memory I was using that function to resolve issues with floating point rounding error that would result in inconsistent rounding. I think it's only an issue if you're unlucky enough to have values that are right on on the limit of the fractional precision. e.g. 0.499999999 rounded to the nearest whole value should round down 0.0, but instead rounds up to 1.0; that kind of thing. It's been ages since I worked with this code though, so I don't rightly remember. I'd suggest that if you're using it for critical work you should test is extensively, particularly in regards to rounding error, to satisfy yourself that it's still behaving as you'd expect it to. As always, use at your own risk! :)
@@engineerstoolkit4900 Yes, that was my understanding, going through your description on the Wordpress site. In this recent project we were rounding to the quarter. I tested a few values and it seems to work fine. BTW I ended up adding a =LEFT(A1;LEN(A1)-1) to get rid of the apostrophe at the end of each decimal ft value - that's how ArchiCAD displays it. Thanks again!
