Remove paragraph mark when copying from excel cell

Question

Please help me copy a string from a listbox when a user hits ctrl+c. I was using the dataobject but for some reason this worked perfectly some times and gave me an error message other times. If you know why this is, stop reading, as the rest of this question is not necessary.

Now I am putting this in a worksheet cell and using range.copy, however, when the string is pasted into a textbox, it retains the paragraph mark that excel seems to put at the end of every cell! Just to make things fun, the paragraph mark cannot be removed by using Left() - it takes everything but the paragraph mark. (Paragraph mark below is represented by P).

s = "stringP"
s = Left(s,len(s)-1)
print s

returns: strinP

Has to be something simple I'm missing.

Answer 1

Have you tried trim() function ? And why do you have to use Range.copy?

Can't you just assign textbox1.value = Range("A1") ? It works fine without any bugs.

Answer 2

I haven't tested this but have you tried chopping two characters?

I'm sure it's \r\n or carriage-return + line feed, not just \n you need to chop.