# How do I convert data for height that's in multiple formats into inches?

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?

• A quick googling led me to this. Might be helpful. 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?) Feb 8, 2016 at 21:33
• @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? 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

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