Removing Line Returns (Alt+Enter) in Excel Cells

Removing Line Returns (Alt+Enter) in Excel Cells

Didn't find your answer?

I have a spreadsheet with line returns making data appear on more than one line in each cell.
If I clear formatting or use alignment/wrap text I can see the formatting as small black line boxes or bold vertical lines.
Does anyone know how I can automatically remove or replace these.
Thanks
Richard
RICHARD BROOKS

Replies (2)

Please login or register to join the discussion.

avatar
By ACDWebb
01st Nov 2002 15:58

A rather lonwinded macro to do it....
Public Sub ClrLineFeeds(Optional ColLtr As String)
Const PlWt As String = "Please Wait..."
Dim LoseIt As String
LoseIt = Chr(10)'or replace 10 with 13
Dim a, b, c, d
Dim r, tr
Dim AddrCol
Dim addr As String

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
If ColLtr = "" Then
AddrCol = InputBox(prompt:="Enter letter for column containing addresses", _
Title:="Address Column")
Else
AddrCol = ColLtr
End If
If AddrCol = "" Then
MsgBox prompt:="No column for Addresses entered." & vbCr & "No changes will be made"
GoTo Finish
End If
AddrCol = ActiveSheet.Columns(AddrCol).Column
Columns(AddrCol).Replace what:=LoseIt, replacement:=";"

tr = ActiveSheet.UsedRange.Rows.count
For r = 2 To tr
Application.StatusBar = PlWt & "Checking row " & r & " of " & tr
addr = Cells(r, AddrCol).Address
a = Range(addr)
Do Until Right(a, 1) <> ";"
a = Left(a, Len(a) - 1)
Loop
Do Until InStr(a, ";") = 0
b = InStr(a, ";")
c = Mid(a, b, 1)
d = Mid(a, b + 1, 1)
If d = c Then
a = Left(a, b - 1)
Else
a = Left(a, b - 1) & vbCr & Mid(a, b + 1)
End If
Loop

Range(addr) = a
Next r
Finish:
Application.StatusBar = False
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
End Sub

Thanks (0)
Simon Hurst
By Simon Hurst
28th Oct 2002 09:29

Clean
The 'Clean' function might help, this removes non-printing characters from text strings in Excel - e.g. Clean(A1) should return the text in A1 with the alt-enters removed.

In Word you can replace specific non-printing characters using ^0nnn where nnn is the code of the non-printing character, but I don't think this works in Excel.

To replace you could try a combination of Substitute and Char functions as shown below. This should replace line feeds with spaces.

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

Hope this helps

Regards

Simon

Thanks (1)