Need help with regex formula
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
Solved! Go to Solution.
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
![](/skins/images/A7612391DEAF4EF69C140133EA533832/responsive_peak/images/icon_anonymous_message.png)