Page 1 of 2 12 LastLast
Results 1 to 10 of 13

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

This is a discussion on CHAR(10)&CHAR(13), and double quotes? within the Excel Questions forums, part of the Question Forums category; Hello Board members, I am trying to get text in a single cell to be broken into several paragraphs when ...

  1. #1
    New Member
    Join Date
    May 2009
    Posts
    2

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

    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!

  2. #2
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: CHAR(10)&CHAR(13), and double quotes?

    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.
    Richard Schollar

    Using xl2013

  3. #3
    Board Regular
    Join Date
    Mar 2009
    Posts
    332

    Default Re: CHAR(10)&CHAR(13), and double quotes?

    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.
    You can't teach an old dog new tricks...
    but you can re-define 'old'.

  4. #4
    New Member
    Join Date
    May 2009
    Posts
    2

    Default Re: CHAR(10)&CHAR(13), and double quotes?

    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

  5. #5
    Board Regular
    Join Date
    Mar 2006
    Location
    Italy
    Posts
    1,132

    Default Re: CHAR(10)&CHAR(13), and double quotes?

    If the generated string has to go to a web page maybe you can use the formula
    Code:
    ="blah blah "&"< br >"&"more blah blah "&"< br >&"even more blah blah "
    (remove the spaces before and after "br", ie after "<" and before ">")

    Bye.
    Anthony47 (GMT+1)

  6. #6
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: CHAR(10)&CHAR(13), and double quotes?

    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.
    Richard Schollar

    Using xl2013

  7. #7
    New Member
    Join Date
    Jul 2009
    Posts
    2

    Default Re: CHAR(10)&CHAR(13), and double quotes?

    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

  8. #8
    New Member
    Join Date
    May 2010
    Posts
    2

    Default Re: CHAR(10)&CHAR(13), and double quotes?

    Quote Originally Posted by Richard Schollar View Post
    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 seems to be by far the best solution to this problem but when i try it i am getting the fowling error:

    Compile error:
    User defined type not defined

    Can you tell me what am i doing wrong here?

  9. #9
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: CHAR(10)&CHAR(13), and double quotes?

    Hi

    Did you follow the top instruction in the code?

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

    Using xl2013

  10. #10
    New Member
    Join Date
    May 2010
    Posts
    2

    Default Re: CHAR(10)&CHAR(13), and double quotes?

    Quote Originally Posted by Richard Schollar View Post
    Hi

    Did you follow the top instruction in the code?

    Thank you very much for your reply.
    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.
    Still there is one last problem in my worksheet to solve. What i want to do is this.
    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.
    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.

    ex.

    In the cell i have this
    command no1
    command no2
    command no3
    etc

    when i run the formula the result is this command no1command no2command no3 (at least with no quotes but still i cannot use it)

    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.
    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)
    Still your double quotes solution was very practical.
    Thank you in advanced.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com