Parsing Text Fields

[myers_co]

I have a column containing mixed data that I need to parse and extract a particular portion from but I've run out of brain power in figuring this one out. The data in the column is a combination of string codes and number codes that are joined by a varying number of spaces (no apparent rhyme or reason) and occasionally other symbols (like ~!@#$%^&*-_=+). The following list shows SOME of the possibilities of combinations that this column contains:

TRISTAR(15 spaces)M4 04333-0032
WIND ONE(11 spaces)K5
EXTRENDO(13 spaces)D1 92366
WILK17(17 spaces)K7 140 816
A-723(2 spaces)121-701
SAW SCAW 2222

It is a horrible mess! What I need to do is find a way to submit a pattern to a function and have the function return a zero (not found) or a numeric value (the position in the string) where the pattern is located. The pattern needs to allow for spaces, characters, digits and symbols (in fixed and variable quantities).

Examples:
=PARSE(A1, "[D5]-[D4]") should result in 26 for the first data example but return 0 for all others. The "[D5]-[D4]" pattern would describe 5 digits connected with a dash connected to 4 digits.

=PARSE(A1,"[SX][CD]") should result in 23 for the first example, 20 for the second, 22 for the third, 24 for the fourth, 0 for the fifth and 0 for the sixth. The "[SX][CD]" pattern would describe a variable number of spaces immediately followed by a character and digit combination.

 

[Rick Rothstein]

OK, fair enough but any user of the function is ..
a) going to have to know that it uses the Like operator, and
b) have a very good understanding of how the Like operator, and the overall functions works
.. if they are to devise any successful pattern for it.

Well, yes, that is true, but no more so than for the function you posted in Message #4. My modification to your function will simply allow the user to do exactly whatever you intended in your function, but with the ability to use wildcards (? for single character, * for zero or more characters).

 

[Peter_SSs]

.. but no more so than for the function you posted in Message #4.

Agree entirely, & for post #3, & hence my prefaces:

If you want to be able to devise your own patterns you will certainly need to read up on Regular Expressions.

.. but would require knowledge of "Like" patterns instead.