How to remove hidden tick marks ' from Excel

[mv8167]

This sems like a simple question. But i havent found a solution online that fixes my tick mark issue.

My cell indicates TC_ ABC_123 but when selecting the cell i see 'TC_ABC_123.

When i deleted the ' mark manualy, it returns. Doing a replace ' with "" does not find anything. I tried the following macro with still no luck.

How can i remove the ' tick mark?

Sub NoMoreApostrophes()

lastrow2 = Sheets("TestPlanInfo").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 'find lastrow of sheet2
rng = "N2:N" & lastrow2

With Worksheets("TestPlanInfo").Range(rng) '.UsedRange
.value = .value
End With
End Sub

Thank for any ideas

 

[JoeMo]

Try this:
Copy any blank cell.
Select the cells with tic marks.
Do Pastespecial>Operation and choose the Add button.
Click OK.

 

[mv8167]

Thx JoeMo,

I had tried your idea eawrlier with no luck.

I ended up going Home->Editing->Clear->Clear Formats

This worked finally

 

[marious]

It vexes me too. Use Notepad "it will display all hidden characters" and REPLACE will do the job for '.