HELP to find the difference between two lengths in feet and decimal inches format

First off, I'm new here. I'm not positive I worded the title well enough to understand my issue. I have attached a spreadsheet to show what I'm looking for. I currently have a spreadsheet with two columns. Our target draft listed in ft and inches (11' 5"), and our actual draft listed in ft and decimal inches (11' 0.547"). I need to find the difference between the two values in inches. in the sample spreadsheet I have highlighted the 3 columns I'll be working with. I need to subtract the figures in column M from the figures in column F. I have tried to research and find a way to do this and I just cannot seem to make this work. Any help would be GREATLY appreciated.

Thanks,
Lauren

Some awkward little " ' in there. try this to transform column F into inches:

=IF(F2="","",LEFT(F2,FIND("'",F2)-1)*12+LEFT(MID(F2,FIND("'",F2)+1,255),LEN(MID(F2,FIND("'",F2)+1,255))-1))

and this to transform actual draft to inches:
=IF(M2="","",LEFT(M2,FIND("’",M2)-1)*12+SUBSTITUTE(MID(M2,FIND("’",M2)+1,255),"”",""))

Can you explain what you mean by £it didn't work" for column N?

I had a wee concern that it might break down for M... some unusual character symbols.

try this, instead:

=IF(M2="","",LEFT(M2,FIND("’",M2)-1)*12+LEFT(MID(M2,FIND("’",M2)+1,255),LEN(MID(M2,FIND("’",M2)+1,255))-1))

Here's an alternative:
Column M =IF(M2="","",LEFT(M2,FIND(CHAR(146),M2)-1)*12+SUBSTITUTE(MID(M2,FIND(CHAR(146),M2)+1,255),CHAR(148),""))
Column F =IF(F2="","",LEFT(F2,FIND(CHAR(39),F2)-1)*12+SUBSTITUTE(MID(F2,FIND(CHAR(39),F2)+1,255),CHAR(34),""))

Oh good - the approach at Post 5 worked for you!! You can try/ignore the alternatives suggested at Post 7, as you wish.

Anyhow, glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

Hi. How are the data in P being entered? If you look closely at the feet symbol - there are TWO different symbols in use!! That's what's causing the problem. The feet symbol in P1162 is not the same as P 1163. Is this being manually entered, or what??

Is there ALWAYS a space between the feet and the inches in column P??

Are you doing that consistency check manually? If so, we can get Excel to do it for you. Let me know.