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:
Here, we replace INT with the TRUNC function and use the ABS function to make sure that number inside MOD is positive. This is necessary because the INT function, contrary to its name, actually rounds negative numbers down away from zero. TRUNC, on the other hand, just chops off the decimal value and keeps the integer with the sign. The MOD function also behaves differently with negative numbers. By wrapping number inside the ABS function, we eliminate this difference. This formula will work fine with positive or negative inches as input. The final result carries the sign of the original inches value.
In this example the goal is to parse feet and inches out in the text strings shown in column B, and create a single numeric value for total inches. The challenge is that each of the two numbers is embedded in text. The formula can be divided into...
The 1st dimension To get the first dimension, we are using this formula in C4: = LEFT ( B4 , FIND ( "x" , B4 ) - 1 ) This works by extracting text starting at the LEFT. The number of characters is calculated by locating the first "x" in...
In this example, the goal is to split a text string at the underscore("_") character with a formula. Notice the location of the underscore is different in each row. This means the formula needs to locate the position of the underscore character...
The Excel INT function returns the integer part of a decimal number by rounding down to the integer. Note that negative numbers become more negative. For example, while INT(10.8) returns 10, INT(-10.8) returns -11.
The Excel TRUNC function returns a truncated number based on an (optional) number of digits. For example, TRUNC(4.9) will return 4, and TRUNC(-3.5) will return -3. The TRUNC function does no rounding, it simply truncates as specified.
Some Excel formulas are complex, but all complex formulas are built from simple steps. In this video we build 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.