Using CHAR(10) inserting quotes ". How do I avoid this

[bmacbmac]

Hi,

I have lines in excel in the following format:

Code: Select all

=concatenate(a1, a2, CHAR(10), a3, a4))
=concatenate(b1, b2, CHAR(10), b3, b4))

I'm using the CHAR(10) as a carriage return. When I copy and past this info into text pad, the carriage return is recognized, but a set of quotes comes across at the beginning and end of each row. It looks like this:

"Test1 Test2
Test3 Test4 "
"Test5 Test6
Test7 Test8"
"Test9 Test10
Test11 Test12"

But I need it to come across without the quotes:

Test1 Test2
Test3 Test4
Test5 Test6
Test7 Test8
Test9 Test10
Test11 Test12

My concatenate script is actually longer that includes quotes so a simple find/replace of " isn't working for me.

Is there a way I can paste this info into Textpad without the quotes coming over? I tried an \n instead of CHAR(10) in excel, but it wouldn't recognize this character.

[ak47wong]

As far as I know, multi-line cells in Excel always get copied with double-quotes. How about continuing with the idea in your last paragraph: use a unique character or string instead of CHAR(10) and then replace that character or string with a newline in TextPad?

For example, use the formula: =CONCATENATE(A1, A2, "##", A3, A4)
then copy the values to TextPad and do a regular expression replace of "##" with "\n".

Andrew

[bmacbmac]

As far as I know, multi-line cells in Excel always get copied with double-quotes. How about continuing with the idea in your last paragraph: use a unique character or string instead of CHAR(10) and then replace that character or string with a newline in TextPad?

For example, use the formula: =CONCATENATE(A1, A2, "##", A3, A4)
then copy the values to TextPad and do a regular expression replace of "##" with "\n".

Andrew

That's a good idea! I'll try that.