On Mon, 17 Aug 2009 22:34:01 -0700, DWR wrote:

Hi Bernard,

I am trying to conver the following into an excel sheet from the US Bonds
Market.

This is a tricky one.....today's closing price on Bonds was 119'215

That translates to 119 and 21.5 32nds of point.

In other words 119 21.5/32

How can I get excel to recognize this in this format?


Thanks!

David

You won't be able to express it that way as a numeric value.

You could express it as 119 215/320

First, convert it to a decimal dollar value:

IF you have the Analysis ToolPak installed, or if you have Excel 2007+

=DOLLARDE(SUBSTITUTE(A1,"'","."),32)

If not:

=LEFT(A1,FIND("'",A1)-1)+MOD(SUBSTITUTE(A1,"'","."),1)*100/32


Then, either format the cell as 0 ???/320

or surround the formula with the TEXT function to apply that format.

=TEXT(DOLLARDE(SUBSTITUTE(A1,"'","."),32),"0 ???/320")


--ron