How to replace all paragraph marks in Microsoft Excel

[Tritanic]

I am trying to replace "$$$" with a paragraph mark (ctrl + j) in an Excel chart. I keep getting the error message "formula too long". It seems I am searching for a formula. I need to search for a value. What am I doing wrong.

[JustineBB]

You can use a macro to do the change:

Option Explicit
Sub testme01()
Dim FoundCell As Range
Dim ConstCells As Range
Dim BeforeStr As String
Dim AfterStr As String
BeforeStr = "$$$"
AfterStr = vblf
With ActiveSheet
Set ConstCells = Nothing
On Error Resume Next
Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0
If ConstCells Is Nothing Then
MsgBox "Select some cells in the used range"
Exit Sub
End If
With ConstCells
'get as many as we can in one step
.Replace what:=BeforeStr, Replacement:=AfterStr, _
lookat:=xlPart, SearchOrder:=xlByRows
Do
Set FoundCell = .Cells.Find(what:=BeforeStr, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'done, get out!
Exit Do
End If
FoundCell.Value _
= Replace(FoundCell.Value, BeforeStr, AfterStr)
Loop
End With
End With
End Sub

If you're using xl97, change that Replace() to application.substitute(). If you're new to macros, you may want to read David McRitchie's intro. Ps. Try it against a copy of

your data--just in case.

[BillieJ]

See the below link which will provide information on finding and replacing the items in Microsoft Excel.

Find and replace text or other items