number formats

[blackbox]

How can I combine decimal and fraction number formats. example 12.5/32
I've been using 64ths but it's not the proper format for my application.
Under format cells > number > custom, I tried entering 00.0/32 but excel
won't accept it.

 

[Bob Phillips]

Is this any good for you

0 ?#/32

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

i would try four cells A1 would be for the 12,b1 is =if(c1="","","."),c1 is
where the part fracton goes and d1 is the denominator.In cell e1 try
=A1&B1&C1&"/"&D1.Why isnt 12 and a half 32 equal to 25/64 ?

hth
paul
remove nospam for email addy!

 

[blackbox via OfficeKB.com]

I thought of that but would rather do it in a single cell if possible.
Every day I receive data that isn't in a usable format, I use the MID
function to break it apart.
the data comes in this format 106125. This is actually 106 12.5/32.
I've been displaying it as 25/64 but 12.5/32 is the proper format.
Actually, the ideal format I'd like to display is 106'12.5

 

[blackbox via OfficeKB.com]

Bob,

0 ?#/32 displays 13/32 instead of 12.5/32

 

[Ron Rosenfeld]

I thought of that but would rather do it in a single cell if possible.
Every day I receive data that isn't in a usable format, I use the MID
function to break it apart.
the data comes in this format 106125. This is actually 106 12.5/32.
I've been displaying it as 25/64 but 12.5/32 is the proper format.
Actually, the ideal format I'd like to display is 106'12.5

You can display 106125 as 106'12.5 by using the custom format:

0'00\.0

However, it will still have a VALUE of 106125

In formulas that rely on the real value, you will have to convert it using the
formula:

=INT(A1/1000)+MOD(A1,1000)/320


--ron

 

[CLR]

Maybe.....

=LEFT(A1,3)&"' "&RIGHT(a1,4)

Vaya con Dios,
Chuck, CABGx3

 

[blackbox via OfficeKB.com]

Ron,
Thanks. I think that will work.

CLR,
I had already tried the LEFT/RIGHT functions.
They work as far a displaying the result but can't be a dependent of another
cell.

 

[blackbox via OfficeKB.com]

Ron,

Got another question for you.

Your formula,=INT(A2/1000)+MOD(A2,1000)/320, returns 106125 as 106.390625.
Is it possible to format that as 106'12.5?

I've been using =MID(A2,1,3)+(MID(A2,4,3)/320)

Is there any advantage to using one formula or the other?

Your number format works great but I still need it broken down for other
functions.

I would like it displayed as 106'12.5 but recognized by excel as 106.390625.

Thanks