Dividing a Number of Inches and Returning feet and inches

[BobH]

Is it possible to set up a formula that will divide a cell that contains a number of inches that will return and display the number of feet and the number of inches contained in the cell the formula acts upon?

IIRC there is a modulus function that will return remainders; thus a formula that operates on cell A2 (for example) with =MOD(A2/12) will return a zero if the A2 value contains a factor of 12 but will return the inches and fraction thereof (depending or formatting chosen for cell containing the formula), but it does not return the number of factors of 12 (ie, feet) contained in A2.

I cannot think of a way to get both results displayed in a single column with but one formula. Obviously, I could round the result of division by 12 in a cell and determine the modulus in a cell below it and sum concatenate the 2 cells to get the result, but I was wondering if there is a way to do it with but one formula.

[Jay Freedman]

Using the convention of a single quote as an abbreviation for feet and a double quote for inches, this function will get the correct results:

=CONCAT(TEXT(QUOTIENT(A1,12),"0"),"'",TEXT(MOD(A1,12),"0"),"""")

[Rudi]

CONCAT will cause a #NAME? error in Excel as that function is not recognized.

To correct Jays function, it should be:
=CONCATENATE(TEXT(QUOTIENT(A1,12),"0"),"' ",TEXT(MOD(A1,12),"0"),"""").

As a matter of fact, you could shorten it to the following for the same result:
=INT(A1/12)&"' "&MOD(A1,12)&""""

[HansV]

Rudi,

CONCAT is a new function in the Office 365 version of Excel 2016, and in the online/iOS/Android versions. See CONCAT function. It is not available in the installer version of Excel 2016, nor in older versions.

[Rudi]

Oh goodness... =CONCAT( , " and " , )
Ignorance is bliss until you're informed!

[Jay Freedman]

As a matter of fact, you could shorten it to the following for the same result:
=INT(A1/12)&"' "&MOD(A1,12)&""""

Rudi, I bow to your superior experience. You can tell I'm not a frequent Excel user. I would have reached for a formula like that if I was thinking in Basic, but instead I looked in the Help(less).

[Rudi]

Jay, we each have individual fields of interest and preference and we all learn from each others strengths. Right now, the scale of helpfulness and experience still tips heavily in your favour with all the things I have learned from your Word skills and guidance over the years.

[BobH]

Thanks for the information!!!!

Could you tell me, please, why there are 4 double quotes at the end of the formula?

[HansV]

Excel uses " to mark the beginning and end of a fixed text string, for example "BobH" is used to return the text string BobH in a formula.
What if we want to return a double quote as the sign for inch? You might think """: the first " marks the beginning of the string, the second one is the content, and the third one marks the end. But this would confuse Excel: it sees the first " as beginning, the second " as end, and then it doesn't know what to do with the third one.
The convention (which you have to know) is to use two " in a row if you want to include a " as a character inside a string. So in """", the first " marks the beginning, the second and third one stand for ONE " inside the string, and the fourth " marks the end.
Clear as mud?

[BobH]

I guess there is a programmer somewhere for whom the solution is as clear and obvious as crystal.

[Jay Freedman]

I guess there is a programmer somewhere for whom the solution is as clear and obvious as crystal.

There is, and his name is Hans.