I have cells with the following formula in them
=J19&" x "&K19&""&" x "&L19&"" and the cells display this 0.25 x 19.0625 x 30.25
I cant figure out how to set up the custom formatting to display 3/4 x 19-1/16 x 30-1/4
The first set of fractions will always be 1/16,1/4,1/2 or 3/4
The 2nd and 3rd set of fractions will always have a number first then the fraction or just a number.
Thanks in Advance
c9: =TEXT(B9,"###/##") where b9 is .25
b10: 19.0625 so c10 =TRUNC(B10)&" "&TEXT(B10-TRUNC(B10),"###/##")
Thank for your reply rcm
Im an excel amateur I dont really understand what I need to to with what you provided. Could please explain?
To format 0.25 as 1/4 you have to go to Format Cells and select Fraction, As quarters.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
I know that, but I also have text in the same cell as show in the formula in my first post. It does not work if I simply use the fraction format.Originally Posted by AlKey
You can't have it with custom formatting to display 3/4 x 19-1/16 x 30-1/4
Could I have (3/4) (19-1/16) (30-1/4)
If yes, how would I do that?
Thanks
try this and look at line 19
I introduced the dashes (-)
Please Login or Register to view this content.
Try this
=TEXT(MOD(J19,1),"#/#")&" x "&TEXT(INT(K19),"#-")&TEXT(MOD(K19,1),"#/##")&" x "&TEXT(INT(L19),"#-")&TEXT(MOD(L19,1),"#/##")
Thanks Alkey your formula is exactly what I needed.
Thank You rcm yours was real close and I could have made it work.
Thanks to you both!! Rock Stars!!
you are welcome and happy holidays!!!
You're welcome and thank you for your feedback!
Upon further review when I have a whole number with a fraction the formula works great.
When I have just the whole number alone its displays like this 3-0/1
Is it possible to make it like 3/4 x 3 x 18-1/2
Try this adjusted formula
=TEXT(MOD(J19,1),"#/#")&" x "&IF(MOD(K19,1)=0,K19,TEXT(INT(K19),"#-")&TEXT(MOD(K19,1),"#/##"))&" x "&TEXT(INT(L19),"#-")&TEXT(MOD(L19,1),"#/##")
and just in case other cell have whole numbers:
=IF(MOD(J19,1)=0,J19,TEXT(MOD(J19,1),"#/#"))&" x "&IF(MOD(K19,1)=0,K19,TEXT(INT(K19),"#-")&TEXT(MOD(K19,1),"#/##"))&" x "&IF(MOD(L19,1)=0,L19,TEXT(INT(L19),"#-")&TEXT(MOD(L19,1),"#/##"))
Last edited by AlKey; 12-27-2013 at 09:01 PM.
Im going with the 2nd formula it works perfect in all cells. Many Thanks!!!
You're welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks