VBA Remove hidden quotes in one cell

[rplohocky]

Hello,
I am trying to find a way to use VBA to remove these hidden quotes in front and end of a few words in one cell (B1). Can someone please pass on a way to delete or remove the quotes just in one cell?

Thank you!

 

[My Aswer Is This]

Please let me learn more.
What is a Hidden Quote?

 

[Logit]

.
Well this deletes any quote marks in the string. You'll need to change the cell range to suit:

Option Explicit


Sub Macro1()
On Error Resume Next
    
    Range("D2").Select              '<--- Edit cell in question
    ActiveCell.Replace What:="""", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Find(What:="""", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
End Sub

But I am curious also .. what is a HIDDEN QUOTE ?

 

[Fluff]

When you say Hidden Quotes, do you mean an apostrophe ', rather than quotes "

 

[rplohocky]

Please let me learn more.
What is a Hidden Quote?

Funny guys!!! I really means " " Quotes" or quotation marks. I downloaded a report from a website and found hidden quotes in specific columns of data. You can't see them until you copy them into notepad.
Example: "Inventory Report Template - U.S."

I am using a reference to rename a workbook but excel does not to name files with quotes in them. I'm just trying to insert a snippet of code to remove them.

 

[rplohocky]

Another note about this issue. I tried a find replace but excel can't find any quotation marks on the entire sheet. Also when I click in the cell and then go to the formula bar to try and delete them they are not there. The quotes are only showing up when I copy and paste into note pad. Also I tried other cells and all the others don't have them. Why would this one cell have them in there but no others and why can't see them and delete them?

 

[Logit]

.
Did the macro I posted help ?

Can you post a sample of the code to DROPBOX or similar for download so we can look at it ? I wouldn't know how to recreate "hidden quotes" here to experiment with.

 

[Fluff]

Try copying the quotes from notepad to excel.
Can you see them?
Also run this on the cell you pasted the Quote into

Sub chk()
MsgBox AscW(ActiveCell.Value)
End Sub

what does the message box say

 

[rplohocky]

.
Did the macro I posted help ?

Can you post a sample of the code to DROPBOX or similar for download so we can look at it ? I wouldn't know how to recreate "hidden quotes" here to experiment with.

Hello Logit,
Your didn't seem to work but I think it was because excel didn't show the quotes, they were hidden unlike anything I've seen before. I was able to remove the quotes by using =CLEAN(B1), but that was the only thing that worked! I think your code would work if you could see them though.

Thanks for the help!

 

[rplohocky]

Try copying the quotes from notepad to excel.
Can you see them?
Also run this on the cell you pasted the Quote into

Sub chk()
MsgBox AscW(ActiveCell.Value)
End Sub

what does the message box say

Hello Fluff,
Really wierd! I pasted the statement with the quotes, which I got from the excel report originally, from notepad into a general formatted cell and I could NOT see the quotes! I ran your code and got a 73 response. This is what I pasted:
"Inventory Report Template - U.S.
"

Also the wierd thing is when I pasted this into this post the trailing quote mark created a new line but in notepad it was one line.

I was able to remove these quote marks by using =CLEAN(B1).

Thanks for the help!