I am using Excel to query an Access database. The query is pulling in a memo
field from Access. Excel is displaying the carriage control "chr(13)" as a
box. I can do a find replace using "chr(13)" changing it to a space until I
hit a cell that has to much data in it. I am getting a "formula too long"
error. Is there a way to use VB code to address this issue and replace this
"box"?

Modified from a previous post:

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim ConstCells As Range
Dim BeforeStr As String
Dim AfterStr As String

'double quote (") is chr(34)
BeforeStr = Chr(13)
AfterStr = " "

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 at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ps. Try it against a copy of your data--just in case!

LenJr wrote:

I am using Excel to query an Access database. The query is pulling in a memo
field from Access. Excel is displaying the carriage control "chr(13)" as a
box. I can do a find replace using "chr(13)" changing it to a space until I
hit a cell that has to much data in it. I am getting a "formula too long"
error. Is there a way to use VB code to address this issue and replace this
"box"?

--

Dave Peterson