Bond prices convert to decimals
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.
--
Regards,
Fred
"J-EL" wrote in message
...
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" wrote:
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.)
--
Regards,
Fred
"J-EL" wrote in message
...
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
|