Glenn wrote:
Steve W. wrote:
Hello,

Odd problem. I need to find text in a column...
Every row (44,000) contains a single byte and it needs to stay that
way as the previous columns contain information about that single byte..

I need to search for patterns of text/strings against a column....

For example.
A B C D E Info Info Info Info 5
Info Info Info Info 2
Info Info Info Info 0
Info Info Info Info 3
Info Info Info Info 4
Info Info Info Info 7
Info Info Info Info 1
Info Info Info Info 3
Info Info Info Info 6
Info Info Info Info 7

So I need to search for "5203471367" and find that repeating pattern.

I can not seem to find a way or function to do it inside of Excel and
I am not a programmer so I am unable to write code/macros/vbs.

I tried to concatenate the data in a new column but it only lets me do
30 at time... and that wont really work anyway because I need to what
differences exist from row to row within my search pattern... (the
previous columns text do not match and I need to find the
commonalities so I can create a search function for flat/text files)

Thanks for any help that can be provided.


You could use a UDF to concatenate the values in column E into a string
and use the SEARCH() or FIND() function to look for your pattern. I
found the following with a quick search of the newsgroup:


Function mcat(ParamArray s()) As String
Dim r As Range, x As Variant, y As Variant
For Each x In s
If TypeOf x Is Range Then
For Each r In x.Cells
mcat = mcat & r.Value
Next r
ElseIf IsArray(x) Then
For Each y In x
mcat = mcat & y
Next y
Else
mcat = mcat & x
End If
Next x
End Function


Seems to have a limit of 32767 characters, so you may have to string a
couple of them together to cover all of your data. With "5203471367" in
F1:


=IF(ISERROR(FIND(F1,mcat(E1:E25000))),
IF(ISERROR(FIND(F1,mcat(E20001:E45000))),"not found",
FIND(F1,mcat(E20001:E45000))+20000),FIND(F1,mcat(E 1:E25000)))




Will return the first row number of the first time your string was found. Not
sure what you want to do from there.


Look here if you need help with the UDF:

http://www.vertex42.com/ExcelArticle...functions.html