mguhlin

Google Sheets Tip – Separating Out Email Components

A short time ago, I indulged and allowed myself to facilitate a workshop for paraeducators. What a wonderful experience that was, doing something I hadn’t done in a long time–how to training on spreadsheets. I’ve often reflected at the simple fact that “how-to” training is often unnecessary these days. . .many of us just watch a YouTube video and learn that way. Of course, teachers and those in K-12 education still have a profound need to learn how to do technology related tasks in a face to face environment.

Image Source: http://goo.gl/F8cAxG

Often, though, we assume that since folks do have access to an abundance of online tutorials–printed tutorials, videos–that anyone who begins to create how-to resources must surely ask himself, “Should I do this if there’s already tons of stuff online via YouTube and WikiHow?“

The answer should be, “Yes, of course.” I have arrived at that answer through the pleasant experience of re-discovering how much fun preparing for a workshop can be. It may be that we have all the answers, but no one is asking the questions. With the Web, though, it’s so easy to share and realize that some may find your contribution worthwhile.

Below is a sample “real life” problem I am able to solve with spreadsheets. I had not played with this myself, having left those tasks to others. I hope you’ll find these resources helpful. Of course, they feature one or two of my work colleagues.

You can find the original online in the “real problems” section at my Spreadsheet Magic site. I don’t want to suggest that any of this is brilliantly original work…only that it’s my attempt at working through a few items and I hope you find it useful.

Problem – Separating Out Email Components
What if you had a list of email addresses and needed to pull out the person’s first name and last name? For example, the ECISD email address is composed of a person’s first name and last name, separated by a period. It looks like this…

juan.guhlin@ecisd.net


…where the firstname is “juan,” the last name is “guhlin.” If you have only a few of these, you can probably separate them by hand, but what if you have 50 or more?

Solution
In this solution, we’ll use several formulas involving LEFT, RIGHT, SEARCH and FIND functions in GoogleSheets. Here’s what the formulas look like and what the desired result is:

In the example above, you can see the full email address is “jill.sievers@ecisd.net.” To separate out the first name and last name, we first want to grab the Username (everything to the left of the @ sign) so we can then separate it out. The formula to grab the username is displayed in red above and looks like this:

=LEFT(C3,FIND(“@”,C3)-1)


To separate out the first name, the following formula is used and is displayed in green:

=LEFT(D3,FIND(“.”,D3)-1


To separate out the last name, the following formula is used and displayed in blue:
=RIGHT(B3,LEN(B3)-SEARCH(“.”,B3))

HOW TO CAPITALIZE and CONCATENATE
If you wanted to do a mail merge, you wouldn’t want the Firstname and Lastname to be lowercase, but rather, capitalized. Here’s how you could do it…you would include =Proper BEFORE the formulas above so that they would like this:

FirstName:=PROPER(LEFT(B3,FIND(“.”,B3)–1))

LastName:
=Proper(LEFT(D3,FIND(“.”,D3)-1)


You could put the results of that together and CONCATENATE everything:
=concatenate(C3,” “,D3)

This is what the final product would look like:

You could then take the components above and use them in a mail merge.

ONE FORMULA
Of course, you could do all the steps above using ONE formula and that would result in “Jill Sievers“

=CONCATENATE(PROPER(LEFT(B3,FIND(“.”,B3)–1)),” “,PROPER(RIGHT(B3,LEN(B3)–SEARCH(“.”,B3))))


WAIT WAIT, I HAVE A FEW HUNDRED OF THESE!
So far, we’ve worked magic on only ONE person’s email. But we can quickly duplicate the formula so that it affects more than one. Watch this short video (on YouTube) to see “Fill Down” in action:


View my Flipboard Magazine.

Everything posted on Miguel Guhlin’s blogs/wikis are his personal opinion and do not necessarily represent the views of his employer(s) or its clients. Read Full Disclosure

var _gaq = _gaq || []; _gaq.push([‘_setAccount’, ‘UA-3445626-5’]); _gaq.push([‘_setDomainName’, ‘mguhlin.org’]); _gaq.push([‘_trackPageview’]); (function() { var ga = document.createElement(‘script’); ga.type = ‘text/javascript’; ga.async = true; ga.src = (‘https:’ == document.location.protocol ? ‘https://ssl’ : ‘http://www’) + ‘.google-analytics.com/ga.js’; var s = document.getElementsByTagName(‘script’)[0]; s.parentNode.insertBefore(ga, s); })();

Discover more from Another Think Coming

Subscribe to get the latest posts sent to your email.

2 comments

Leave a comment