Paste text into single cell

[geobruin]

Hi! I have a couple scripts that I would like to run on chunks of text in a single cell (A1). The problem I have is that often the text I copy has paragraph breaks so Excel likes to throw each line of text in a new row. Here is some example text:

Buddy you're a boy make a big noise
Playing in the street gonna be a big man some day
You got mud on yo' face, you big disgrace
Kicking your can all over the place

Singing
We will we will rock you
We will we will rock you

When I highlight cell A1 and paste the text, the first line appears in A1 but the second line starting with "Playing in the street" appears in A2. I would like the text to appear as a continuous string as below:


Buddy you're a boy make a big noise Playing in the street gonna be a big man some day You got mud on yo' face, you big disgrace Kicking your can all over the place Singing We will we will rock you We will we will rock you

I guess ideally, every paragraph break would just become a space.

Thanks in advance,

Cheers!

 

[pgc01]

Hi

To paste the text into the cell:

- Select the cell
- click on the formula bar
- paste

All the text will be pasted in the same cell.

If you want you can then replace the newlines with spaces.

 

[geobruin]

Wow. That was stupid simple. Thank you.

As for replacing "newlines" with spaces, how do I identify a "newline" and a space in the find and replace tool?

Thanks again!

 

[pgc01]

A newline is usually, depending where you imported the text from, either just a linefeed (character ascii 10) or a carriage return followed by a linefeed (characters ascii 13, 10).

Assuming you pasted the text in A1, use in B1

for the first case:

=SUBSTITUTE(A1,CHAR(10)," ")

for the second case:

=SUBSTITUTE(A1,CHAR(13)&CHAR(10)," ")

 

[Wes Tracy]

[h=2]How to preserve paragraph breaks in pasted text? [/h]

There's an easy way to preserve the paragraph breaks. If you only select the cell and then paste, it will go into multiple cells like you are experiencing. However, if you double click in the cell so your cursor is active, and then paste, it will all go in the same cell.

Wes​