Solved

# Custom Format Excel Cells for Bond Prices

Posted on 2010-01-06
544 Views
Hello experts,
I am looking for a custom format in excel that will display the fixed income bond price, which can be 104-2 as 104.0625. Basically we have to divide the number after the - by 32 and add it to the number on the left hand side of the hypen.

Can this be done? We do not want a UDF? We want something we can directly do in the excel sheet.

Thanks for your time and support.

\$wapnil
0
Question by:spattewar

LVL 81

Expert Comment

Since the data has a dash in it it is already a text string and thus you will be unable to use the numeric formatting options.

Your only option is to use a formula.

Kevin
0

LVL 81

Expert Comment

One formula is:

=VALUE(LEFT(A1,FIND("-",A1)-1))+VALUE(MID(A1,FIND("-",A1)+1,9))/32

Kevin
0

LVL 81

Accepted Solution

Don't need the VALUE functions:

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

Kevin
0

LVL 22

Author Comment

Thanks Kevin for the quick response.

I thought so but wanted to get it confirmed from a top expert :-)

\$wapnil
0

## Featured Post

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…