Looking for a macro to remove Line Feeds and Carriage Returns

[Dave]

Greetings Excel masters,

I'm attempting to come up with a macro, or some other automated way, that
will allow me to strip out CR and LF in my Excel files. I'm converting these
to CSV files, and the system that imports them has a fit with CR/LFs that are
imbedded, making them into new lines. In searching the net, I've found a few
macros, but none of them have been the holy grail solution I am looking for.
I've listed them below.

The first one looks great in its simplicity, but it often fails to operate.
Not sure if there is some criteria I'm missing (maybe it requires a specific
worksheet name or something?). The second one works, however it often
crashes out when used on large amounts of data with an error of being too
big. I also have to have two versions - one for CR, one for LF as I don't
know how to have multiple items in there. Suggestions are greatly
appreciated.

PS: As a bonus, I'd also like to strip out pipe (|) characters. I'm not
sure how to include this. Ideally one big macro will nuke out all three
troublesome items.

***************************
Sub RemoveCrLfs()

Dim pobjCell As Range
Dim plCharCounter As Long
Dim psCellText As String

For Each pobjCell In Selection
psCellText = pobjCell.Text
Do While InStr(psCellText, vbCrLf) > 0
psCellText = Replace$(psCellText, vbCrLf, " ")
Loop
pobjCell.Value = psCellText
Next

End Sub
****************************
*****************************
Sub LineFeedRemoval()
For Each cl In Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeConstants,
23)
cl.Replace What:=Chr(10), Replacement:=" ", SearchOrder:=xlByColumns
Next cl
End Sub

 

[Tim Williams]

Untested, but try something like this

Sub Tester()
dim c as range
dim arrVals, ub, x

arrVals=Array(vbcrlf, vblf, "|") 'add terms as required
ub=ubound(arrVals)

for each c in selection.cells
if len(c.value)>0 then
for x=0 to ub
c.value=replace(c.value,arrVals(x)," ")
next x
end if
next c

end sub

If you have large amounts of data, it will go faster if you turn off
screenupdating and set calculation to manual (don't forget to set them back)

Tim

 

[JBeaucaire]

I use the clean function to some success:

=======
Sub CleanData()
'Remove Linefeeds and Pipe characters
'from selected text
Dim cell As Range

Selection.Replace What:="|", Replacement:="", LookAt:=xlPart

For Each cell In Selection
cell = Clean(cell)
Next cell

End Sub
=========

Does that help?

 

[JBeaucaire]

I like Tim's array. Meanwhile mine had a small error in it:

======
For Each cell In Selection
cell = Application.WorksheetFunction.Clean(cell)
Next cell
======