Working with Inches/fractions

[borolo222]

Hi, I have this program that reads a number of cells which have some measurements in inches with the following cell format # ??/??

so i get te following results (all numbers rounded to 1/16):

58
58 1/4
3 1/16 etc
13 3/8


when the macro reads the cell it reads it as decimal.

58
58.25
3.0625
13.375

So, the output of the program has to present me each part of the fraction in a diferent column like so

col1 col2 col3
58
58 1 4
3 1 16
13 3 8


can anyone help me with this macro?
thanks in advance.

 

[VoG]

Try

Sub test()
Dim c As Range, s As String, LR As Long, X
LR = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Range("A1:A" & LR)
    With c
        s = WorksheetFunction.Text(.Value, "# ??/??")
        X = Split(Replace(s, "  ", "/"), "/")
        With .Offset(, 1).Resize(, UBound(X) + 1)
            .NumberFormat = "General"
            .Value = X
            .Value = .Value
        End With
    End With
Next c
End Sub

 

[borolo222]

Worked like a charm. Excelent.

Thankyou very much.

 

[borolo222]

VoG
I ran into trouble when i have a value like 3 15/16. Since it doesn't have the two spaces after the first digits it doesn't replace it with the /.

Try

Sub test()
Dim c As Range, s As String, LR As Long, X
LR = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Range("A1:A" & LR)
    With c
        s = WorksheetFunction.Text(.Value, "# ??/??")
        X = Split(Replace(s, "  ", "/"), "/")
        With .Offset(, 1).Resize(, UBound(X) + 1)
            .NumberFormat = "General"
            .Value = X
            .Value = .Value
        End With
    End With
Next c
End Sub

 

[VoG]

Try this

Sub test()
Dim c As Range, s As String, LR As Long, X
LR = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Range("A1:A" & LR)
    With c
        s = WorksheetFunction.Trim(WorksheetFunction.Text(.Value, "# ??/??"))
        X = Split(Replace(s, " ", "/"), "/")
        With .Offset(, 1).Resize(, UBound(X) + 1)
            .NumberFormat = "General"
            .Value = X
            .Value = .Value
        End With
    End With
Next c
End Sub

 

[borolo222]

Thanks Peter. that solved the issue. I made a mess trying to fix it by myself, jajaja.

Thanks.

Try this

Sub test()
Dim c As Range, s As String, LR As Long, X
LR = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Range("A1:A" & LR)
    With c
        s = WorksheetFunction.Trim(WorksheetFunction.Text(.Value, "# ??/??"))
        X = Split(Replace(s, " ", "/"), "/")
        With .Offset(, 1).Resize(, UBound(X) + 1)
            .NumberFormat = "General"
            .Value = X
            .Value = .Value
        End With
    End With
Next c
End Sub