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. See illustration below: Formula =LEFT(B5,FIND(“‘”,B5)-1)*12+SUBSTITUTE (MID(B5,FIND(“‘”,B5)+1,LEN(B5)),””””,””) Explanation In the example shown, the formula in D5 is: =LEFT(B5,FIND(“‘”,B5)-1)*12+SUBSTITUTE (MID(B5,FIND(“‘”,B5)+1,LEN(B5)),””””,””) How this formula works In the first …
To convert a column number to an Excel column letter (e.g. A, B, C, etc.) you can use a formula based on the ADDRESS and SUBSTITUTE functions. See example below: Formula =SUBSTITUTE(ADDRESS(1,col_number,4),”1″,””) Explanation In the example shown, the formula in C5 is: =SUBSTITUTE(ADDRESS(1,B5,4),”1″,””) The first step is to construct an address that contains the column …
To convert a column letter to an regular number (e.g. 1, 10, 26, etc.) you can use a formula based on the INDIRECT and COLUMN functions. See example below: Formula =COLUMN(INDIRECT(letter&”1″)) Explanation In the example shown, the formula in C5 is: =COLUMN(INDIRECT(B5&”1″)) How this formula works The first step is to construct a standard “A1” …
To convert a measurement in inches to inches and feet (i.e. 53 to 4′ 5″) you can use a formula based on the INT and MOD functions. See illustration below: Formula =INT(A1/12)&”‘ “&MOD(A1)&”””” Explanation In the example shown, the formula in D5 is: =INT(B5/12)&”‘ “&MOD(B5,12)&”””” How this formula works This formula converts a numeric value …