Custom Format Excel Cells for Bond Prices

Posted on 2010-01-06
Last Modified: 2012-08-14
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.

Question by:spattewar
    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    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.

    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    One formula is:


    LVL 81

    Accepted Solution

    Don't need the VALUE functions:


    LVL 22

    Author Comment

    Thanks Kevin for the quick response.

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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    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…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now