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

apia

New Member
Joined
May 31, 2009
Messages
2
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!
 
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.
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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:

Code:
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

Code:
[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

:warning:I'm still using combination of
&CHAR(13)&CHAR(10)
in my cells
:oops:Sorry for my english

here is potato:
tomato-250x250.jpg
 
Upvote 0
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. :-)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,210
Members
453,023
Latest member
alabaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top