Trying to figure out how to turn a ratio into a number

[MichaelBMF]

Searched around the web and MS help and all I could find is people trying to do the reverse of what I need.

So I have a spreadsheet that is exported in a data only method from another program. One of the columns of data provides information in a ratio format, for example "9:1".


How would one go about having that column return the numerical value of that ratio, like returning just a 9 for the above example so that it could be used in a further calculation?

I know it can be done because I have seen someone do it before, but he no longer works with the company.

Thank you in advance for any help that can be provided.

 

[Weazel]

I'm not sure if you mean to return it to a separate cell or isolate it in a formula to use in a calculation....
but you could try something like...

=0+REPLACE(A24,FIND(":",A24),99,"")

 

[Rick Rothstein]

More examples would have been useful. Does this do what you want...

=0+LEFT(A1,FIND(":",A1)-1)/MID(A1,FIND(":",A1)+1,9)

 

[chicagocomputerclasses]

does it come with quotes or without quotes?
9:1
"9:1"

 

[MichaelBMF]

Sorry if it was a little confusing but what Rick Rothstein had is exactly what I needed.

Thank you very much everyone for your help and hope you have a great 4th (if you celebrate the 4th if not have a great weekend

 

[barry houdini]

This works for me

=("0 "&SUBSTITUTE(A1,":","/"))+0