PDA

View Full Version : [SOLVED] Displaying numbers in fractions, not decimal (when concatenating)



jaydee
06-15-2016, 05:09 PM
Hi guys,

Is there a way to show fractions in a cell, even when concatenating numbers?
When I type "92 1/4", it converts it to 92.25, which displays correctly with the fractions format, but when I concatenate the values with length x width x height it comes up wrong.

I.E. if I enter "2X4X92 1/4" in three separate fields, it will show up as 2X4X92.25.

I work in the lumber industry, so it's not acceptable to show "92.25" to mean "92 1/4". The only way I have found to get around this is by using the ' sign, but it's an added step and I have to change my formula cell linking to LxWxH and manually update it for some entries where I only have 1 dimension.

I attached a sample for reference. Thank you.

Paul_Hossler
06-15-2016, 05:43 PM
I'd use a User Defined Function to handle the formatting

Using .Text is not the same as .Value since .Text used cell's data as displayed, such as fraction number formatting, etc.




Option Explicit
Function LWH(L As Range, Optional W As Range = Nothing, Optional H As Range = Nothing) As Variant
Dim S As String

If Not L Is Nothing Then If Len(Trim(L.Text)) > 0 Then If L > 0 Then S = Trim(L.Text)
If Not W Is Nothing Then If Len(Trim(W.Text)) > 0 Then If W > 0 Then S = S & "X" & Trim(W.Text)
If Not H Is Nothing Then If Len(Trim(H.Text)) > 0 Then If H > 0 Then S = S & "X" & Trim(H.Text)

LWH = S
End Function



So if you look at the Red columns, I marked the inputs and the output using my function

SamT
06-15-2016, 05:46 PM
Select the Cells
Right Click the Selection
Click Format cells
Chose Numbers
Choose Fraction
Select the style of fractions you want. Note: "Up to One Digit" will display all one digit denominators. (n/2, to n/9)

If you select "Quarters" or other discrete denominator, it will display the decimal number rounded to the nearest Quarter (.4 = 2/4)

Whatever you choose, any math will use the actual decimal numbers. For example: Format as Quarters; enter 0.12 in four cells. They will display 0. Sum them and the result will be displayed as 2/4, (0.48)

I would use 8ths, but I was used to hearing measurements always called in 8ths, ie, "Sixtytwo and four," "Thirtysix and no."

SamT
Carpenter, Local 547

Aflatoon
06-16-2016, 01:03 AM
You could use:
=TRIM(TEXT(F6,"# ??/??")&" X "&TEXT(G6,"# ??/??")&" X "&TEXT(H6,"# ??/??"))

snb
06-16-2016, 01:06 AM
In J79:

=TEXT(F79;"# ??/??")&TEXT(G79;" X # ??/??")&TEXT(H79;" X # ??/??")

jaydee
06-16-2016, 05:32 PM
Thanks everyone! I'm saved from doing a lot of things manually!

Paul - Yours was really helpful because occasionally I only have 1 dimension and the UDF always displays the data correctly when I pivot it.

Thank you so much for your help guys. Have a wonderful evening.

Paul_Hossler
06-17-2016, 06:25 AM
Because it uses .Text, the Number formatting will be picked up (e.g. "6 3/4"), BUT if the column is too narrow, you'll also get the ######## fills, so just something to be aware of