Escanaba
asked on
Excel 2007 Removing Carriage Returns In a Cell
Hello,
Hoping someone can assist by creating a formula or VB code that will remove all carriage returns within a cell for an entire column. For example, cell B2 has 3 blank returns, text, then 2 blank returns, and more text. I would like the blank returns deleted. Any thoughts on how to remove for the entire B column?
Thanks!
Hoping someone can assist by creating a formula or VB code that will remove all carriage returns within a cell for an entire column. For example, cell B2 has 3 blank returns, text, then 2 blank returns, and more text. I would like the blank returns deleted. Any thoughts on how to remove for the entire B column?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No points, please :)
Typically, to insert a new line with an Excel cell, the line feed character (ANSI 10) is used, not the carriage return (ANSI 13). Thus, Hakan's approach is probably more in line with what you need.
If you want a non-macro approach:
1) Use a formula like this:
=TRIM(SUBSTITUTE(A2,CHAR(1 0)," "))
That replaces line feeds with a single space, and TRIM reduces consecutive spaces to a single space
2) Copy that formula as needed
3) If desired, use Copy / Paste Special / Values over the original data, and delete the area with the formula above
Typically, to insert a new line with an Excel cell, the line feed character (ANSI 10) is used, not the carriage return (ANSI 13). Thus, Hakan's approach is probably more in line with what you need.
If you want a non-macro approach:
1) Use a formula like this:
=TRIM(SUBSTITUTE(A2,CHAR(1
That replaces line feeds with a single space, and TRIM reduces consecutive spaces to a single space
2) Copy that formula as needed
3) If desired, use Copy / Paste Special / Values over the original data, and delete the area with the formula above
This will reduce all repetitive CR's to 1 CR in selected range.
Sub ReduceTo1CR()
Do
Selection.Replace What:=ChrW(10) & ChrW(10), Replacement:=ChrW(10)
Loop Until Selection.Find(ChrW(10) & ChrW(10)) Is Nothing
End Sub
More generally, RemoveRepeating sub will replace a repetitive text with single ones in specified range.
Also a sub for prompting user input is added.
Also a sub for prompting user input is added.
Sub ReduceTo1CR()
RemoveRepeating Selection, ChrW(10)
End Sub
Sub PromptRemoveRepeating()
Dim promptrange As Range
Dim promptstring As String
On Error Resume Next
Set promptrange = Application.InputBox("Select the range from which you want to remove repetitive text", "RemoveRepeating", , , , , , 8)
If promptrange Is Nothing Then
MsgBox "No range selected."
Exit Sub
End If
promptstring = InputBox("Enter single text for replacing repetitives.", "RemoveRepeating")
If Len(promptstring) = 0 Then
MsgBox "No text written."
Exit Sub
End If
RemoveRepeating promptrange, promptstring
End Sub
Sub RemoveRepeating(ByRef myrange As Range, ByVal mystring As String)
Do
myrange.Replace What:=mystring & mystring, Replacement:=mystring
Loop Until myrange.Find(mystring & mystring) Is Nothing
End Sub
I've requested that this question be closed as follows:
Accepted answer: 500 points for gowflow's comment #a40577913
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Accepted answer: 500 points for gowflow's comment #a40577913
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Please review the question and answers. Question is about replacing not needed multiple blank returns with one.
Open in new window
gowflow