Convert fractional text to number

[maxman]

I've got a column of fractional text that needs to be converted to decimal number. They look like this:

1/2
5/8
3/4
7/8
1
1-1/4
1-1/2
1-3/4
etc.

If I multily the text by 1 to convert to a number, I end up with a 5 digit number likely a date or something. I want 1/2 to convert to .5 and 1-3/4 convert to 1.75, etc.

Please help.

 

[anglais428]

For the ones like "1/2" you could use something like this:

=IF(ISTEXT(Your_Cell),LEFT(Your_cell,1))/RIGHT(Your_cell,1)

How are you calculating the 1-3/4 to get 1.75?

 

[barry houdini]

If your data is in A2 down then try this formula in B2

=SUBSTITUTE(IF(COUNTIF(A2,"*-*"),"",IF(COUNTIF(A2,"*/*"),"0 ",""))&A2,"-"," ")+0

format B2 as number and copy down

 

[maxman]

thanks!

 

[maxman]

For the ones like "1/2" you could use something like this:

=IF(ISTEXT(Your_Cell),LEFT(Your_cell,1))/RIGHT(Your_cell,1)

How are you calculating the 1-3/4 to get 1.75?

1-3/4 in this case is 1 and three quarters, not 1 minus 3/4