Changing Fractions to Decimals

[chini2105]

Hello,

I have been trying to change fractions to decimals in one click without adding a colum.
Just automatically change the values.
Is this has to be with VBA?

I have an exported file from solidworks and it shows the values as 3/16" , 3/8" , 40 1/2", 55 1/2"

First I look for the " to delete it, for 40 1/2" and 55 1/2" I don't have a problema. It automatically changes to decimal but, for 3/16" & 3/8" changes to a date.

Could you please help me?

Thank you in advance

 

[Rick Rothstein]

I think you will need a macro to do what you want. Here I have assumed your fractions are in Column A starting at Row 1 (change to match your actual layout)...

Sub MakeInchesFractionsToDecimalNumber()
  Dim R As Long, Data As Variant
  With Range("[B][COLOR="#FF0000"]A1[/COLOR][/B]", Cells(Rows.Count, "[B][COLOR="#FF0000"]A[/COLOR][/B]").End(xlUp))
    Data = .Value
    For R = 1 To UBound(Data)
      Data(R, 1) = Evaluate(Replace(Data(R, 1), """", ""))
    Next
    .Cells = Data
  End With
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, 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. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (MakeInchesFractionsToDecimalNumber) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[chini2105]

Thank you very much Rick!

I tried the code but is not working for the 1/8". It changes to date.

 

[Rick Rothstein]

Thank you very much Rick!

I tried the code but is not working for the 1/8". It changes to date.

It does not do that for me. What exactly do you have in the cell and what date does it convert it to?

Also, what is the cell format before and after the conversion?

 

[chini2105]

I am so sorry, I tried it again and it worked.
Thank you very much

Is there a way that for a selecte range of cells do the same?

 

[Rick Rothstein]

Is there a way that for a selecte range of cells do the same?

What do you mean by "a select range of cells"... one's the user selects manually or a fixed range of cells? If you mean user selected, then change this line of code...

With Range("A1", Cells(Rows.Count, "A").End(xlUp))

to this...

With Selection

Otherwise, if you mean a fixed range of cells, simply specify the address for that range between the outer parentheses. For example, if the range were C5:H12, then you would change that line of code to this...

With Range("C5:H12")

 

[chini2105]

Thanks!

I used "With Selection" but only the first that I selected from the table change to there correct decimal representation.
The other ones also change but to another values.

I have the following table:

| F | G | H | I | J | K | l | M | N | O | P | Q | R | S | T | U | V | W | X |
| | A572-65 | | | | | | | | | | | | | | | | | |
|3/16" | A572-65 | | |40 1/2" | | | 1 | 5/8" | | | | | | | 20 | | | |
|3/8" | A36 | | |39 1/2" | 2" | | | | | | 1 | 5/8" | | | | | | |
|5/8" | A572-65 | | |47 1/8" | 9" | | | | | | 4 | 1 1/8" | | | | | | |
|5/8" | A572-65 | | |51 1/8" | 9" | | | | | | 4 | 1 1/8" | | | | | | |
|5/8" | A572-65 | | |55 1/8" | 9" | | | | | | 4 | 1 1/8" | | | | | | |
|1/2" | A572-65 | | |26" | 23 7/16" | | | | | | | | | | 1 | 16 13/16" | 8 3/8" | S |
|6SCH40 | A53 GR. B | | | | | | | | | 24" | | | | | | | | |
|3/8" | A572-65 | | |5" 3" | | | | | | | 1 | 5/8" | | | | | | |
|3/8" | A572-65 | | |55 15/16" | 9" | | | | | | 3 | 7/8" | | | | | | |
|3/8" | A572-65 | | | |12" | 18"| 2 | 6" | 2 | 5/8" | | | | | | | | |
|1 3/4" | A572-55 | | |55 3/4" | | | 28 | 1 1/8" | | | 1 | 3" |46 1/8"| S |20 | | | |
| | 304 | | |3/4" | 16 5/16" | | | | | | | | | | | | | |
|3/4" | A572-65 | | |47 5/16" | 9" | | | | | | 2 | 1 1/8" | | | | | | |
|3/4" | A572-65 | | |51 5/16" | 9" | | | | | | 2 | 1 1/8" | | | | | | |
|3/4" | A572-65 | | |47 5/16" | 17" | | | | | | 2 | 1 1/8" | | | | | | |
|3/4" | A572-65 | | |51 5/16" | 17" | | | | | | 2 | 1 1/8" | | | | | | |
|3/4" | A572-65 | | |47 5/16" | 17" | | | | | | 2 | 1 1/8" | | | | | | |
|3/4" | A572-65 | | |51 5/16" | 17" | | | | | | 2 | 1 1/8" | | | | | | |
| | A563-DH | | | | | | | | | | | | | | | | | |
| | A36 | | | | | | | | | | | | | | | | | |
| | 304 | | | | | | | | | | | | | | | | | |



I want the user to be able to select a range from cell F2 to column X because the cell can vary and only the values that have the inches unit (3/16", 3/4", ... , 7/8", 55 15/16", 26") change to decimals

 

[chini2105]

Here is an image of the table

 

[Rick Rothstein]

Thanks!

I used "With Selection" but only the first that I selected from the table change to there correct decimal representation.
The other ones also change but to another values.

I want the user to be able to select a range from cell F2 to column X because the cell can vary and only the values that have the inches unit (3/16", 3/4", ... , 7/8", 55 15/16", 26") change to decimals

Okay, that is definitely not the layout I imagined you had. Assuming there are no formulas, give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub MakeInchesFractionsToDecimalNumber()
  Dim R As Long, Data As Variant
  With ActiveSheet.UsedRange
    Data = .Value
    For R = 1 To UBound(Data)
      If Right(Data(R, 1), 1) = """" Then Data(R, 1) = Evaluate(Replace(Data(R, 1), """", ""))
    Next
    .Cells = Data
  End With
End Sub[/td]
[/tr]
[/table]