Removing apostrophe

Hi Faisal, sorry can we have more info please?

Data from erp? Are you inserting a column next to it and removing the apostrophe'?

I'm just a novice, but I actually think I might be able to help you here.

Originally Posted by faisal.ta

Hi
I have imported data from erp and the dates are carrying apostrophe ' .
Tried the VBA code

Sub foo()
With Range("d:d")
.Formula = .Value
End With
End Sub

and other available thru net. But this is removing the apostrophe of date upto 13th. The rest its not able to remove.
Can anyone help

Try this...

Please Login or Register  to view this content.

Dunno what ERP is but you sure can remove the apostrophe with the substitute command. But as requested if you are doing this from a relative column you will need to code accordingly e.g.

Column E contains D but with apostrophe removed:
Range("E:E").Formula = "=SUBSTITUTE(RC[-1], ""'"", ""/"")"

You'll have to insert Column D and convert that data, copy and paste the values and then delete column D.

Range("E:E").Formula = "=SUBSTITUTE(RC[-1], ""'"", """")"
Range("E:E").Copy
Range("E:E").PasteSpecial xlPasteValues
Range("D:D").Delete

Hello faisal.ta,

Please also see this Thread: http://www.excelforum.com/showthread.php?t=1106411

Regards.