Extracting data using colon delimiter
I have a Notes field in a Companies table that stores line-return-separated Contact information formatted with colons to separate the names from the contact info, like this:
John Doe: Manager - firstname.lastname@example.org - 555-555-5555
Jane Doe: President - email@example.com - 555-888-8888
Jeff Doe: Assistant - firstname.lastname@example.org - 555-777-7777
I would like to make a script to loop through the Company records and loop within that loop through the list of Contacts in the Notes field, exporting their names to a Name field in a separate Contacts table and putting their contact info into another Attributes field, which I'll parse out later.
Is there a function or combination of functions that will allow me to isolate the name on the left of the colon on each line and isolate the contact info to the right of the colon??? The colon delimiter is used consistently throughout all records. Is there a way to, perhaps, find how many characters from the left the colon is located on a line and then use Left() and Right() functions to pull the data? Or is there a more elegant approach? Or is this idea a fantasy?