carriage returns

[Nancie]

I need to know if there is a way to globally remove
carriage returns that have been made in a cell. EX: I
get flat file databases in that have carriage returns
within individual cells. I want be able to "save as" a
csv file. Is there a way to globally remove all carriage
returns? I use the "clear all formatting" function but
that does not remove carriage returns. Any help would be
greatly appreciated.

 

[Peter Atherton]

Nancie

You can use this code to remove the returns.

Sub removeReturns()
Dim c As Variant, f, s
Dim p As Integer
With Selection
.WrapText = False
End With
For Each c In Selection
p = Application.WorksheetFunction.Find(Chr(10), c, 1)
s = Right(c, Len(c) - p)
f = Left(c, p - 1)
c.Value = f & " " & s
Next c

End Sub

Press Alt + F11 to open the VB editor then choose
Insert, Module.

Copy the code into the module, Select the text in the
sheet and run the macro.

F5 will run through the macro quickly, or you can press F8
to to step through the code. If the window is small you
can see the code execute as it loops through

Regards
Peter

remove nothere from address to contact direct.

 

[Dave Peterson]

You could used Edit|Replace and do it manually:

Edit|Replace
find what: hit and hold the altkey while typing 0010 on the numeric keypad
with what: (spacebar) or (leave blank)

(I would think you'd want a spacebar for readability purposes.)

Or as a macro:

Option Explicit
Sub testme()
ActiveSheet.Cells.Replace What:=Chr(10), Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

 

[Nancie]

Hi Dave,

Thanks so much for taking the time to reply. I did as you
advised with the macro but I am getting an error when I
try to run it.
Compile Error: Syntax error

Sub testme() is highlighted in yellow
and
the command line directly under it is highlighted in black.

Have any ideas?

I also tried the find replace but it didn't quite catch
all of the returns...most of them but not all, don't know
why.

Thanks so much.

-----Original Message-----
You could used Edit|Replace and do it manually:

Edit|Replace
find what: hit and hold the altkey while typing 0010 on the numeric keypad
with what: (spacebar) or (leave blank)

(I would think you'd want a spacebar for readability purposes.)

Or as a macro:

Option Explicit
Sub testme()
ActiveSheet.Cells.Replace What:=Chr(10),

Replacement:=" ", LookAt:=xlPart, _

 

[Nancie]

Hi Peter,

Thanks for the reply. I did as you suggested but when I
run the macro I get this error message:
Runtime Error "1004"
Unable to get the find property of the WrokSheetFunction"
class.

any ideas as how to fix it?

Thanks again for you help and time.

 

[Dave Peterson]

First, did you copy and paste from the newsgroup posting or did you type the
macro yourself?

If you typed, look for a typo. (It worked ok for me.) Or just copy|paste once
more.

my original post looked ok, but this will make sure you weren't hit by linewrap
issues:

Option Explicit
Sub testme()
ActiveSheet.Cells.Replace What:=Chr(10), _
Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

And are you sure that all the returns weren't changed--maybe you had some cells
set to wraptext and it just overflowed to the next line within the cell?

Chip Pearson has a very nice addin that will help you inspect the contents of a
cell:

http://www.cpearson.com/excel/CellView.htm

It'll help you really know what that character is.