Effortlessly (ish) Extract Email Addresses with ChatGPT in Tableau

0:00When I made my last video about ChatJPT,

0:03everyone told me that they were also using

0:04it to help

0:05them write calculations better.

0:07So I came across a use case today that I

0:09wanted to show you.

0:10And so in this video, we're going to see if

0:12ChatJPT can help us answer this problem.

0:13However, let's get stuck in.

0:15So here we are, I've got a paragraph of

0:17text in every single row.

0:19And inside of that text, I've got an email

0:21address.

0:21If you haven't noticed, you can see there's

0:24an email address pretty much in

0:25inconsistent

0:26places throughout this whole text.

0:28And the result I want is to be able to

0:31extract the email from that.

0:33So it's a separate column, and then I can

0:35work with that email to maybe go and send

0:37a message to everyone.

0:38Let's say this is from a form and people

0:39put their emails in the text field because

0:41they're happy to be responded to.

0:43You could get this in a survey by mistake

0:45and need to be able to go and either

0:46replace

0:47the emails, maybe you need to clean up the

0:49data to remove sensitive information.

0:50Whatever you're trying to do, you just need

0:53to find this email and extract it.

0:55So the problem you have here is that there

0:57are tools in Tableau that can do this.

1:00And if you don't know what they are, you

1:02would never go to Google and ask the

1:03question about

1:04how to use those tools.

1:06The simplest way you'd ask the question is

1:08to simply ask, "How can I extract this

1:10email

1:10from a paragraph of text?"

1:12And so that's what I'll go and do.

1:14And I'll first do it on Google, so, "How

1:19can I extract an email from a paragraph of

1:25text

1:26in Tableau?"

1:27Okay.

1:28And I think I've got the question right.

1:30Yep.

1:31And the first response is a Tableau

1:32community.

1:33It just shows how useful the Tableau

1:35community pages are.

1:36Google sends you to a community post where

1:39someone's asked this in June of 2022, so

1:42very

1:42recently.

1:44And what someone has done...

1:45Here we go.

1:46Yeah.

1:47I was looking for someone who just posted

1:49the answer in code.

1:49You can see that someone's actually posted

1:51that.

1:51And you can see they're using something

1:54called the regexp extract.

1:56And they're using the nth function to

1:57essentially, I think, return the first

1:59email, in essence.

2:00So you now know sort of what function you

2:01're going to use.

2:02If you've never heard of that, you might

2:05then go and copy that and put that into

2:07Tableau.

2:08And then you'll come across this, which

2:10will tell you what the regex functions do.

2:13And you can learn more about regular

2:14expressions.

2:15It actually sends you off to another page

2:17here, which is, I think, not Tableau's

2:20website.

2:20It's going to be...

2:21If I just hover over the link, what website

2:23is that?

2:23userguide.icu.

2:24So this is the official documentation for

2:28regex in the icuproject.org.

2:30And essentially, it sort of talks about

2:33text handling.

2:33Now, that's all great, but you don't

2:36necessarily have the time to go and learn

2:38regex to just

2:39implement it on this one column.

2:40You're kind of looking to do your solution

2:42really, really quickly.

2:43So what I'm going to do is we're going to

2:46go back to go...

2:48So what we're going to do is we're going to

2:49take this question, and we're going to go

2:51back to Google and just paste that in.

2:54I could help this by spelling extract

2:57correctly.

2:57How can I extract an email from a paragraph

3:00of text in Tableau?

3:01Let's not click on my face.

3:02Let's hit the enter button and put this

3:05down and see what chatgpt says.

3:07Remember, Google sent us the community post

3:09, which we scroll down, we found a solution.

3:12The solution told us that there was a

3:14function we didn't know about called regex

3:17p extract.

3:17We went to the documentation and we found

3:19the answer.

3:19So the three or four steps.

3:21So it starts giving us an answer.

3:23Now, I've noticed that some of the

3:25questions, some of these violate what's

3:27called their

3:28content policy.

3:29So if you don't think it does violate the

3:31content policy, which I don't think this

3:33does,

3:35I think I'll just go ahead and hit the

3:37feedback.

3:37But let's look and see what it says.

3:39So it's saying drag the column containing

3:41text to the columns or row, shaft and Table

3:43au,

3:43right click on the column and select

3:46extract.

3:46And then you want to select regular

3:48expression.

3:49In the regular expression dialog box, enter

3:51the regular expression for email addresses,

3:53which is typically this.

3:55And it's given us the example text.

3:57Click OK to extract the email address from

3:58the text.

3:59You can then use the extracted email

4:01address in your analysis or visualization.

4:03So let's go ahead and follow exactly what

4:04it says.

4:05So drag the column containing text to the

4:07columns or row shaft.

4:08So let's go ahead.

4:09We've already got that in here.

4:12And the next step it's saying is right

4:14click on the column and select extract.

4:16And you can basically give it regular

4:18expression.

4:19And I'm going to go ahead and grab the

4:21formula here.

4:23And we can just go back.

4:24So go ahead, right click.

4:26And it's asking us to select extract.

4:29Now that's not an option in here, okay?

