community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Finding only initials in words

Highlighted
Alteryx Certified Partner

How would I just filter for only initials in words? For example, if I look at a field of company names and I want to only find names such as "IBM" or "L.E.K." or "CBRE"?

Inactive User
Not applicable

Use Regex to parse out the initials. You can use something like ^.*(\u\u\u+).*$

 

Rinse and repeat for other variations, such as with full stops in the initials.

 

Magnetar
Magnetar

I'd suggest a tweak to @Inactive User suggestion to account for possible . periods... 

 

Try adding a filter tool with the following expression: REGEX_CountMatches([Field1], "([A-Z]\.*){3}",0)>=1

 

This will look for anything that has the pattern of an uppercase letter followed by an optional period at least 3 times in a row. Tested it with the following data & results:

 

NAMEFILTERED = TRUE
CBREx
Microsoft 
ABC Consultingx
L.E.K

x

T-Mobile 
JP Morgan 
IBMx
Alaska Airlines 
Alteryx 

 

Note that it includes ABC Consulting (since there are three capital letters in a row), but not JP Morgan (since there are only two in that case). Is that correct?

 

Hope that helps with some of the possible one-off situations! :)

 

Cheers,

NJ

Alteryx Certified Partner

Thank you so much for your suggestion! It was working fine...but then I noticed that some of the company names are in all caps. For example, Alaska Airlines would be ALASKA AIRLINES...so alteryx is picking up ALASKA AIRLINES as part of my search for finding only initials. Any suggestions?

Magnetar
Magnetar

Try this one instead? 

 

REGEX_CountMatches([Field1], "\b(\u\.?){3,4}\b")=1

 

Adds the word boundaries and makes the assumption that a name that is initials will only have 3 or 4 letters in it (so CBRE and IBM are strings containing initials, but ALASKA and JP Morgan are not). Won't be fool proof, because it will still say that something like BEST BUY is a match for initials... But I'm not sure you can get it more narrowed down than that.

 

Hope that helps weed out at least a few more though! :)

 

NJ

Labels