Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this community
I have a column with several thousand heights, in different formats, that I would like to convert to inches.
Examples from actual data:
5 ft 2 in
6 feet 3 inches
6ft 3 inches
6 feet 3"
5*8
5.3
5'10"
5 71/2
I'm thinking there is some way to get the feet in one column and the inches in a second to calculate inches. Feet seems easy, with LEFT, but I'm not sure how to reliably get the inches in another column.
What's the best way to do this?
This will be a multi-step solution, and I would solve it by breaking it into parts.
I will use your semi-colon delimited list (from the comments), plus one additional value: 5ft 7 1/2".
Standardize the list as much as you can using SUBSTITUTE:
Col B: Replace "feet" with "ft" =SUBSTITUTE(A2,"feet","ft")
Col C: Replace "'" with "ft" =SUBSTITUTE(B2,"'","ft")
Col D: Replace "ft" and " ft" with " ft"
=SUBSTITUTE(SUBSTITUTE(C2,"ft"," ft")," ft"," ft")
Col E: Remove "inches" =SUBSTITUTE(D2,"inches","")
Col F: Remove "in" =SUBSTITUTE(E2,"in","")
Col G: Remove " =SUBSTITUTE(F2,CHAR(34),"")
(CHAR(34) is the double quotes symbol, ")
This can also be done with a long nested formula. I prefer simple.
Use SEARCH to find the position of " ft"; if it's not there, write "0"
Col H: =IFERROR(SEARCH(" ft",G2,1),0)
Calculate the number of feet
Col I: =IFERROR(VALUE(LEFT(G2,H2-1)),0)
Inches is harder, because it can contain fractions.
Determine the text representing inches and trim leading spaces using TRIM.
Col J: =IFERROR(MID(G2,H2+2,1000)),0)
Col K: =TRIM(J2)
Determine if there are fractions in the "inches" text.
If there is a mixed fraction, the whole numbers should be separated from the fraction by a space (71/2 = 35.5; 7 1/2 = 7.5)
Find the position of any spaces and "/" symbols after them.
Col L: =IFERROR(SEARCH(" ",K2,1),0)
Col M: =IFERROR(SEARCH("/",K2,L2+1),0)
Find the number of inches or (if there is a mixed fraction) the whole number of inches.
If the inches text = "", the number of inches is 0.
If there is no "/" symbol, assume the number is a decimal number.
If there is a "/" symbol, the whole number is the number before the space
Col N: =IF(K2="",0,IF(M2=0,VALUE(K2),IFERROR(VALUE(LEFT(K2,L2-1)),0)))
If there is a "/" symbol, calculate the numerator and denominator.
If there is no fraction, denominator = 1.
Col O: Numerator =IFERROR(VALUE(IF(M2=0,0,MID(K2,L2+1,M2-L2-1))),0)
Col P: Denominator =IFERROR(VALUE(IF(M2=0,1,MID(K2,M2+1,100))),1)
Add the list of numbers
Col Q: =I2*12+N2+O2/P2
You can use MID or Right for inches or you can convert text to column. Also there is a Formula Convert you can use after separating the data,this formula can convert all kind of units see the help for it.
5 ft 2 in,6 feet 3 inches,5*8(for five feet eight inches?),5.3(is this 5 foot three inches, or 5.33333 feet?)