Replace Paragraph Return

[Paul Wagner]

Is there a way to replace a paragraph return in excel? In word I read that
you can do it by using "^p" (without the quotes), and in other editing
programs I've been able to use similar combinations to find invisible
characters such as the paragraph return or a tab, but this key combo doesn't
work in excel.

does anybody know what it might be?

Thanks

Paul

 

[Dave Peterson]

If the "paragraph returns" are made by alt-enter (char(10)'s), then you can

Select your range to fix
edit|replace
Find what: hit and hold the alt-key and type 0010
(from the number pad--not above the QWERTY keys)
It may look like nothing happened, but try it anyway.

Replace with: (space bar) or (leave blank)
(what ever you want)

Then replace or replace all.

If those paragraph marks are actually char(13)'s, I've always used a little
macro:

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

You could use this with chr(10), too. (Change replacement:="" to what you
want (maybe a space character???).)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Paul Wagner]

Dave thanks so much for the tips. In the file I'm trying to fix there is a
column that is all product descriptions... when I do the replace it will do
some of the cells but then it will hit a cell that has a little more copy
and then it says that the "formula is too long." Is there a way around that?

thanks again
Paul

 

[Dave Peterson]

In a private reply:

If you're using xl2k or xl2002, this might work:

Option Explicit
Sub testme01()
Dim myCell As Range
For Each myCell In Selection.Cells
myCell.Value = Replace(myCell.Value, Chr(10), " ")
Next
End Sub

(Replace was added in xl2k.)