How to get rid of single quote in Microsoft Excel sheet

[Wisaal]

Sponsored Links

I have many non contiguous cells (anywhere within the range D2 and G200)where I have put only a single quote within the cell and now I want to make all such cell blank. The cells which doesn't have single quote have valid data which I don't want to remove/delete. I chose replace feature in excel 2000 and within Find field indicated a single quote and he Replace field I kept blank. Strangely I get the message "MS excel cant find matching data to replace...." Why is it happening. One more doubt. Sometimes when Excel cant find the matching data then the message which gets displayed comes in the standard message box (similar to the VBA msgbox command) but sometimes I get the message in that yellow msgbox background with the Excels assistant tagging along. What determines the difference the kind of msgbox getting displayed.

[DanielaA]

A single quote in excel is also an indicator that the cell will contain text only. It is used when you want to enter some mathematical expression a text. That is why the 'find' cannot find it! Try pressing ctrl+a, right click > paste special and formula. Repeat and paste special formats. That should do the trick.

[GaganjyotTechie]

If your cells with a single quote is in a column, you could try data|text to columns choose delimited (but uncheck all the delimiters) and finish. If your cells are spread out all over, you could use a macro. Select your range and run this:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no constants!"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
.Value = .Value
End With
Next myCell

End Sub