Solved

Excel 2007 Removing Carriage Returns In a Cell

Posted on 2015-01-29
9
103 Views
Last Modified: 2015-02-25
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!
0
Comment
Question by:Escanaba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 40577913
This code should do it

Sub ReplaceCR()
Dim cCell As Range

For Each cCell In Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
    cCell = Replace(cCell, Chr(13), "")
Next cCell

End Sub

Open in new window



gowflow
0
 
LVL 5

Accepted Solution

by:
Hakan Yılmaz earned 500 total points
ID: 40577916
Please try this.
DeleteCR will delete carriage returns in selected range.
ReduceCR will replace double CRs with one.

Sub DeleteCR()
    Selection.Replace What:=ChrW(10), Replacement:=""
End Sub

Sub ReduceCR()
    Selection.Replace What:=ChrW(10) & ChrW(10), Replacement:=ChrW(10)
End Sub

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 40577927
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(10)," "))
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
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40577986
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

Open in new window

0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40578168
More generally, RemoveRepeating sub will replace a repetitive text with single ones in specified range.
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

Open in new window

0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40628844
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.
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40628845
Please review the question and answers. Question is about replacing not needed multiple blank returns with one.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question