Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for patterns in text split into adjacent cells
Hi,
If I have data in cells such as YDR DYH VNB etc, in separate cells, and I want to search for a pattern that can span multiple cells how do I do this? Suppose I want to search the cells for the pattern DRDY (which includes characters in cells 1 and 2) and color it when its found in the above example? Matt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for patterns in text split into adjacent cells
How about something like
=FIND("DRDY",A6&B6&C6), which returns the value 2 in your example. If "DRDY" is not found then the formula will return Value#. hth ScottO "chemdude77" wrote in message ... | Hi, | | If I have data in cells such as YDR DYH VNB etc, in separate cells, and I | want to search for a pattern that can span multiple cells how do I do this? | Suppose I want to search the cells for the pattern DRDY (which includes | characters in cells 1 and 2) and color it when its found in the above example? | | Matt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for patterns in text split into adjacent cells
Hi Scott,
Thanks for the reply this is exactly what I'm looking for! Matt "ScottO" wrote: How about something like =FIND("DRDY",A6&B6&C6), which returns the value 2 in your example. If "DRDY" is not found then the formula will return Value#. hth ScottO "chemdude77" wrote in message ... | Hi, | | If I have data in cells such as YDR DYH VNB etc, in separate cells, and I | want to search for a pattern that can span multiple cells how do I do this? | Suppose I want to search the cells for the pattern DRDY (which includes | characters in cells 1 and 2) and color it when its found in the above example? | | Matt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for patterns in text split into adjacent cells
Actually, I may have gotten excited too early as my code is failing now. The
problem is when the FIND command doesn't find the string the code crashes with error 1004: Unable to get the Find property of the WorksheetFunction class. Any idea how to fix this? Here is relevant code so far: position = Application.WorksheetFunction.Find("AYRT", rng.Cells(1, k) & rng.Cells(1, k + 1)) And then I have If-Then statements to evaluate the strating position of the string and color it accordingly. Final statement is an Else to start back at the top if the string doesnt exit, but this is the part that doesn't work. Any way to have it move on to the next k value if the string isn't found? Thanks Matt "chemdude77" wrote: Hi Scott, Thanks for the reply this is exactly what I'm looking for! Matt "ScottO" wrote: How about something like =FIND("DRDY",A6&B6&C6), which returns the value 2 in your example. If "DRDY" is not found then the formula will return Value#. hth ScottO "chemdude77" wrote in message ... | Hi, | | If I have data in cells such as YDR DYH VNB etc, in separate cells, and I | want to search for a pattern that can span multiple cells how do I do this? | Suppose I want to search the cells for the pattern DRDY (which includes | characters in cells 1 and 2) and color it when its found in the above example? | | Matt |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for patterns in text split into adjacent cells
Nevermind, I fixed the problem by switching to Mid statements:
For k = 1 To L - 1 position1 = Mid((rng.Cells(1, k) & rng.Cells(1, k + 1)), 1, 4) position2 = Mid((rng.Cells(1, k) & rng.Cells(1, k + 1)), 2, 4) position3 = Mid((rng.Cells(1, k) & rng.Cells(1, k + 1)), 3, 4) And then If-thens to evaluate. Everything's working now. Matt "chemdude77" wrote: Actually, I may have gotten excited too early as my code is failing now. The problem is when the FIND command doesn't find the string the code crashes with error 1004: Unable to get the Find property of the WorksheetFunction class. Any idea how to fix this? Here is relevant code so far: position = Application.WorksheetFunction.Find("AYRT", rng.Cells(1, k) & rng.Cells(1, k + 1)) And then I have If-Then statements to evaluate the strating position of the string and color it accordingly. Final statement is an Else to start back at the top if the string doesnt exit, but this is the part that doesn't work. Any way to have it move on to the next k value if the string isn't found? Thanks Matt "chemdude77" wrote: Hi Scott, Thanks for the reply this is exactly what I'm looking for! Matt "ScottO" wrote: How about something like =FIND("DRDY",A6&B6&C6), which returns the value 2 in your example. If "DRDY" is not found then the formula will return Value#. hth ScottO "chemdude77" wrote in message ... | Hi, | | If I have data in cells such as YDR DYH VNB etc, in separate cells, and I | want to search for a pattern that can span multiple cells how do I do this? | Suppose I want to search the cells for the pattern DRDY (which includes | characters in cells 1 and 2) and color it when its found in the above example? | | Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching Multiples Words in cells and returing it in adjacent cell | New Users to Excel | |||
How do I combine text from adjacent cells? | Excel Worksheet Functions | |||
text cells end page how split to next. Text lost! | Excel Discussion (Misc queries) | |||
Searching for text in cells | Excel Discussion (Misc queries) | |||
Searching text in cells | Excel Programming |