Results 1 to 3 of 3

Thread: How to get rid of single quote in Microsoft Excel sheet

  1. #1
    Join Date
    Nov 2011
    Posts
    123

    How to get rid of single quote in Microsoft Excel sheet

    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.

  2. #2
    Join Date
    Jun 2011
    Posts
    804

    Re: How to get rid of single quote in Microsoft Excel sheet

    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.

  3. #3
    Join Date
    May 2011
    Posts
    449

    Re: How to get rid of single quote in Microsoft Excel sheet

    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:
    Code:
    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

Similar Threads

  1. Need help to get single quote in every cell of Excel
    By Hache hi in forum MS Office Support
    Replies: 2
    Last Post: 23-02-2012, 12:17 PM
  2. Not able to edit a lock sheet in Microsoft Excel
    By Tur^turro in forum MS Office Support
    Replies: 3
    Last Post: 25-01-2012, 07:01 PM
  3. How to recover a deleted sheet in Microsoft Excel 2007
    By Al Kaholic in forum MS Office Support
    Replies: 2
    Last Post: 24-01-2012, 06:44 PM
  4. Single quote precision parameter consider as String
    By RogerFielden in forum Technology & Internet
    Replies: 3
    Last Post: 24-04-2009, 01:05 PM
  5. Replies: 1
    Last Post: 20-04-2009, 11:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •