Excel: how do I remove all carriage returns from a cell?

I want to get rid of all the carriage returns in my cell. How do I do this?

1

5 Answers

=CLEAN(A1)

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

2

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).

1

=Clean(A1)

This command can be found in Excel under the formulas tab > text.

1

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.

Sub RemoveLineBreaks() Application.ScreenUpdating = False Dim rngCel As Range Dim strOldVal As String Dim strNewVal As String For Each rngCel In Selection If rngCel.HasFormula = False Then strOldVal = rngCel.Value strNewVal = strOldVal Debug.Print rngCel.Address Do strNewVal = Replace(strNewVal, vbLf, " ") If strNewVal = strOldVal Then Exit Do strOldVal = strNewVal Loop If rngCel.Value <> strNewVal Then rngCel = strNewVal End If End If rngCel.Value = Application.Trim(rngCel.Value) Next rngCel Application.ScreenUpdating = True
End Sub

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy