Solved

Revising a formula to convert a number in decimal format to a particular text format

Posted on 2012-08-20
399 Views
A while ago I requested a formula to convert a number in decimal format to a text format using a ^ to separate the decimal portion of the number and convert it to 32nds.  I was given the formula below, which seemed to work fine:

=SUBSTITUTE(TEXT(ROUND(C3*32,0)/32,"0^0/32"),"/32","")

Now I need a (hopefully) small modification.  The formula above converts a number like 123.125 into the following text: 123^4, which equates to 123 and 4/32.  I now need it to appear as 123^04.  In otherwords, whenever the numerator of the fraction in 32nds is less than 10 it needs to have a zero in front of it.  For example, 123^1 would be 123^01, 123^7 would be 123^07 and 123^9 would become 123^09.

Can anyone adjust the formula for me?
0
Question by:pwflexner

LVL 24

Accepted Solution

I think this will do it...

=SUBSTITUTE(TEXT(ROUND(C3*32,0)/32,"0^00/32"),"/32","")
0

Author Closing Comment

Thanks!
0

Featured Post

Suggested Solutions

I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.