Need help with regex formula

[soncoku]

Hey everyone.

I need a formula (regex) that will find all the names that appear in this format.

Bob Alan Smith 17522691 AU

John Smith 13798229 UK

Jade Shaw 54654645345345 AR

Chriss Jaw 234322 AL

Any ideas?

[TheOC]

hi @soncoku 
I managed this with the formula:
^(.+?)\d

This takes all the values before the first number in the field.

I then split the result by space, but obviously this step is only necessary if you need to do so
Please find attached my workflow, hope this helped!

[StephaneP]

Hello @soncoku,

Here is a way of doing it.

[estherb47]

Hi @soncoku 

Are you trying to pull the name part out, or find all strings with the pattern of text followed by a number followed by a two letter code in capital letters?

You can use a RegEx Match function inside a filter tool. If the pattern below represents the whole string, then those that match the pattern will come out on the T, and the rest on the F.

Is there more in each line in your data than what you've highlighted?

Cheers!

Esther

[soncoku]

@estherb47 

@TheOC 

@StephaneP 

I apologize. I think I did a terrible job at explaining the problem.

So  I have a list that has about 20k names

Most of the names are in the format : 

FName + LName

Fname + Middle name + Lname

Fname + Lname + digits (about 8-10 digist or more)

Now in this list, I am only interested to find which names are of this format :
Bob Alan Smith 14679952 AL

or

Bob Smith 13479786 UK

So I ONLY want the names that have DIGITS after the name and 2 letter country code UK

Name digits Country Code = Bob Smith 14679753 UK

[estherb47]

@soncoku 

Thanks for the further explanation.

try this formula inside of a Filter tool: REGEX_Match([Name], "\D.+\d+\s\u{2}",0)

That will take strings that begin with a non-number, followed by a number, followed by a space, followed by a capital letter two letter code.

Let me know if that helps.

Cheers!

Esther

[TheOC]

@estherb47 
Very nice solution! I've tasked myself to learn more Regex and it still makes me want to pull my hair out haha!
I had put your solution in a workflow with the example data just to see if it worked/to learn from it, and seems to do the job perfectly!

@soncoku I've attached that below if you need

[estherb47]

@TheOC that remains one of my favorite things about community, how we can learn from each other. I have become so much better at Alteryx by serving others here in the Designer discussions, and I've learned so much in solving together.

This is working out to be a fun team effort here!!

Cheers!

Esthter

[StephaneP · Accepted solution]

And to complete the trio here is the one using the RegEx tool. 😄
The tool showing how t extract data, the second just to check if it match.

I was looking for 3 starting surname/name and 1 was optionnal.

Let's leanr RegEx !! 🙂

[TheOC]

@estherb47 
I completely agree, started Alteryx a matter of months ago, and addicted to the community!

Learning here is great and despite some people often beating me to solutions, (looking at you @atcodedog05  😉)  I think what's so good with Alteryx is there's always another way of doing things that people can learn from, so every solution is valuable.

Cheers!
Owen