Fractions in Text

[DBCox]

Hello everyone,

I hope the title is not too confusing, but I couldn't think of what else to put. I am trying to string together several cells to create a machine nut description. I have a spreadsheet with several columns (thread diameter, pitch, locking/non-locking, etc). I need a cell with a "call out" for that nut. So, column A contains the diameter, B contains the pitch, and C contains the locking/non-locking parameter, I would use the following equation:


=A2&" -"&B2&" "&C2&" Nut"


That works, except I need the diameter column to be represented with a fraction. I can easily make Excel display the fractional equivalent in the column, but when I string them together, it defaults back to a decimal.

I know there has to be an easy solution to this, but I am not knowledgeable enough to find it.

Thanks!

 

[pgc01]

Hi

For ex., if it's the A2 value that you want in fraction format:

=TEXT(A2,"0/0")&" -"&B2&" "&C2&" Nut"

 

[DBCox]

PGC,

Thank you for the suggestion. It almost worked, but 1/16 factional sizes did not work. For instance, 5/16" rounded to 1/3". So, I tried Text(A2,"0/00"), which worked for the 16th intervals, but returned X/04 and x/08 for the 1/4 and 1/8 intervals.

Other than an if statement, any ideas?

Thanks!

 

[pgc01]

Hi

You mean you want to force the denominator to 16?

In that case:

=TEXT(A2,"0/16")&" -"&B2&" "&C2&" Nut"

 

[DBCox]

I need to force the smallest denominator. For instance:

.25 = 1/4
.3125 = 5/16
.375 = 3/8
.5 = 1/2
.75 = 3/4
.875 = 7/8
1 = 1
1.25 = 1 1/4
1.375 = 1 3/8
1.5 = 1 1/2
and so on...

 

[pgc01]

OK.

Try

"0/??"

This allows for 1 or 2 digits in the denominator.

 

[pgc01]

P. S. I noticed you have also mixed fractions, so try instead:

"? ?/??"

 

[DBCox]

Genius! That did it. Thank you for the help!!

 

[Rick Rothstein]

If your decimal values do not equate to exact 16th, you may get odd denominators if you use the suggestion in Message #7. If that is the case for you, then perhaps you can use this UDF (user defined function) instead...

Function MakeFraction(ByVal DecimalNumber As Variant, _
         Optional ByVal LargestDenominator As Long = 64) As String
  Dim GCD As Long
  Dim TopNumber As Long
  Dim Remainder As Long
  Dim WholeNumber As Long
  Dim Numerator As Long
  Dim Denominator As Long
  If IsNumeric(DecimalNumber) Then
    DecimalNumber = CDbl(DecimalNumber)
    WholeNumber = Fix(DecimalNumber)
    Denominator = LargestDenominator
    Numerator = Format(Denominator * _
                         Abs(DecimalNumber - WholeNumber), "0")
    If Numerator Then
      GCD = LargestDenominator
      TopNumber = Numerator
      Do
        Remainder = (GCD Mod TopNumber)
        GCD = TopNumber
        TopNumber = Remainder
      Loop Until Remainder = 0
      Numerator = Numerator \ GCD
      Denominator = Denominator \ GCD
      MakeFraction = Trim(IIf(WholeNumber = 0, "", CStr(WholeNumber)) & " " & _
                     CStr(Numerator) & "/" & CStr(Denominator))
    Else
      MakeFraction = CStr(WholeNumber)
    End If
  Else
    'Input wasn't a number, handle error here
  End If
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. For example,

=MakeFraction(A2,16)&" Nut"

Note that this function works for any denominator you want (you just specify it in the second argument)... it converts the decimal value to the nearest fraction with the that denominator and then reduces it to lowest terms. I have it defaulted to 64 (meaning it converts to the nearest 64th), but the 16 I specified in my above example force the conversion to the nearest 16th.

 

[pgc01]

You're welcome. Thanks for the feedback.