excel: how do i replace all carriage returns from a cell?

Question

i want to get rid of all the carriage returns in my cell. how do i do this

Answer 1

Assuming your cell is in A1, you can use the following formula:

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

Depending on the carriage return, you may have to use char(13) instead of char(10).

Answer 2

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.

Answer 3

=CLEAN(A1)

Clean removes all nonprintable characters from text. -- Excel Help Documentation