The above is what I really NEED, but I figure I might be able to solve another potential issue if someone has a way to populate that column with some sort of customizable search like "X+10numbers" or "Y+6numbers". Like if I was able to enter P######, and it would populate the column with every occurence of a P followed by 6 numbers. Or just enter ###, and it would populate any 3 digit number.
I think this function (which can be used as a UDF) will do what you asked for above...
Code:
Function GetPattern(Source As String, ByVal Pattern As String) As String
Dim X As Long, FindPattern As Long
Do Until Left(Pattern, 1) <> "*"
Pattern = Mid(Pattern, 2)
Loop
For X = 1 To Len(Source)
If Mid(Source, X) Like Pattern & "*" Then
FindPattern = X
Exit For
End If
Next
For X = 1 To Len(Source) - FindPattern + 1
If Mid(Source, FindPattern, X) Like Pattern Then
GetPattern = Mid(Source, FindPattern, X)
Exit For
End If
Next
End Function
The pattern formation rules are the same as for the Visual Basic "Like Operator". Note that the special characters can be mixed with regular characters as needed. So, you can use the patterns you indicated in your posting "P######", "###", etc. You can also make more complex patterns, for example, "P##[A-Za-z]#X" which would return a text string starting with a "P", followed by two digits, followed by any upper or lower case letter, followed by one more digit and ending with an "X". Here is the description of the pattern matching rules from the VB help files...
<TABLE dir=ltr border=1 cellSpacing=1 cellPadding=6 width=623><TBODY><TR><TD vAlign=center width="18%">
Characters in pattern
</TD><TD vAlign=center width="82%">
Matches in string
</TD></TR><TR><TD vAlign=center width="18%">
?
</TD><TD vAlign=center width="82%">Any single character.
</TD></TR><TR><TD vAlign=center width="18%">
*
</TD><TD vAlign=center width="82%">Zero or more characters.
</TD></TR><TR><TD vAlign=center width="18%">
#
</TD><TD vAlign=center width="82%">Any single digit (0–9).
</TD></TR><TR><TD vAlign=center width="18%">[
charlist]
</TD><TD vAlign=center width="82%">Any single character in
charlist.
</TD></TR><TR><TD vAlign=center width="18%">[
!charlist]
</TD><TD vAlign=center width="82%">Any single character not in
charlist.
</TD></TR></TBODY></TABLE>
A group of one or more characters (
charlist) enclosed in brackets (
[ ]) can be used to match any single character in
string and can include almost any
character code, including digits.
Note To match the special characters left bracket (
[), question mark (
?), number sign (
#), and asterisk (
*), enclose them in brackets. The right bracket (
]) can't be used within a group to match itself, but it can be used outside a group as an individual character.
By using a hyphen (
–) to separate the upper and lower bounds of the range,
charlist can specify a range of characters. For example,
[A-Z] results in a match if the corresponding character position in
string contains any uppercase letters in the range A–Z. Multiple ranges are included within the brackets without delimiters.
Other important rules for pattern matching include the following:
An exclamation point (
!) at the beginning of
charlist means that a match is made if any character except the characters in
charlist is found in
string. When used outside brackets, the exclamation point matches itself.
A hyphen (
–) can appear either at the beginning (after an exclamation point if one is used) or at the end of
charlist to match itself. In any other location, the hyphen is used to identify a range of characters.
When a range of characters is specified, they must appear in ascending sort order (from lowest to highest).
[A-Z] is a valid pattern, but
[Z-A] is not.
The character sequence
[] is considered a zero-length string ("").
The meaning of a specified range depends on the character ordering valid at
run time (as determined by
Option Compare and the
locale setting of the system the code is running on). Using the
Option Compare Binary example, the range
[A–E] matches A, B and E. With
Option Compare Text,
[A–E] matches A, a, À, à, B, b, E, e. The range does not match Ê or ê because accented characters fall after unaccented characters in the sort order.