4:31And it's not something you can do.

4:32And I did wonder where it was going.

4:34I thought is there like a hidden feature I

4:37've never known to exist?

4:38And it's just not there.

4:39So in this case, it's giving us a convinc

4:42ingly wrong answer.

4:43But it's also given us a formula.

4:45So maybe what we can do is we can go ahead

4:47and put that formula into the tool and find

4:50out if it's actually working or not.

4:52So let's go ahead and right click, create

4:54calculated field.

4:56And what we want to do is paste the formula

4:58.

4:58Now this is the formula it's given us.

5:01Now it's not given it to us in the context

5:03of Tableau, which is also not useful.

5:05So let's go ahead and type in regex extract

5:10.

5:10And if we paste that in there, what we'll

5:12need to do is just finish the function.

5:14Let me just make this larger so you can see

5:17this more clearly.

5:18And the way regex extract works is you need

5:20to call it with a string.

5:22And in this case, it's going to be our test

5:23field.

5:24We'll just put that there.

5:25Put a comma and then we'll put the regex

5:27itself that's being called.

5:29And now that we've done that, we can just

5:31expand this a little bit.

5:32So it's easier for everyone to see this.

5:35And the last thing we need to do is just

5:37put a speech marks around all of this

5:39because

5:39this is essentially a formula.

5:42So now that that's done, you see we have a

5:44valid calculation.

5:45So this is the formula it gave us.

5:48And if we hit apply, you can see that let's

5:51go to get calculation one and put it next

5:53to test field.

5:54And you can see that it does not work

5:56because you can see there's a bunch of null

5:57s.

5:58Now I know this doesn't work because I

6:00think sometimes what Tableau wants is it

6:02wants something

6:03called a capture group.

6:04So if I go ahead and just explain this to

6:06you, if I put a brackets in front of it, in

6:08regex, when you put a brackets around

6:10something, you're essentially describing a

6:12group of patterns

6:13that you want to capture.

6:14And so that's known as a capture group.

6:16And I seem to recall through past

6:18experience with Tableau and regex that you

6:20need the capture

6:21group for this function to work.

6:22So if I go ahead and hit apply, let's go

6:24ahead and hit apply, and you can see that

6:26has worked.

6:27This has gone ahead and got the answer.

6:30So all in all, was this a knockout

6:32performance for chatGPT?

6:34No, because I had crucial bits of

6:37information that I used to kind of get this

6:40going.

6:40It also gave me the wrong instructions as

6:43to how to do this.

6:44Now is it because my question was poor?

6:48Should I have asked it this, how can I

6:53write a formula in Tableau that extracts

6:59emails

7:01from a paragraph of text?

7:07If I just go ahead and clean up my awful

7:11spelling and hit enter, it might do a

7:16better job of

7:17this.

7:18And it might also have less complaints

7:20about the content policy.

7:21I don't know what that's about.

7:22So here we go.

7:23In Tableau, you can write a formula to

7:24extract the email address from a paragraph

7:26of text

7:26using the regex extract function.

7:28Okay.

7:29Syntax for this function is this.

7:31That's correct.

7:32Where string is a column containing text

7:34and pattern is a regular expression.

7:36For email addresses, a regular pressure for

7:38email addresses is typically, is this the

7:40same as the one it gave us before?

7:42I believe it is, yes.

7:45And to extract email address from a column

7:49called text, you would use the following

7:52formula.

7:53And it does indeed go and give us the whole

7:55formula.

7:55What it doesn't do is it doesn't add that

7:59bracket that I added.

8:01But what I'll do is I'll go ahead and add

8:03that and we'll see that this should work.

8:05You can then drag this column to column

8:06array shelf to create a new column

8:08containing the

8:09extracted email address.

8:10Now, this is a general guide on how to

8:12extract an email address from a paragraph

8:13of text

8:14using Tableau.

8:15If data has different format for email

8:16addresses, you may need to adjust regular

8:18expressions

8:18accordingly.

8:19Okay.

8:20That's useful.

8:21So if I go ahead and grab this, we'll go

8:26back to Tableau.

8:28And let me just do this.

8:30Okay.

8:31If I paste it here, you can see that it is

8:36more or less the same formula.

8:41Now this one used regexp.

8:42This one's just using regex.

8:44So have I misunderstood something?

8:46I think Tableau have like a secret...

8:52What's the word?

8:53Sometimes Tableau will have secret

8:55functions that it basically won't pull you

8:57up for using.

8:58So if I actually do this, let's just

9:00comment out this line, you'll see it will

9:02say that

9:03this is incorrect.

9:05Unknown function.

9:06So yes, this does need to actually be

9:07correct.

9:08I thought it was going to let me get away

9:09with that, but it didn't.

9:10So there you go.

9:11It does need the p.

9:12So it's interesting that it even got the

9:14function incorrect.

9:15It got the wrong function in the text.

9:18So you can see this here.

9:20And so like, you know, how close did this

9:23really get me to the answer?

9:26Bearing in mind, the top one is correct.

9:28That's the one that I sort of troubleshoot

9:31ed.

9:31And if I put the test fill there, again, it

