Extracting data using regexextract in Google Sheets

One of our tools has usage data that ends up being three items on one line. Each line of data is something like this 1. email@middlebury.edu 123.

The first number doesn’t matter. I want the email address and the last number (which indicates usage hours).

In the old days, I’d have rigged something together using LEFT, MID, RIGHT. It would have worked, but it’d be messier and more involved.

These days I am better at REGEXEXTRACT.

To get the email address out . . . =REGEXEXTRACT(A2,”[a-zA-Z0-9_.-]+@middlebury.edu”). That gets me any combination of letters or numbers AND the middlebury.edu domain.

To get the usage hours out . . . =VALUE(REGEXEXTRACT(A2,” [0-9]+”)). That gets me any number combination with a leading space. The VALUE modifier makes a number and gets rid of the space. I know there’s a way to make a non-capturing space piece there . . . but I don’t have to care because I know this other option.

I like to think of my progression with regex (and technology in general) as something like drunken boxing. Just picture me stumbling forward and somehow still making progress in strangely fluid ways. It could also be that I have been watching too many old Jackie Chan movies.

2 thoughts on “Extracting data using regexextract in Google Sheets

  1. I agree that regex is like cryptic voodoo, but when you get one that works, well King of the World.

    I hammer them hard at https://regex101.com/ since you can toss in a variety of test strings and it explains each part. I have no clue what looking forward is, so bring it on Jackie

    1. That is a great resource. I always end up confused about what regex style different things support. Google Sheets does a particular version and something else I use does some other version. Drives me a bit crazy.

Leave a Reply