Can I remove prefix apostrophe

[swansonwc]

Hi,

I have a spreadsheet that is exported from a database, and all of the cells
have an apostrophe prefix. The apostrophe keeps hyperlinks from functioning
(since they are now considered text). Here is the code I have created to
remove the apostrophe - unfortunately it removes the first actual character
in the cell as well as the apostrophe:

Public Sub ParseApostrophe1()

Dim Cell As Range

Worksheets("customers").Activate
ActiveCell.CurrentRegion.Select

For Each Cell In Selection
If Len(Cell.Value) >= 1 And Cell.PrefixCharacter = "'" Then
Cell.Value = Right(Cell, Len(Cell) - 1)
End If
Next Cell

End Sub

Does anyone have an idea how I can get rid of the apostrophe without
deleting the first actual (visual) character in the cell.

TIA

Best regards,
Bill

 

[DCSwearingen]

I have not tried to write a procedure to do what you are asking, but I
have used the search & replace from within the spreadsheet to replace
everything matching the search criteria.

I don't know if any of your hyperlinks have an apostrophe in the
address itself. Wouldn't seem likely?

Make a backup copy before your do try this.

 

[Dave Peterson]

I'd replace:

Cell.Value = Right(Cell, Len(Cell) - 1)

with these lines:

with Cell
.numberformat = "@" 'keep it text
.value = .value
end with

 

[swansonwc]

Dave,

That worked perfectly.

Thank you for your help.

Best regards,
Bill