How do I get rid of tick marks?

[Galena1]

I have a worksheet with a column of data that is in this format:

'12345

Whenever I operate on it formulaically it retains the tick mark. It cannot be cell formatted out.

I don't want to co thru the hassle of saving it as .txt file and then re-importing into my Excel workbook, making sure to delimit it and import as text, and finally copy paste special values over existing data, although we all know this method does work.

Isn't there a cleaner or easier way to deal with this cleansing?

btw, this is a text value string, not a date integer.

 

[Datsmart]

Sub ApostroRemove()
    For Each currentcell In Selection
        currentcell.Value = currentcell.Value
    Next
End Sub

Select range of cells and run macro code.

 

[Galena1]

My bad John. Your macro works fine. It does remove the tick (apostrophe) nicely. I can still see the 'tick' if I mouse over the cell, which of course signifies it is still text string. But, I just looked at my data and I actaully needed to converted to a value (1234), NOT text ('1234). Again, my bad. Sorry.

 

[shg]

Sub x()
    With Intersect(ActiveSheet.UsedRange, Selection.Areas(1))
        .Value = .Formula
    End With
End Sub

 

[Galena1]

'x' macro seemed to work at first. Now stopped working. It too still converts tick maked text to label '1234. Need it to convert to integer (value) 1234...

 

[shg]

The selection needs to cover the cells of interest.

 

[shg]

Also, the cells of interest must be formatted as something other than Text.