VBA: Converting Ft. to decimal

[bamaisgreat]

Thanks for Looking. I am trying to come up with a way to convert a column of Fractions to a column of decimal. I have tried just formatting the cells but it doesn't seem to look the way I need it. Example: If I enter 6-11 3/8 I need it to show up in the other column as 83.375

 

[Rick Rothstein]

This formula seems to work...

=IF(A1="","",12*LEFT(A1,FIND("-",A1&"-")-1)+IF(COUNTIF(A1,"*-*"),MID(A1,FIND("-",A1)+1,2),0)+IF(COUNTIF(A1,"* */*"),MID(LEFT(A1,FIND("/",A1)-1),FIND(" ",A1)+1,9)/MID(A1,FIND("/",A1)+1,9),0))

 

[bamaisgreat]

This formula seems to work...

=IF(A1="","",12*LEFT(A1,FIND("-",A1&"-")-1)+IF(COUNTIF(A1,"*-*"),MID(A1,FIND("-",A1)+1,2),0)+IF(COUNTIF(A1,"* */*"),MID(LEFT(A1,FIND("/",A1)-1),FIND(" ",A1)+1,9)/MID(A1,FIND("/",A1)+1,9),0))

Thanks Rick, Worked Great

 

[István Hirsch]

Also, you may give this formula a try:

=CONVERT(LEFT(A1,MIN(FIND({"-"," "},A1&"- "))-1),"ft","in")+IFERROR(CONVERT(RIGHT(A1,LEN(A1)-FIND({"-"," "},A1&"- ")),"in","in"),0)

 

[Rick Rothstein]

Also, you may give this formula a try:

=CONVERT(LEFT(A1,MIN(FIND({"-"," "},A1&"- "))-1),"ft","in")+IFERROR(CONVERT(RIGHT(A1,LEN(A1)-FIND({"-"," "},A1&"- ")),"in","in"),0)

I like István's use of the CONVERT function in the second part of his formula, but I would point out his use of the IFERROR function restricts his formula to being used on XL2007 or later (my formula work for XL2003 and probably earlier versions as well... I don't have earlier versions to test it on). I would eliminate the first CONVERT function call though and go with a simple multiplication by 12...

=12*LEFT(A1,MIN(FIND({"-"," "},A1&"- "))-1)+IFERROR(CONVERT(RIGHT(A1,LEN(A1)-FIND({"-"," "},A1&"- ")),"in","in"),0)