find text split across cells

  • Thread starter Thread starter djh
  • Start date Start date
D

djh

Is there a way to search for a word or phrase which may
be a part of more that one cell? For example, one cell
may contain the text string "the remit" and the next
contain "tance". Is there a way I can search
for "remittance" and end up positioned in the first of
these two cells?
 
djh wrote...
Is there a way to search for a word or phrase which may be a
part of more that one cell? For example, one cell may contain
the text string "the remit" and the next contain "tance". Is
there a way I can search for "remittance" and end up positioned
in the first of these two cells?
Click to expand...

Not without using VBA. Why are your cell contents corrupted like this
Seems it might be more efficient to treat the cause rather than th
symptoms
 
How would this be done in VBA? The text is split across
the cells, because the spreadsheets are the result of
report text files with columns of data being imported.
The text is in the header and footer portions of each
page. The finds are part of macros to get rid of the
paging, headers, footers, etc.
 
djh wrote...
How would this be done in VBA?
Click to expand...

Sub s4st()
Const END_OF_FIRST As String = "foo"
Const START_OF_LAST As String = "bar"

Dim c As Range
Static fmc As String

On Error GoTo CleanUp

If ActiveCell.Address(0, 0) = "A1" Then
Set c = ActiveSheet.UsedRange.Cells( _
ActiveSheet.UsedRange.Cells.Count _
)
Else
Set c = ActiveCell
End If

Set c = ActiveSheet.UsedRange.Find( _
What:=END_OF_FIRST, _
After:=c, _
SearchDirection:=xlNext, _
LookIn:=xlValues, _
LookAt:=xlPart _
)

If fmc = "" Then fmc = c.Address(0, 0)

Do
If c.Value Like "*" & END_OF_FIRST _
And c.Offset(0, 1).Value Like START_OF_LAST & "*" Then
c.Activate
Exit Sub
End If

Set c = ActiveSheet.UsedRange.Find( _
What:=END_OF_FIRST, _
After:=c, _
SearchDirection:=xlNext, _
LookIn:=xlValues, _
LookAt:=xlPart _
)
Loop While c.Address(0, 0) <> fmc


CleanUp:

If c Is Nothing Then
MsgBox _
Prompt:="No cells found containing" & Chr(13) & END_OF_FIRST, _
Title:="Search For Split Text"

ElseIf c.Address(0, 0) = fmc Then
MsgBox _
Prompt:="No more matching cells found", _
Title:="Search For Split Text"

End If

End Sub

The text is split across the cells, because the spreadsheets are
the result of report text files with columns of data being
imported.
Click to expand...
...

You could import without parsing, so all lines of text in a singl
column. Then you could search for the unbroken text, delete the line
containing it (or whatever else you do), and finally parse th
remaining lines into fields in multiple columns.

Purge before parsing rather than parse before purging
 
Back
Top