Greetings all.
Hope you're all doing great.
I have a long list of names. Most of them are of the format:
1) First Name + Last Name = John Smith
2) First Name + Middle Name + Last Name = John Alan Smith
Now in this list there is another format, in which I'm mostly interested in:
- Name + Last name + an 8 digit number + country code = John Smith 13798229 UK or Bob Alan Smith 17522691 AU
Does anyone know any method on how to separate these rows that have this format from the list and put them in a different one?
Solved! Go to Solution.
You can use a REGEX_Match function in a Formula tool:
IF REGEX_Match([f1], ".*\d{8}.*")
THEN 1
ELSE 0
ENDIF
Chris
@ChrisTX hmmm, not working properly. It doesn't give me anything that ends with the 2 letter country code at the end.
All it gives me is names of this format : Bob Smith 14679523
I was looking for something like this : Bob Smith 14679523 UK
Can you post a screenshot where it doesn't work?
Here are the results showing it does find records with 2 letters at the end:
@ChrisTX If I modify it like this it should work right?
IF REGEX_Match([name], ".*\d{8}\s\u\u")
THEN 1
ELSE 0
ENDIF
the .* after the digits means "any character"
. = Any single charachter
* = zero or more
so I'm assuming that it even if it has nothing after the 8 digit number it will still take it, right?
Correct. If the "second format" you're trying to identify always has 8 digits, then you can write a REGEX that only uses that criteria. If you want a more restrictive pattern match, then update the REGEX.
The web site https://regex101.com/ is helpful to validate your REGEX.