Reformat user input data

[michaelj07]

Hello-
I have a spreadsheet where a column reads:

4' 4"
4 ft. 4in.
52in.
etc...

I need to convert everything to inches without a quotation mark or abbreviation (in.). is this possible?

Thanks!
mj

[Bryan Hessey]

Hello-
I have a spreadsheet where a column reads:

4' 4"
4 ft. 4in.
52in.
etc...

I need to convert everything to inches without a quotation mark or abbreviation (in.). is this possible?

Thanks!
mj

Hi,

the formula

=IF(LEN(A1)>LEN(SUBSTITUTE(A1,"'","")),TRIM(LEFT(A1,FIND("'",A1)-1)*12)+VALUE(MID(SUBSTITUTE(A1,"""",""),FIND("'",A1)+1,LEN(A1))),IF(LEN(A1)>LEN(SUBSTITUTE(A1,"ft.","")),TRIM(LEFT(A1,FIND("ft.",A1)-1)*12)+VALUE(MID(SUBSTITUTE(A1,"in.",""),FIND("ft.",A1)+3,LEN(A1))),VALUE(SUBSTITUTE(A1,"in",""))))

does the three shown, you may need to adjust for minor variations.

hth
---

[michaelj07]

Thank you, but unfortunately the formula would need to cover a wide array of user input styles; more than just the three I mentioned. As humans, we can look at numbers such as 4ft 4in, 4' 4", 4 ft. 4", and know they all equate to 52"- I was hoping the program could do the same.

[Bryan Hessey]

Thank you, but unfortunately the formula would need to cover a wide array of user input styles; more than just the three I mentioned. As humans, we can look at numbers such as 4ft 4in, 4' 4", 4 ft. 4", and know they all equate to 52"- I was hoping the program could do the same.

as it could, if you have the resources and the manpower to write the code, but until then we just code for what we see, or are told about.

---