Text fraction conversion to decimal

Hello, I am having a problem converting text fractions (eg. 6¾) into a decimal figure which I can work with. When I use the substitute function, Excel inserts a zero straight after the whole number, so instead of 6¾ converting to 6.75 it comes up as 60.75.
I have tried a few different ways to get it to work, but Excel is very flaky when it tries to deal with these text fractions and most often than not it gives a #VALUE error stating one of the elements is of the wrong data type.
Using the IF, MATCH and OR functions to find the specific fractions and deal with them seemed to be the most logical way to go, but it simply won't do it.
This is the function as I am using it:

=1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"¼",0.25),"½",0.5),"¾",0.75)

and I have attached a worksheet with examples.

Any help would be most appreciated and will ensure the bald patches where I have torn out hair will grow back.

The SUBSTITUTE function returns (and deals with) text values, so change your formula to this:

=1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"¼",".25"),"½",".5"),"¾",".75")

then copy down. I've removed the leading zeros and treated the fractions as text values by putting them within quotes.

Hope this helps.

Pete

Thank you that man, although I originally tried that. Every time I delete the 0 and pressed return, Excel puts it back! It's still doing it now, but by making a cut and paste straight from here to the sheet made it work. What's that all about then ? I think some computers and software just play by their own rules sometimes

Thank you again, Pete.

Thanks Al, that also works perfectly and elegant