CODEWITHSUNDEEP
excel-formula
Tom Melly
Tom Melly

Reputation: 108

Excel - Prevent quotes when using concatenate with new lines

When using concatenate with char(10), my output ends up being wrapped in quotes when I cut and paste the value to a new application.

e.g. =concatenate("A",char(10),"B") produces:

"A
B"

in Notepad.

Anyone know how to suppress the quotes? (this only seems to happen with concatenate + new lines).

Upvotes: 3

Views: 5161

Answers (3)

TMWP
TMWP

Reputation: 1625

I was using Excel to piece together some xml, so I wanted to retain all the "pretty-print" style white space in the snippets I put in my concatenated input cells. Then I used the "&" for concatenate as in: A1&A2&A3 ... and got the same problem described on this post.

Pasting in Word strips off all the indent white space around my tags putting them flush against the margin (not good)

    <tag1>
    <tag2>
    <tag3>
    </tag3>
...

Using Clean() around it was even worse - it killed all spaces of any kind so my tags came out like this:

<tag1><tag2><tag3></tag3><tag2> ...

The answer that worked for me was to do this: in the A1 cell, add a double-quote at the start (which is supposed to mean right-justified text cell).

In the final cell, add a double-quote at the end of the content to be concatenated

The result then looks like this (the desired tagging output):

"""
      <tag1>
          <tag2>
             <tag3>
             </tag3>
              ...

 """

All white space is now preserved.

although the tripple double-quotes are not ideal, they mark the start and end of a record and represent a character combination that does not exist anywhere else in the content I am trying to concatenate. That makes it easy in a programmer's text editor to do a global replace that takes them all out and leaves me with what I was trying to piece together in Excel.

If anyone knows a way that eliminates this one manual step, please do post on here as I'd love to see it.

Upvotes: 0

Carlos Teevin
Carlos Teevin

Reputation: 139

Avoid the annoying quotes by adding "clean" function to the very beginning of your formula, examples:

Original formula surrounding result with quotes:

=CONCATENATE(A1, " ", B1, ".", C1)

Final formula quotes free:

=CLEAN(CONCATENATE(A1, " ", B1, ".", C1))

Upvotes: 1

Swagata
Swagata

Reputation: 622

This is not exactly a solution to your problem but if you paste it in MS Word, you won't get the quotes. If your requirement is to see the data without quotes, this may help.

Upvotes: 1

Related Questions