'Cleaning' Zoom Attendance Names to use Index/Collect

paige_pj

(We cannot use Zapier Integration). So, I created a Data Shuttle to pull the information from the Zoom attendance report. However, I need to Index/Collect how we designate the entity that they are associated with based on the person that attended, and we don't get the emails of outside parties from the meeting.

People's Zoom names are different almost EVERY month. They have different ways of doing their names (e.g. Name, Client Info; Name - Client Acronym; Name | Full Client Name, on and on). Which means, my overall list of possible attendees to use for the Index/Collect could have 1 person listed 4 times, and then, the next month, it's different AGAIN.

I can separate the first name from the Last Name + extra stuff, but since the # of characters is always different as well as what they are using to divide their name from the extra stuff I can't figure out how to just get their name in order to Index/Collect.

Has anyone developed something that will clean that?

Or, is there a way to partial match that isn't a string of text? B/c I need for the Index/Collect to match the Name without all of the rest of the stuff to pull something from another sheet.

KlausNoah

Using helper columns to pull first and last names, format them as "Last, First" with LEFT, MID, and FIND, and keep it consistent for INDEX/COLLECT to work smoothly. (Change as needed for name variations.)

paige_pj

• 
  I am not sure I understand how to use MID for a formula when the character counts could be different every month. For example, I mean that it could look like this from month to month:
  

• January's List

• George Washington, Agency1 Name

• Jane Eyre | Agency2 Name

• Jennifer Simple

• John Doe | Agen.

• Andrew Johnson

• Woodrow Wilson Agency3

• Virginia Woolf ABC

• February's List

• George Washington, Acronym

• Jane Eyre, Agency2

• Jennifer Simple | Agency Name

• John D.

• Andrew Johnson Agency

• Woodrow Wilson, Agency3

• Virginia Woolf (Agency Name)

So, there is never a common divider from the Last Name to the rest of the information. Is there a way to use mid without knowing what the characters or divider would be?