Inside INT, the value in B5 is divided by 12 and INT simply returns the integer portion of the result, discarding any decimal remainder. The result is then concatenated to a string with a single quote and space character.
To get a value for inches, the MOD function is used like this:
where number comes from B5 and the divisor is 12. Configured in this way, MOD returns the remainder after division. The result is concatenated to two sets of double quotes. The outer pair indicates text, and inner pair is needed for Excel to output a single double quote.
Finally, the INT code and MOD code are concatenated together and Excel returns the final text value.
To round inches to a given number of decimal places, wrap the MOD function in ROUND. For example, to round inches to one decimal:
To convert a measurement in feet and inches to inches only (i.e. 4'5" to 53) you can use a formula based on several functions, including LEFT, FIND, MID, and SUBSTITUTE. In the example shown, the formula in D5 is: = LEFT ( B5 , FIND ( "'...
To split dimensions that like 100x50x25 into three separate parts, you can use some rather complicated formulas that use LEFT, MID, RIGHT, FIND, LEN, and SUBSTITUTE. Note: you can also use Flash Fill in Excel 2013 and above, and the "text to columns...
To split a text string at a certain character, you can use a combination of the LEFT, RIGHT, LEN, and FIND functions. In the example shown, the formula in C5 is: = LEFT ( B5 , FIND ( "_" , B5 ) - 1 ) And the formula in D5 is: = RIGHT ( B5...
The Excel INT function returns the integer part of a decimal number by rounding down to the integer. Note the INT function rounds down, so negative numbers become more negative. For example, while INT(10.8) returns 10, INT(-10.8) returns -11.
Some formulas you see in Excel can be extraordinarily complex. But all complex formulas are built from simple steps. In this video, we built a more complex formula step by step.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.