I’m working with a survey tool that collects height data in the format like 5’8" and puts it into my Google Sheets document. The survey participants select their height from a dropdown list, and I need to keep this feet and inches display because it’s the most user-friendly option. The problem is that Google Sheets has trouble processing these single and double quote characters. I need to create a formula that can take values like 5’8" and transform them into total inches (68 in this case) in a separate column. This way I can use the numeric result for calculations. I’m struggling with how to parse the text that contains the apostrophe and quotation mark characters. Has anyone dealt with similar text processing in spreadsheets before?
if you’re into regex, you can use =REGEXEXTRACT(A1,“(\d+)'(\d+)”) to get the numbers, then just multiply the first one by 12 and add the second. it’s way simpler than using all those FIND functions.
I ran into the same issue with construction data. SUBSTITUTE + SPLIT works great for this. Here’s what I use: =VALUE(INDEX(SPLIT(SUBSTITUTE(SUBSTITUTE(A1,“'”,“|”),“"”,“”),“|”),1,1))*12+VALUE(INDEX(SPLIT(SUBSTITUTE(SUBSTITUTE(A1,“'”,“|”),“"”,“”),“|”),1,2)). It swaps the apostrophe for a delimiter, strips out the quotation mark, then splits everything to grab feet and inches separately. Yeah, it looks messy, but it handles weird formatting way better than other methods I’ve tested - especially with inconsistent survey data. Just make sure your height format stays consistent.
I encountered this same issue when dealing with fitness tracking data. The key is to use the FIND and MID functions to extract the feet and inches separately before performing the calculation. You can apply the formula: =(VALUE(LEFT(A1,FIND(“'”,A1)-1))*12)+VALUE(MID(A1,FIND(“'”,A1)+1,FIND(‘"’,A1)-FIND(“'”,A1)-1)). Just remember to replace A1 with the actual cell reference. This formula identifies the position of the apostrophe to determine the feet, multiplies that by 12, and then extracts the inches using the positions of both the apostrophe and the quote. It has worked effectively for me with various height formats, provided your data adheres to the foot’inch" structure.