Hi,

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 doesnt have single
quote have valid data which I dont 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 Excel's assistant tagging
along.
What determines the difference the kind of msgbox getting displayed?

Regards,
Hari
India

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 formulae. Repeate and paste special formats. That should do
the trick.

"Hari" wrote:

Hi,

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 doesnt have single
quote have valid data which I dont 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 Excel's assistant tagging
along.
What determines the difference the kind of msgbox getting displayed?

Regards,
Hari
India

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

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Hari wrote:

Hi,

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 doesnt have single
quote have valid data which I dont 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 Excel's assistant tagging
along.
What determines the difference the kind of msgbox getting displayed?

Regards,
Hari
India

--

Dave Peterson