Bond prices convert to decimals

[J-EL]

I need to convert a bond price, which is expressed as (for example) : 99-07
and which really means 99 7/32. How can I do that in excel to be part of a
macro (like, convert everything with looks like 00-00 to 00 00/32, or even as
decimal ) . I'd appreciate any ideas anyone can offer me!!
Cheers

 

[Fred Smith]

The formula would be quite simple:

=left(a1,2)+right(a1,2)/32

However, I suspect you also have prices over 100, so sometimes you would have 3
characters to the left of the '-'. If so:

=left(a1,find("-",a1)-1)+right(a1,2)/32

If you really need a macro, post back and I'm sure others will help you. Give
them an idea of how your data is store (ie, all over the sheet, in a column,
etc.)

 

[J-EL]

Fred - you are awesome. That was extremely helpful. Although - I have to say
that I am curious about a macro which could do that "seek and convert" for a
column of prices. Let's say I had :
97.5
101-20
99-8
100.1

So - is it even possible to do that? Convert those two "dashed prices
without screwing up the already decimaled prices?

Also - does anyone know about macros for the old mainframe AS4000 ?

 

[Fred Smith]

This macro should do what you want:

Sub ConvertPrices()
For Each cell In Selection
If InStr(1, cell, "-") > 0 Then
cell.Value = Left(cell, InStr(1, cell, "-") - 1) + Right(cell, 2) / 32
End If
Next cell
End Sub

Highlight the range you want it to work on, then execute the macro. It converts
only those cells which have a dash in them. It skips any others.

Can't help you with the AS4000 -- I never worked on that machine. You're
probably better off to post this as a stand alone question.