Searching for a string of text inside a column

[Steve W.]

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.

 

[T. Valko]

One way:

Entered if F2 and copied down as needed:

=AND(E2=5,E3=2,E4=0,E5=3,E6=4,E7=7,E8=1,E9=3,E10=6,E11=7)

Cells that return TRUE will mark the start of the sequence.

 

[Steve W.]

One way:

Entered if F2 and copied down as needed:

=AND(E2=5,E3=2,E4=0,E5=3,E6=4,E7=7,E8=1,E9=3,E10=6,E11=7)

Cells that return TRUE will mark the start of the sequence.

Tried it, I am certain that it works however in my case the data was not
concise enough from what I can tell to search for known patterns.

I appreciate the answer though, thank you!!

-Steve
Some_d00D

 

[T. Valko]

Oh well! Good luck!

 

[Glenn]

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(E1:E25000)))


Look here if you need help with the UDF:

http://www.vertex42.com/ExcelArticles/user-defined-functions.html

 

[Glenn]

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(E1:E25000)))

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