Pasting text with double quotes

I'm trying to paste text that contains double quotes into Excel.

Example:

"Bob Anderson" 36475tmmn8766

Excel is stripping out the double-quotes upon pasting. I've look
everywhere for an option or setting to override this but have been
unsuccessful.

Does anyone have any ideas?

Thanks


- Bob A.

Some additional info...the stripping only occurs when a double-quote
is the first character in the string. If there is more than one quote
it will strip out the first two it encounters.


On Thu, 31 Mar 2005 18:30:00 -0800, Bob Anderson
<banderson1962@yahoo.com> wrote:

>I'm trying to paste text that contains double quotes into Excel.
>
>Example:
>
>"Bob Anderson" 36475tmmn8766
>
>Excel is stripping out the double-quotes upon pasting. I've look
>everywhere for an option or setting to override this but have been
>unsuccessful.
>
>Does anyone have any ideas?
>
>Thanks
>
>
>- Bob A.

Try PasteSpecial > As: > HTML

Vaya con Dios,
Chuck, CABGx3


"Bob Anderson" <banderson1962@yahoo.com> wrote in message
news:a9cp41pejhi86q5hklmghppjm3ooi7jk01@4ax.com...
> I'm trying to paste text that contains double quotes into Excel.
>
> Example:
>
> "Bob Anderson" 36475tmmn8766
>
> Excel is stripping out the double-quotes upon pasting. I've look
> everywhere for an option or setting to override this but have been
> unsuccessful.
>
> Does anyone have any ideas?
>
> Thanks
>
>
> - Bob A.

'Paste as HTML' is not an available option, which makes sense since I
originally copied unformatted text.


- Bob A.



On Thu, 31 Mar 2005 22:01:13 -0500, "CLR" <croberts@tampabay.rr.com>
wrote:

>Try PasteSpecial > As: > HTML
>
>Vaya con Dios,
>Chuck, CABGx3
>
>
>"Bob Anderson" <banderson1962@yahoo.com> wrote in message
>news:a9cp41pejhi86q5hklmghppjm3ooi7jk01@4ax.com...
>> I'm trying to paste text that contains double quotes into Excel.
>>
>> Example:
>>
>> "Bob Anderson" 36475tmmn8766
>>
>> Excel is stripping out the double-quotes upon pasting. I've look
>> everywhere for an option or setting to override this but have been
>> unsuccessful.
>>
>> Does anyone have any ideas?
>>
>> Thanks
>>
>>
>> - Bob A.
>

If you're doing them one at a time, you could paste into the formula bar.



Bob Anderson wrote:
>
> I'm trying to paste text that contains double quotes into Excel.
>
> Example:
>
> "Bob Anderson" 36475tmmn8766
>
> Excel is stripping out the double-quotes upon pasting. I've look
> everywhere for an option or setting to override this but have been
> unsuccessful.
>
> Does anyone have any ideas?
>
> Thanks
>
> - Bob A.

--

Dave Peterson

Unfortunately I have thousands of line to copy from SQL Server Query
Analyzer...there are a couple of ways I can work around it, but it
seems to me there should be a setting in Excel that will solve the
problem. I just haven't been able to find it.





On Fri, 01 Apr 2005 00:01:10 -0600, Dave Peterson
<ec35720@netscapeXSPAM.com> wrote:

>If you're doing them one at a time, you could paste into the formula bar.
>
>
>
>Bob Anderson wrote:
>>
>> I'm trying to paste text that contains double quotes into Excel.
>>
>> Example:
>>
>> "Bob Anderson" 36475tmmn8766
>>
>> Excel is stripping out the double-quotes upon pasting. I've look
>> everywhere for an option or setting to override this but have been
>> unsuccessful.
>>
>> Does anyone have any ideas?
>>
>> Thanks
>>
>> - Bob A.

How about a workaround. Convert the double quotes to some character(s) that you
don't use ($$$$), then paste, then edit|replace to revert back.

Paste into notepad first, then do the change, then copy|paste into excel and fix
that change.



Bob Anderson wrote:
>
> Unfortunately I have thousands of line to copy from SQL Server Query
> Analyzer...there are a couple of ways I can work around it, but it
> seems to me there should be a setting in Excel that will solve the
> problem. I just haven't been able to find it.
>
> On Fri, 01 Apr 2005 00:01:10 -0600, Dave Peterson
> <ec35720@netscapeXSPAM.com> wrote:
>
> >If you're doing them one at a time, you could paste into the formula bar.
> >
> >
> >
> >Bob Anderson wrote:
> >>
> >> I'm trying to paste text that contains double quotes into Excel.
> >>
> >> Example:
> >>
> >> "Bob Anderson" 36475tmmn8766
> >>
> >> Excel is stripping out the double-quotes upon pasting. I've look
> >> everywhere for an option or setting to override this but have been
> >> unsuccessful.
> >>
> >> Does anyone have any ideas?
> >>
> >> Thanks
> >>
> >> - Bob A.

--

Dave Peterson

Hi Bob,

I hope that I have the answer to your problem with using quotation marks. When you put quotation marks around text, that confirms to Excel that you are entering a literal string. You can solve this, at least in Excel 2003, by adding one single quote first:

' "Bob Anderson" 36475tmmn8766

Have a great day!

~Diane


> >Bob Anderson wrote:
> >>
> >> I'm trying to paste text that contains double quotes into Excel.
> >>
> >> Example:
> >>
> >> "Bob Anderson" 36475tmmn8766
> >>
> >> Excel is stripping out the double-quotes upon pasting. I've look
> >> everywhere for an option or setting to override this but have been
> >> unsuccessful.
> >>
> >> Does anyone have any ideas?
> >>
> >> Thanks
> >>
> >> - Bob A.

Hi,

This occurs due to Text To Columns -settings.
Change TextQualifier to be xlNone and problem is solved.. Unfortunately, his has to be done every time when you open the Excel.

Selection.TextToColumns Destination:=Range("E4"), DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

Do it like this and Excel changes the settings every time you open the Excel:

Private Sub Workbook_Open()
Dim ggg As Range

Set ggg = Cells.Find(What:="*", After:=Range("a1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If ggg Is Nothing Then
Set ggg = Range("a1")
ggg.Value = "x"
ggg.TextToColumns Destination:=ggg, DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
ggg.Value = ""

Else
ggg.TextToColumns Destination:=ggg, DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
End If

End Sub

Hi Bob,

All you need to do is to add a space (yes, only one space) right before the first double quotes on the first row (if you have hundred of rows like mine tonight) of your .txt/.doc file. After that, CTRL+A, CTRL+C, open your excel file, and CTRL+V.

Tonight, 13 years after you posted this, I found the trick from Pamela Schmidt at microsoft answer

Try this:

Paste with control V.
After you've pasted with control v, use the little box that pops up and click it's drop down arrow.
Now, you will see the option for text import wizard. This is different from "paste special."

Using the text import wizard, on the second screen, change the text qualifier from " to none.

Hi

In case someone gets here...

I had the same Issue. I'm building a string from a formula involving other columns (pasted from a text source).

The problem was that in some of the pasted text there is a TAB character, affecting the result text from formula,
and apparently that lead excel (or the destination editor) to add the double quotes to render the copied text.

The solution for me was simple. Add a SUBSTITUTE function to the formula, changing the CHAR(9) to empty String. Something like:

=SUBSTITUTE(E8,CHAR(9),"")

Ps. In my case was the TAB character, but i guess it could be some other non-visible character. I found it on a text editor that is able to show special characters (notepad ++).