9:34didn't have the brackets, which is really

9:36important in this particular scenario,

9:38because the capture groups, I think don't

9:39make this

9:39work.

9:40So notice itself that the core of what it

9:42wrote is actually, you know, pretty much

9:44bang

9:44on, it will do the job, you can see it's

9:47working over here.

9:48But it just shows the sort of challenge of

9:50using something like chat GPT, I think it

9:52will get you 80% of the way there.

9:54But it's really important to note that that

9:56final 20% is probably the most important

9:59part

9:59of this to make this work.

10:00So you could use it to help you diagnose

10:02what's going wrong, maybe you already know

10:04what you

10:04need to do, but you want to check it

10:06against something like this, I think

10:07absolutely fine.

10:08Now, let's say you found this formula

10:11inside of your Tableau workbook, and you

10:15wanted to

10:16understand how this work, let's start a new

10:18chat.

10:18And so let's just ask this, explain what

10:24this formula does.

10:26You don't know it's called regex, you don't

10:28know you didn't give it the context of

10:29Tableau.

10:31So let's just go ahead and see if we can

10:33figure it out.

10:33The formula is a regular expression that is

10:36used to match and validate email addresses.

10:38It's used to check whether a given string

10:40is in the proper format of an email address

10:41.

10:42Specifically, the regular expression will

10:44match any string that contains one or more

10:45characters.

10:46Okay, so it's given us a good description

10:50of what that formula does.

10:52And what it didn't do is explain it in

10:54detail.

10:55So one of the things you can do is you can

10:57ask a follow up question and say, explain

10:59that in more detail.

11:02Okay.

11:03And this is, I think, the most useful part

11:05of chat GPT.

11:06Of course, it goes, sure.

11:09And so sure, this regular expression can be

11:11broken down into several parts.

11:12And then it basically takes each part.

11:14So it's taking this first part, and it's

11:16explaining that in a lot more detail than

11:18it did before.

11:20Then it's going to the at, and then it's

11:22going to go to the next section, which is

11:24just here.

11:26And then after that, it will go to the very

11:29final section, I forgot to add the plus.

11:33But it also actually, interestingly, also

11:35talks about the period in that period that

11:36must follow the domain name.

11:38That is actually pretty important in this

11:41particular sort of example.

11:44And yeah, it's breaking it down in nothing

11:46really, really good detail.

11:48You know, I still need to kind of go away

11:50and try this for myself.

11:52What I'd recommend you do is if you were

11:54going to use this for regexes, you take

11:55this, go

11:56to something like regex 101, you paste the

11:58sample of your data, as I've already done

12:00here, and you paste that in and you see

12:02that it's actually matching the email

12:04addresses.

12:05This way, you can validate that it's doing

12:07what it's supposed to do, you can test it

12:09without sort of mucking around with real

12:11data set.

12:11And now that you've got this, you can, you

12:13know, put a capture group around it all.

12:16So let's go ahead and do that by putting

12:17brackets, as you're supposed to, it goes

12:20green because,

12:21you know, tableau's saying, sorry, regex is

12:24noticing that it's a capture group.

12:26This website, regex 101, does a really good

12:29job of explaining regex as well.

12:31So if you see, expand this, you'll see that

12:33, look, this is the first capture group and

12:35it's basically telling you that first

12:37capture group is broken down into this

12:40particular

12:40set of explanations.

12:41So you've got one matches a single

12:44character present in the list, the at sign,

12:48the next

12:49character, the full stop, and then the last

12:51one.

12:51So there's five steps.

12:52And to be fair, those are the same five

12:54that ChatGPT sort of break down.

12:56And when you select these sort of small

12:58arrows, you get these nice capabilities to

13:00basically

13:01explain what's going on.

13:02So a tool like regex 101 is also doing this

13:05kind of explanation, but it does it in a

13:07little

13:07bit more detail, almost too much detail.

13:09And it's a little bit more intimidating

13:11than using something like ChatGPT.

13:13But you know, as a use case, this is a good

13:16example of how it could help you get to an

13:18answer and how it could help you validate

13:21that what you're doing is correct.

13:23But as you've seen, it wasn't perfect.

13:25There was a couple of stumbling blocks.

13:27And man, when this technology gets better,

13:30I'm just I'm just going to be so interested

13:32to see how it's used, not just in terms of

13:34helping people do their work.

13:36But also companies must be thinking of

13:38putting this technology inside of their

13:40software to

13:41help people better understand their

13:43software as a way of working.

13:44Anyway, let me know what you thought of

13:46this video.

13:46Let me know what you think of ChatGPT and

13:48regex.

13:48How are you using it?

13:49What other creative use cases have you got?

13:51I've seen people do lots of different

13:53things from creating dummy data sets to SQL

13:55to run

13:56on their database.

13:57It's absolutely sort of tearing through

13:59software and code development everywhere.

14:02So, yeah, check out check out the videos on

14:04many other channels more advanced than this

14:06one covering how it works.

14:08And yeah, let me know what you find and

14:09what you find interesting.

14:11I'll catch you in the next one.

14:12[BLANK_AUDIO]