CHAR(10)&CHAR(13), and double quotes?

[apia]

Hello Board members,

I am trying to get text in a single cell to be broken into several paragraphs when pasted into a word processor, using CHAR(10) and CHAR(13).

Right now I am tinkering with the following kind of formula:

="blah blah "&CHAR(13)&CHAR(10)&"more blah blah "&CHAR(13)&CHAR(10)&"even more blah blah "

And when I paste the result into notepad, rather than getting

blah blah
more blah blah
even more blah blah

I get

"blah blah
more blah blah
even more blah blah "

i.e. the first and last double quote are preserved, for some reason.

Can anyone suggest a workaround (possibly within the formula, and not involving VBA) that would eliminate those quotes in the pasted text?

Thanks!

 

[Richard Schollar]

Hi & Welcome to the Board!

You could work around this by copying and pasting values into the formula cell and then, rather than copying the entire cell, whilst the cell is selected, hit F2 and select all the text within the cell in the formula bar and then do a Ctrl+C to copy and then paste this into Notepad.

If you don't want to go such a long-winded route, there would be code solutions which you could create to do a special copy from the cell which would avoid the double quotes. Post back if interested.

 

[Andy B]

The formula you suggested gives a line feed and carriage return between each row meaning the rows are spaced as follows:

blah blah

more blah blah

even more blah blah

If you just want it without the spacing as follows you only need CHAR(10):

blah blah
more blah blah
even more blah blah

If you copy and paste the cell into Word and save it as a Plain Text (*.txt) file it will open in notepad correctly.

 

[apia]

Hello Richard and hello Andy. Thanks for your quick replies!

The place where I ultimately need to put the data in is a web form. Going through Word would be an option, but it would mean one extra step & a lot of time spent manually doing a repetitive task. If that could be avoided, it would be better.

Doing copy-paste values in Excel & then pasting the cell contents is also a pretty long workaround. Just deleting the double quotes manually seems quicker (although still a big waste of time).

Richard, if you have an easy code alternative that would fit the bill, I would indeed be interested.

Thanks & regards,

apia

 

[Anthony47]

If the generated string has to go to a web page maybe you can use the formula

="blah blah "&"< br >"&"more blah blah "&"< br >&"even more blah blah "

(remove the spaces before and after "br", ie after "<" and before ">")

Bye.

 

[Richard Schollar]

apia

In the workbook where you want this to apply (or in your Personal.xls if you want to use across several workbooks), place the following code in a standard module:

Sub CopyCellContents()
'create a reference in the VBE to Microsft Forms 2.0 Lib
Dim objData As New DataObject
Dim strTemp As String
strTemp = ActiveCell.Value
objData.SetText (strTemp)
objData.PutInClipboard
End Sub

To add a standard module to your project (workbook), open up the VBE with Alt+F11 and then right-click on your workbook in the top left Project Window and select Insert>Module. Paste the code into the code module window which will open on the right.

Back in Excel, go Tools>Macro>Macros and select the macro called "CopyCellContents" and then choose Options from the dialog. Here you can assign the macro to a shortcut key (eg like Ctrl+c for normal copy) - I used Ctrl+q.

Then, when you want to copy a single cell over to Notepad/wherever, just do Ctrl+q (or whatever you chose) and then do a Ctrl+v or Edit>Paste in your chosen destination.

 

[jemery]

Why does excel do this? We use a lot of spreadsheets for generating text to be used for configuration files. We always have to remove all these extra double quotation marks. The strange thing is if the text is on a single line, then the quotes are not added, but if it is multiple lines then you get quotes around the contents of each cell.

Considering we are merging and combining the exact text we need in the cell it would be nice if Excel would leave it alone and not add double quotes. If we wanted to the quotes we would add them. This seems like some kind of bug.

Jason

 

[mepanousis]

apia

In the workbook where you want this to apply (or in your Personal.xls if you want to use across several workbooks), place the following code in a standard module:

Sub CopyCellContents()
'create a reference in the VBE to Microsft Forms 2.0 Lib
Dim objData As New DataObject
Dim strTemp As String
strTemp = ActiveCell.Value
objData.SetText (strTemp)
objData.PutInClipboard
End Sub

To add a standard module to your project (workbook), open up the VBE with Alt+F11 and then right-click on your workbook in the top left Project Window and select Insert>Module. Paste the code into the code module window which will open on the right.

Back in Excel, go Tools>Macro>Macros and select the macro called "CopyCellContents" and then choose Options from the dialog. Here you can assign the macro to a shortcut key (eg like Ctrl+c for normal copy) - I used Ctrl+q.

Then, when you want to copy a single cell over to Notepad/wherever, just do Ctrl+q (or whatever you chose) and then do a Ctrl+v or Edit>Paste in your chosen destination.

This seems to be by far the best solution to this problem but when i try it i am getting the fowling error:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Compile error:<o></o>
User defined type not defined<o></o>
<o></o>
Can you tell me what am i doing wrong here?<o></o>

 

[Richard Schollar]

Hi

Did you follow the top instruction in the code?

'create a reference in the VBE to Microsft Forms 2.0 Lib

 

[mepanousis]

Hi

Did you follow the top instruction in the code?

Thank you very much for your reply. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I didn't understand what i had to do with the top instruction but finally i did create the reference so now when i am using the macro i can copy a cell without seen any double quotes.<o></o>
Still there is one last problem in my worksheet to solve. What i want to do is this. <o></o>
I have a text in a cell (which is actually telnet commands - so each row IN the cell is a command with a hidden "ender" character in the end) and i auto replace certain words from this text with values from certain cells. I take the result in a third cell.<o></o>
With this excellent example of yours i got rid finally the double quotes but i have to make the copied by the formula text appeared line under line.<o></o>
<o></o>
ex. <o></o>
<o></o>
In the cell i have this<o></o>
command no1<o></o>
command no2<o></o>
command no3<o></o>
etc<o></o>
<o></o>
when i run the formula the result is this command no1command no2command no3 (at least with no quotes but still i cannot use it)<o></o>
<o></o>
what i was doing so far was an extra formula that copies the result to another cell (so I have just text in this cell and no formulas) and then i had to double click in this cell and copy the text by drugging the mouse. This small excel program is a main part of my daily work so any less click would save me a lot of time.<o></o>
So I am wondering if there is anything I can do to keep the text format by just copy the whole cell with control+q (your macro)<o></o>
Still your double quotes solution was very practical.<o></o>
Thank you in advanced.<o></o>