Macro to remove apostrophe during data import

[zeppelinmike]

Excel 2003
Hello all, my Excel skills are limited and would appreciate your help. I have a spreadsheet which gets its data imported from another program. The problem is each cell has a apostrophe (') automatically added in front of it in each of the cells. This prevents problems when I'm carrying out a formula.

Any suggestions to automatically remove this during each import.

Many thanks for your support!

 

[CWatts]

You can try this:

Sub ReplaceApos()
    Cells.Replace What:="'", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Easiest way I find to set up a find/replace is to use the macro recorder, do your find/replace and then just snag the code (as I did here.)

 

[markmzz]

If I understand correctly what you want, so this code can help you:

Important: do a test with a copy of your workbook.

Note: Sheet1 is the sheet that have the data imported.

Sub ApostOut()
    Sheets("Sheet1").UsedRange.Value = Sheets("Sheet1").UsedRange.Value
End Sub

And tell us if it work.

Markmzz

 

[Datsmart]

markmzz,
Your code works in my test sheet, except it will change all cells with formulas to the formula value. That can be fixed by only working with Constants like this:

Sub ClearApostrophe()
Set Rng = Range("A1").CurrentRegion
    For Each c In Rng.SpecialCells(xlCellTypeConstants, 2)
        c.Value = c.Value
    Next c
End Sub

 

[markmzz]

markmzz,
Your code works in my test sheet, except it will change all cells with formulas to the formula value. That can be fixed by only working with Constants like this:

Sub ClearApostrophe()
Set Rng = Range("A1").CurrentRegion
    For Each c In Rng.SpecialCells(xlCellTypeConstants, 2)
        c.Value = c.Value
    Next c
End Sub

Datsmart,

Thank you for your note.

Markmz

 

[zeppelinmike]

Thanks very much guys! - works like a treat!