Getting rid of carriage return in cell

[James R]

I am trying to improve a spreadsheet a colleague started. There are a ton of
carriage returns within cells.

1. How can I get rid of the carriage returns?
and
2. Is there some way I can get rid of the carriage returns, but have the
existing text in the cell distributed vertically in unused cells?

Thanks in advance for the help, I can provide more detail if it is needed.

 

[Rick Rothstein]

Select the cell or cells, click Data/Text To Columns form Excel's menu bar,
choose the Delimited option on the first dialog page, click the Next button,
uncheck everything except Other and type Ctrl+J into the field next to it
(you won't see anything in that box, but the column layout chart will show
that it is being split at the line feeds (they are not carriage returns)...
then just click the Finish button.

 

[James R]

Thanks for your help, Rick. I appreciate it. Now, what would be the fastest
way to get that information from rows to columns? I've done the 'transpose'
function of Paste Special, is there an easier way to do it?

 

[Rick Rothstein]

I'm sorry... I the part where you said "distributed vertically in unused
cells" didn't register with me when I first read your message. Where is your
multi-line data located... in a column, in a row, or scattered about? Are
the "unused cells" you mentioned already located immediately under the cells
with the multiple lines or are they in a different place?

 

[James R]

I should have explained in more detail. The data I have is as follows:
Column A: Entity (ex: Budgeting)
Column B: Account (ex: 1234)
Column C: Group members

Column C contains the data with line feeds that I want vertically in the
column, instead of all in one cell. For example, I have "SmithR and DoeJ in
cell C2, and I want that data to be in cells C2 and C3, but I want the next
row of data in A3:C3 to be bumped down to A4:C4, so I don't lose that data.

I hope this helps emxplain my situation; thanks for all the help so far, Rick.

-James