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]

So can I just clarify: you have your multi-line cell and then you use the little bit of code I provided to copy it to another cell? Or is there an intermediate step involved?

Multi-line cells usually are created by using a linefeed character at the end of each line (inserted with Alt+Enter when entering data in a cell) - my code shouldn't be removing these, but if that was the case I would expect you to see probably a little 'box' character between your text lines in the cell it is copied to.

Try changing the format of the destination cell to Wrap Text (rightclick on it>Format Cells>Alignment tab and check Wrap Text) and see if that solves.

 

[nuevofurniture]

Use Clean() function in Excel to get rid of such problems. We use so much of excel to do the data work for our site.

Thanks,
Nuevo

 

[midar1]

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 is really good solution.
Anyway, I'll try to improve this solution.
If you recieve error like

Compile error:
​

User defined type not defined​

you probably didn't create a reference in the VBE to Microsft Forms 2.0 Lib.
​

How to do it?
​

• 
  [*=1]Go back to VBE with Alt+F11
  [*=1]Tools->References
  • 
    [*=1]if it is grey, macro is probably still running (but is stopped) - close VBE, that should stop the macro and reopen VBE
  
  [*=1]try to find "Microsoft Forms 2.0 Object Library"
  • 
    [*=1]If there is not this reference in the list, browse it manualy. It should be called FM20.DLL. I found it in C:\Windows\SysWOW64\. If you have 32-bit system, it will probably be in C:\Windows\system32\
    [*=1]"open" Microsoft Forms 2.0 Object Library
  
  [*=1]check "Microsoft Forms 2.0 Object Library"
  [*=1]OK
  [*=1]macro should work now

If you want copy more (visible) cells

[INDENT]Sub CopyCellContents()
Dim objData As New DataObject
Dim strTemp As String
strTemp = ""
Dim MyCell As Range
Selection.SpecialCells(xlCellTypeVisible).Select
For Each MyCell In Selection
    strTemp = strTemp & vbCrLf & vbCrLf & MyCell.Text
Next MyCell
objData.SetText (strTemp)
objData.PutInClipboard
End Sub
[/INDENT]

This code add one empty line between each cell:

& vbCrLf & vbCrLf

I'm still using combination of

&CHAR(13)&CHAR(10)

in my cells
Sorry for my english

here is potato:

 

[Savage702]

I understand this is pretty old, but after successfully using the above referenced vb code from midar1, I have managed to break it/find an issue with it.

I have data in Column A & B, I trim B in C and Hide Column C.
Column D & E references the data in the hidden C column using CONCATENATE.
In Column F, I use =D53&CHAR(10)&E53&CHAR(10) to bring D & E out into a single command that I can copy and paste out.

When C is hidden, the entire worksheet suddenly selects, and if I don't hit ESC fast enough, it will eventually kill Outlook.

I have many different sheets where I bring together multiple columns of data in my spreadsheet to copy out, and it works great.
But this is the first time I've had a hidden column. Now I can just shrink it down and make it virtually hidden, that's fine... just wondering if something I'm doing is breaking it, or if it works as intended and I'm just not aware I'm doing something wrong.

Excuse me if I'm using incorrect terminology, far from an Excel guru/expert... just a tinkerer that makes it work for my needs.

 

[Savage702]

Can't seem to figure out how/where to edit my post, but to add to this... this issue I posted above is when only selecting one cell in column F to CTRL + Q (copy).
If I selected multiple cells in F, no issue.