Go Back   TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How to get rid of single quote in Microsoft Excel sheet

MS Office Support


Reply
 
Thread Tools Search this Thread
  #1  
Old 10-02-2012
Member
 
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.

Reply With Quote
  #2  
Old 10-02-2012
Member
 
Join Date: Jun 2011
Posts: 798
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.
Reply With Quote
  #3  
Old 10-02-2012
Member
 
Join Date: May 2011
Posts: 446
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
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to get rid of single quote in Microsoft Excel sheet"
Thread Thread Starter Forum Replies Last Post
Need help to get single quote in every cell of Excel Hache hi MS Office Support 2 23-02-2012 01:17 PM
Not able to edit a lock sheet in Microsoft Excel Tur^turro MS Office Support 3 25-01-2012 08:01 PM
How to recover a deleted sheet in Microsoft Excel 2007 Al Kaholic MS Office Support 2 24-01-2012 07:44 PM
Single quote precision parameter consider as String RogerFielden Technology & Internet 3 24-04-2009 02:05 PM
How to reconstruct the Microsoft Project Task information to Excel sheet by VBA? goldedit Software Development 1 21-04-2009 12:00 AM


All times are GMT +5.5. The time now is 10:36 AM.