Find and replace # with new line in a cell.

[rmellison]

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?

 

[Carim]

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

 

[rmellison]

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]

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

 

[rmellison]

Works superbly. Thanks.

 

[Carim]

Glad it fixed your problem ...

Thanks for the feedback

 

[Dave Peterson]

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