Use More then one Regex to determine a a name

Hi ,

Say i have 4 columns in an excel sheet .the first is the input column, the second column contains multiple regex definitions, the third column contains a predefined string and the fourth column contains the data entered by the user in a the predefined way.

So what im trying to figure out is when a user enters in data in the first column , a macro or formula will look at all the regex expressions and match with the most relevant one. and display the entered text in the correct format.

Thanks

Please post your workbook with several examples of what you are expecting
To attach workbook
- GoAdvanced \ scroll down \ ManageAttachments \ follow screen instructions
Thanks

I feel that in order to attempt to help we need to see some examples of possible inputs and expected outputs.
Let us know if you have any questions.

The file attached to post#5 appears to be damaged (my) Excel refuses to read it
Before you re-attach it, could you add an extra field and "rank" the RegEx pattern column (from 1 to 5) based on the following criteria to allow rules to be written to allow VBA to make decisions
Clearly some of the patterns are guaranteed to yield the correct result every time if a match is found, but other (less onerous patterns) could result in a lot of "false positives"

Criteria
1 = if string matches ASSUME correct (the searched for string is too specific to be anything else)
2 = if string matches ASSUME correct (based on knowledge of data structure)
3 = if string matches PROBABLY correct (quite a specific string)
4 = if string matches PROBABLY correct (based on knowledge of data structure)
5 = everything else

Thanks

yes it's readable @sandy666 - thanks

Attached is workbook to get this going
- to make things easier to visualise everything is written to the worksheet rather than using arrays
- after testing is complete (depending on performance etc) that may need to change

Workbook contains 3 sheets - TestRegex, Workings & OriginalData

Workings:
- Note user defined function used as formula in column E
- Column C used to give me some TRUE matches to RegEx pattern

TestRegex:
Button Run RegEx Check runs macro
- checking each value in column B against each "Pattern" held in row 3
- TRUE returned if match found, blank otherwise
- column A returns count of matches found for each value
- some test data retained for you to look at before testing yourself

To test
- click on Clear Old Data
- paste values into column B
(pasting only the values will maintain the pretty blue font )

Moving forward:
There are clearly going to be multiple matches for some values, so we need some way to alow VBA to choose likely best choice
After testing with your real data you should be able to come up with a ranking structure to try to avoid some of the false positives
This is my suggestion:
Criteria
1 = if string matches ASSUME correct (the searched for string is too specific to be anything else)
2 = if string matches ASSUME correct (based on knowledge of data structure)
3 = if string matches PROBABLY correct (quite a specific string)
4 = if string matches PROBABLY correct (based on knowledge of data structure)
5 = everything else

To make it easier to develop the next bit of code, please re-attach the workbook with
- your rankings entered in column A on sheet "Workings"
AND
- a lot of typical varied data in column B on sheet "TestRegEx" (1000 rows? perhaps)
(NB file size should be below 1 meg)

Thanks for the workbook
- will look tomorrow or Sunday