Searching for a string of text inside a column
 

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.

Searching for a string of text inside a column
 

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.

--
Biff
Microsoft Excel MVP


"Steve W." <Steve wrote in message
...
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.

Searching for a string of text inside a column
 

"T. Valko" wrote:

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!!

--
Biff
Microsoft Excel MVP

-Steve
Some_d00D

Searching for a string of text inside a column
 

Oh well! Good luck!

--
Biff
Microsoft Excel MVP


"Steve W." wrote in message
...


"T. Valko" wrote:

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!!

--
Biff
Microsoft Excel MVP

-Steve
Some_d00D

Searching for a string of text inside a column
 

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)))


Look here if you need help with the UDF:

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

Searching for a string of text inside a column
 

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