I am embarking on a data cleaning project in one of our applications, and one of the fields I want to use in analysis for this dashboard is "Business Contact". The field in our application is open text, and we generally ask people to enter either an internal mailing list (e.g., *MailingList) or their single-sign-on ID (e.g., AB12345). Of course, not everybody follows directions so we will sometimes get just someone's name (e.g., John Smith).
What I would like to do is pull this text into two columns and disect the strings, so today I might have:
ItemID
Contact
1
AB12345
2
*MailingListOne
3
*MailingListTwo, BC12345
4
CD12345, DE12345 or *MailingListThree
5
John Smith
and I want to turn this into something like this:
ItemID
Contact
ContactType
1
AB12345
Single-Sign-On
2
*MailingListOne
Mailing List
3
*MailingListTwo
Mailing List
3
BC12345
Single-Sign-On
4
CD12345
Single-Sign-On
4
DE12345
Single-Sign-On
4
*MailingListThree
Mailing List
5
John Smith
Other
I'm facing two challenges...
First, there is no common delimiter. There might be a comma, a semi-colon, etc. Can I simply pull out any string of characters that starts with an asterisk or contains two letters followed by five numbers?
Second, once I isolate these via substring, how can I shape logic of the third column that recognizes two letters and 5 numbers as "Single Sign On"?
Thanks for any help or suggestions you can provide!
