Text
Convert feet and inches text (5' 10") to total inches and back
Parse architectural measurement strings like 5' 10" into numeric inches with TEXTBEFORE and TEXTAFTER, and format inches back.
Why architectural strings cannot be summed directly
Lumber schedules and shipping dimensions often arrive as text strings like 5' 10". Because apostrophes and quotes make the cell text, SUM ignores them.
Extracting the number before ' (multiplied by 12) and adding the number between ' and " converts the text into pure numeric inches (5 * 12 + 10 = 70).
| SpecText (A) | Feet Part (*12) | Inches Part | Total Inches |
|---|---|---|---|
| 5' 10" | 60 | 10 | 70 |
| 6' 0" | 72 | 0 | 72 |
Parse feet and inches with TEXTBEFORE, TEXTAFTER, and CHAR(34)
VALUE(TEXTBEFORE(A2, "'")) * 12 converts feet to inches, and VALUE(TRIM(TEXTBEFORE(TEXTAFTER(A2, "'"), CHAR(34)))) extracts the inches before the double-quote symbol (using CHAR(34) avoids hard-to-read """" quadruple quotes).
This parser expects nonnegative feet and decimal inches with the straight apostrophe and quote characters shown. It does not parse fractional inch notation such as 3/8, curly quotes, or signed measurements.
=VALUE(TEXTBEFORE(A2, "'")) * 12 + VALUE(TRIM(TEXTBEFORE(TEXTAFTER(A2, "'"), CHAR(34))))Reverse total inches back to feet-and-inches notation
QUOTIENT(70, 12) gives 5 whole feet and MOD(70, 12) gives 10 leftover inches.
=QUOTIENT(70, 12) & "' " & MOD(70, 12) & CHAR(34)Try the example
Use the original values or change them to test your understanding.