i'm downloading data into an excel spreadsheet from
bloomberg. bloomberg is a financial data provider.

the download is giving me prices in general format that
look like this. 99-27 3/4, 98-18, 103-16, 99-00 1/2.

basically 99-27 3/4 is equal to 99.8671875. you take the
fraction and 27 3/4 which is equals 27.75 and divide that
by 32. that gives you .8671875

is there any function in excel that will help me covert
the general number into decimal format. i need to change
all the general numbers into decimal format.

tks.

I'd use a macro:

And I got these when I tested:

99.8671875 98.5625 103.5 99.015625


Option Explicit
Sub testme01()

Dim myCell As Range
Dim myRng As Range
Dim myStr As String
Dim mySplit As Variant
Dim myValue As Double

Set myRng = Selection

For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
'do nothing
Else
myStr = myCell.Value & " 0 0 0"
myStr = Application.Trim(Application.Substitute(myStr, "-", " "))
mySplit = Split97(myStr, " ")
myValue = mySplit(LBound(mySplit)) * 1 _
+ mySplit(LBound(mySplit) + 1) / 32 _
+ Application.Evaluate(mySplit(LBound(mySplit) + 2)) / 32
myCell.Value = myValue
End If
Next myCell

End Sub
'from Tom Ogilvy
Function Split97(sStr As Variant, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

If you're using xl2k or higher, you can delete the split97 function and change
the call to just Split(mystr," ") (Split was added in xl2k.)

And you could replace application.substitute with Replace (also added in xl2k).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Select your range of cells
click on Tools|macro|macros...
click on the macro name (testme01--but you could rename it to something
meaningful!)
and then click run.


"R.Kim" wrote:

i'm downloading data into an excel spreadsheet from
bloomberg. bloomberg is a financial data provider.

the download is giving me prices in general format that
look like this. 99-27 3/4, 98-18, 103-16, 99-00 1/2.

basically 99-27 3/4 is equal to 99.8671875. you take the
fraction and 27 3/4 which is equals 27.75 and divide that
by 32. that gives you .8671875

is there any function in excel that will help me covert
the general number into decimal format. i need to change
all the general numbers into decimal format.

tks.

--

Dave Peterson