I am copying data into excel where line feeds in the original data are
represented by #. I want to do a find and replace all # with a new line (alt
+ enter) in a cell. How can I do this easily? Or if not easily, with a macro?

Hi,

Adapt following macro to your needs :

Sub Macro1()
Cells.Find(What:="#", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
ActiveCell.Replace What:="#", Replacement:=Chr(10),
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

HTH

Hi Carim,

This doesn't seem to be working.
I need to be able to select a column and search and replace all occurrences
of "#" in ALL populated cells in the column. Is there an equivalent
Cell.ReplaceAll function I can use? I'm afraid my knowledge of creating
Macros is limited to "Record" and "Stop".

Thanks in advance,
R


"Carim" wrote:

Hi,

Adapt following macro to your needs :

Sub Macro1()
Cells.Find(What:="#", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
ActiveCell.Replace What:="#", Replacement:=Chr(10),
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

HTH

Hi,

Select a range and then run macro ...

Sub Macro1()
Dim cell As Range
For Each cell In Selection
Cells.Find(What:="#", After:=cell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
cell.Replace What:="#", Replacement:=Chr(10), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Next cell
Range("A1").Select
End Sub

HTH

Works superbly. Thanks.

"Carim" wrote:

Hi,

Select a range and then run macro ...

Sub Macro1()
Dim cell As Range
For Each cell In Selection
Cells.Find(What:="#", After:=cell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
cell.Replace What:="#", Replacement:=Chr(10), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Next cell
Range("A1").Select
End Sub

HTH

Glad it fixed your problem ...

Thanks for the feedback

Another option is to replace all the # with that newline in one command.

Record a macro when you:
select all the cells
edit|Replace
what: #
with: ctrl-j
replace all



rmellison wrote:

I am copying data into excel where line feeds in the original data are
represented by #. I want to do a find and replace all # with a new line (alt
+ enter) in a cell. How can I do this easily? Or if not easily, with a macro?

--

Dave Peterson