Creating a VBA macro that changes specific string to a number

[zeekiehafa]

Hello all,

I am having some difficulty with a pdf that I converted to an excel document because I wanted to use the data from the pdf tables in a different program I am currently working on. However, the data is in the improper format. For example, in the table it reads 2-1/8 as string and I want it to be the number value 2.125 . Likewise if the value in the table reads 5-1/4 I want it to automatically convert it to something that will be read as the number 5.25

I've been scratching my head over this for quite some time and I would appreciate it if anyone has some helpful comments.

P.S. If you figure out a code for this I would appreciate a careful description of how it works. Thank you.

 

[shg]

How about ...

Select the column and replace the hyphen with a space.

Then do Data > Text to columns, Finish.

 

[zeekiehafa]

Actually I was hoping for a solution a little bit more automatic than that. I know how to write a macro to go through tables and search for the values that need changing, but I want the macro to search and change as it goes.

 

[shg]

Well then, maybe

Sub x()
    Dim cell As Range
    Dim v As Variant
    
    For Each cell In Intersect(Selection.Cells, ActiveSheet.UsedRange)
        If VarType(cell.Value) = vbString Then
            v = Evaluate(Replace(cell.Value, "-", " "))
            If VarType(v) = vbDouble Then cell.Value = v
        End If
    Next cell
End Sub

 

[zeekiehafa]

Thanks, that appears to work. Would you care to explain what the code is doing? Thanks again.

-zeek