Removing quotation marks around values

[ggounder]

I have a worksheet containing cells with values enclosed in quotation marks (see attached image). I was wondering how to remove the quotation marks and simply leave the actual value in the cell. e.g. In the image: in cell B2 i have the value "D1", but i want to remove the quotes and just have the value D1 - no quote marks. Ideally I would like to apply this quote mark removal to the entire worksheet.

 

[VoG]

Select the entire data (click in B1 and Press CTRL + A), Edit > Replace, in the What box enter "", leave the other box blank and ckick OK.

 

[ggounder]

Select the entire data (click in B1 and Press CTRL + A), Edit > Replace, in the What box enter "", leave the other box blank and ckick OK.

Hi VoG Thanks for your quick reply. I tried your suggestion (I'm using Office 2007, so I clicked the find&select icon in the home ribbon then selected replace).

I put "" in the Find what box, then left the replace with box blank. When I hit Replace All i got a message saying Excel couldn't find any data to replace.

Is the process different for Excel 2007? Do I need to use wildcards of some sort? I tried to put "*" in the find what box and left the replace with box blank, but nothing happened to any of the cells with quotes.

 

[VoG]

Your data seems pretty innocuous so can you upload it to a site like www.box.net. It may be specially formatted to display the "" but cannot check without seeing the file.

 

[henrik2h]

You should only replace " not "". You want to find all " in the document and replace with a blank instead.

 

[ggounder]

You should only replace " not "". You want to find all " in the document and replace with a blank instead.

Hi henrik2h, I tried putting just one quote mark and unchecked the box for match entire cell contents. I also left the replace with box blank (did not even put a space in it) It found all instances of the quotes, but it left the cell untouched and the quotes remained. I then put a space in the replace with box and sure enough, it removed the quote marks, but obviously left a space infront of and after the data in the cell, which is not ideal.

I should also mention that this data has been imported from a text file, using VBA code. All the data in text file contains quote marks around it too. i used the following line of VBA code to capture the data in the text files:

temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(myDir & "\" & fn).ReadAll

As per VoG's suggestion I am currently trying to upload the excel file (saved in 2003 format) to box.net, will post link to file shortly.

 

[ggounder]

Here is a link to the file I am working with (excel 2003 format).

http://www.box.net/shared/0zgvq8cm14

 

[shg]

Replacing " (that's one double quotation mark) with nothing worked fine for me.

 

[VoG]

Home > Find and replace then following my earlier destructions worked: Replace "with nothing

 

[ggounder]

Replacing " (that's one double quotation mark) with nothing worked fine for me.

Hi shg4421 - What did you put in the replace with box? A space? or did you leave it completely blank?