Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 |
#2
![]() |
|||
|
|||
![]()
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 |
#3
![]() |
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How do I change the Excel 2000 sheet tab size? It's too small. | Excel Discussion (Misc queries) | |||
Hyperlink to specific sheet in Excel Web File | Links and Linking in Excel | |||
Excel user desires to learn ABC of Access | Excel Discussion (Misc queries) | |||
How to convert the table in word to excel sheet? | Excel Discussion (Misc queries) | |||
balance sheet template in excel adding lines | Excel Discussion (Misc queries) |