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 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?

  • A quick googling led me to this. Might be helpful.
    – Dan Hogan
    Feb 8, 2016 at 20:35
  • Are all those examples of how your data is entered? 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?)
    – BruceWayne
    Feb 8, 2016 at 21:33
  • 1
    @Kerbal314159 Yes, these are examples from the actual data. This was user entered, and no restrictions were placed on format Feb 8, 2016 at 21:40
  • Oh goodness, that last sentence is scary. Hm, okay this will be trickier. Can you try and post an example of all the different ways the heights can be entered?
    – BruceWayne
    Feb 8, 2016 at 21:41
  • @BruceWayne I'll separate by ";": 5ft; 5 ft; 5'; 5 feet 2 inches; 5 ft 2 inches; 5'2"; 5' 2"; 5ft 71/2"; 5ft2in; 5ft 2in Feb 8, 2016 at 22:12

2 Answers 2


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".

  1. 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.
  2. Use SEARCH to find the position of " ft"; if it's not there, write "0"

    Col H: =IFERROR(SEARCH(" ft",G2,1),0)
  3. Calculate the number of feet

    Col I: =IFERROR(VALUE(LEFT(G2,H2-1)),0)
  4. 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)
  5. 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)
  6. 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)))
  7. 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)
  8. 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.

  • I just don't see how to efficiently use MID and RIGHT since there is so much variation between data points in the same column. Feb 9, 2016 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.