Limitation of Find and Replace Text in Excel

  • Thread starter Thread starter wolfteeth
  • Start date Start date
W

wolfteeth

Location:
Posts: 1
Limitation of Find and Replace Text in Excel
I am trying to reformat some data in Excel to make some downloade
information more easily readable. The information to be manipulated i
a. In the download the different items of History are joined togethe
with a | delimiter to produce a long wrap around string.

I have an Excel macro which finds the | delimiter in the string an
converts this to a line break (i.e. vbLF or Alt+Enter). The expecte
result is that wherever | occurs, subsequent text is pushed to a ne
line.

I have found that the macro worked perfectly if the length of th
string does not exceed 911 characters. It failed if the length of th
string exceeded 911 characters.

would appreciate it if you could help investigate this further t
determine if there is something in our macro which has triggered th
lower limits.

I attach the following:

Sub Macro1()
ActiveWindow.ActivateNext
Cells.Select
Selection.Replace What:="|", Replacement:=vbLf, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Look forward to your advice after testing

Best regard

Attachment filename: reformatter2.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=69731
 
This worked for me in xl2002:

It tries to do one mass change (to just the constant cells), then it looks to
see if any vertical bars are still in that range.

If there are, it adjusts the value on a cell by cell basis.

Option Explicit

Sub testme()

Dim ConstRng As Range
Dim FoundCell As Range

With Worksheets("sheet1")
Set ConstRng = Nothing
On Error Resume Next
Set ConstRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If ConstRng Is Nothing Then
MsgBox "No constants!"
Exit Sub
End If

ConstRng.Replace what:="|", replacement:=vbLf, _
lookat:=xlPart, MatchCase:=False

Do
With ConstRng
Set FoundCell = .Find(what:="|", after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If FoundCell Is Nothing Then
Exit Do
End If

FoundCell.Value _
= Application.Substitute(FoundCell.Value, "|", vbLf)
'or if you're using xl2k or higher
' = replace(foundcell.value, "|", vblf)
End With
Loop
End With
End Sub
 
Back
Top