Extracting information from Zoom chats

So, I’ve been puzzling over this for a while. Sometimes you go to zoom meetings where people are asked to sign into the chat with some information about themselves and add in the LinkedIn URL and/or email address. This is kind of a networking thing.

I had created an Excel file that is clunky. The problem stems from the fact that when I paste the text file into Excel, I get two columns: the first column holds the time and the name of the person and the second column holds their comments, information, LinkedIn URL and email address. That second column does not fall in the same row as the name and time; it is offset down by one row. Sometimes the information in the second column actually extends over multiple rows, depending on how the person inputs their information.

I may not be explaining very well so here is a made-up example of the text file pasted into Excel:

When I tried to create formulas to extract names, LinkedIn URL and email address, if provided, the LinkedIn and email address may not fall under the same row. Creating a pivot table out of this information would generate multiple rows for some people with some rows with just name, another row with name and LinkedIn, another with name and email and yet another with name, LinkedIn and email.

All I wanted was a list of names and the corresponding LinkedIn URL and email address all in one row.

I eventually came up with something but it required the use of arrays to make the information line up in one row. Array formulas can take up a lot of memory and with over 2000 rows of text data, I find that the calculation memory bank stopped working when it came time to line up the emails.

This week I finally came up with something that got around the array formulas, but it did involve the use of VBA. Right now, I have a version that gives me the name and LinkedIn URL for anyone who fits a set of criteria I use: the criteria could be a position title, location or whatever it may be.

I’m not going to describe here how I did it because it is late and I need to get ready for bed. Technically, it is not totally done yet but I do feel I accomplished something.

My next step will be to try to incorporate the emails using the same approach. If I succeed, then I will try to set up a post describing what I did.

Then, the next project would be to try to do the same thing using Power Query.