MrExcel.com - Your One Stop for Excel Tips & Solutions

Single quote in cell e.g. '1001 or 'ABCD

Posted by John on February 10, 2002 11:12 PM

I have an Excel worksheet sent to me which contains in each cell a single quote and the cell data, for example '1001 or 'ABCD. You can not see the single quote in the cell because Excel hides the value. By having this single quote in each cell stops the worksheet from sorting correctly. I can't remove the single quote from each cell with search and replace, because Excel ignores it. Is there an easy way to remove the single quote? Thanks

Posted by Coriolanus on February 11, 2002 5:06 AM


Here's one way.
Select the offending cells and run this macro :-

Sub Remove_Apostrophe()
Selection = Selection.Value
End Sub

Posted by Coriolanus on February 11, 2002 6:11 AM


Here's one way.
Select the offending cells and run this macro :-

Sub Remove_Apostrophe()
Selection = Selection.Value
End Sub

Posted by john on February 11, 2002 4:32 PM


:Thanks, the macro did the trick