Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
searching for text inside a text box | Excel Discussion (Misc queries) | |||
searching for text inside a text box | Charts and Charting in Excel | |||
Formula for searching for a text string | Excel Discussion (Misc queries) | |||
Searching a text string in a range of cells. | Excel Worksheet Functions | |||
Need macro to insert text string while inside cell (formula) | Excel Discussion (Misc queries) |