Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

Custom Format Excel Cells for Bond Prices

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
spattewar
Asked:
spattewar
  • 3
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
One formula is:

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

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Don't need the VALUE functions:

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

Kevin
0
 
spattewarAuthor Commented:
Thanks Kevin for the quick response.

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

$wapnil
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now