Okay, I’m crowdsourcing a problem I’ve run into. I found the PERFECT volunteer report for Fellowship One. We check all of our volunteers in every Sunday. We keep up with our volunteers as well. If one drops out, we make their ministry assignment inactive. This way, I can run a report and pull up the names of only those who are active. Likewise, I can pull the same report of those who are inactive to see who isn’t serving anymore.

As PERFECT as this report is, it’s got one little problem. Actually, it’s big enough to make it report frustrating. I’ve sent in a request to have this enhanced, but I’m hoping that someone out there can help me before I hear back from the fine folks at Fellowship Tech. So, I figure that one of three people could help me out. If you’re one of these three types of people, then read on. If you’re not, then feel free to move along unless this just sounds absolutely intriguing.

  • You work for Fellowship Tech and you’d like to have mercy on me and pass my request to the front of the line to have it fixed before the end of the day. (Hey, it’s worth a shot, right?)
  • You’re a Fellowship One ninja and know of another report that will get me the same information (or enough to satisfy my needs).
  • You’re an Excel genius that can help me extract the information I need that is already in the report, just not in a convenient way to access.

So, I’m betting that at least one of you three are reading this post and are going to have a solution for me. Here we go:

The report is M3739E. It’s a fantastic report that spits out the names, addresses and communication information of all active or inactive volunteers for a ministry. In addition, it tells me where they serve, what their role is as well as the RLC they’re assigned to. Best of all, it shows volunteer requirements, such as when they had a background check run as well as if they’re filled out an application. It’s beautiful!

However, the problem is that this report puts all the communication information in one cell. Every phone number and email in once cell. So, if I want to run this report and quickly cut and paste 200 emails, I can’t becasue all the phone numbers are mixed in as well. At first I thought this was a simple fix as I could just do a “text to columns” split, but the contact info doesn’t appear to be separated by commas, spaces or anything else… so I can’t seem to divide the individual contacts. The only way I’ve been able to do this is manually cut and paste.

So, any advice? Is there a similar report? Anyone know an Excel trick that will do this for me? I need to get an email out to all my volunteers later this week and I totally don’t want to have someone cut and paste 300 emails.

Here is a sample of the report. The names, addresses and such have been changed, but it gives you something to play with to see if you can work your excel mojo on it.

UPDATE:

Within an hour of this post, my friend Mike Niebuhr, both a F1 Guru and obviously an excel ninja created a custom fix. So, if you’re curious about using this report, which you should becasue it is awesome, then you can use this fix as well.

Insert a new column between “communication” (column C) and “work address” (column D). Now column D is an empty column. In column D2, paste the following code:

=MID(C2,(SEARCH(“E: “,C2,1)+3),LEN(C2))

This code/function will extract the email (if there is one) from the C column and put it in column D2. Brilliant, right? To quickly add this function to all the rest of the cells in the D Column, click on Cell D2 and in the bottom right corner of the cell you’ll see a little box. When your cursor hovers over the little box, it turns to a solid black “plus sign” instead of what was a “hand” or white “plus sign.” Click on it (and hold down) and drag it all the way down Column D. This will populate the function in every cell in this column.

Thanks Mike for your help!