Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Keep only Capitals of String

AJKENDALL95
6 - Meteoroid

Hi All,


I've been trying to do the following, with no success, for a while now.

 

I have Company Names as a string (i.e Albatross Banana Cookie) in my field Company, I want to go from the full name to an acronym that keep only the capitalised letters (i.e ABC). Any ideas if this is possible?

 

Thanks,

9 REPLIES 9
jdunkerley79
ACE Emeritus
ACE Emeritus

You can do this with a REGEX_Replace in a formula tool something like:

REGEX_Replace([CompanyName],"[^A-Z]","")

This will match and replace everything that isnt a capital letter with an empty string

MarqueeCrew
20 - Arcturus
20 - Arcturus
Regex_replace([name],"[\l\s],'',0)

That will cause all lowercase letters and spaces to be replaced by nothing. Make sure to cleanup business suffixes first (e.g. Inc).

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
MarqueeCrew
20 - Arcturus
20 - Arcturus
James,

I like your style. But, you don't have case sensitivity turned on. Is that another reason to define by characters instead?
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
jdunkerley79
ACE Emeritus
ACE Emeritus

Good point - wasnt in front of Alteryx and always forget that!

 

REGEX_Replace([CompanyName],"[^A-Z]","",0)

as @MarqueeCrew says need the ,0 to make it case sensitive

 

2704ARR
6 - Meteoroid

Just expanding on this how would I pull out only capital letters where the words surrounding it are always going to be the same i.e.

 

carAAmotobike 

carJKmotobike

carTUmotobike

 

-car and motobike will always be the same words which will neeed to be removed

-the capital letters AA, JK, TU will be different but they will be required.

 

This forms part of a wider string which also contains capital letters which I don't want to remove so the formula Regex_replace([Field],"[\l\s]",'',0) works but removes all capital letters in the whole string

 

Thanks in advance.

MarqueeCrew
20 - Arcturus
20 - Arcturus

@2704ARR ,

 

 could you please explicitly state input and expected results for each row?

 

 Cheers,

 mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
2704ARR
6 - Meteoroid

Hi, 

 

I would like to keep the capitals that are in between car and motorbike specifically ie AA, JK, TU

 

Many thanka

MarqueeCrew
20 - Arcturus
20 - Arcturus

Substring([field],3,2) would work for these examples. 

cheers,

 

 mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
2704ARR
6 - Meteoroid

Thank you.

 

What if it forms part of a larger string?

 

ie

The carAAmotobike Cycle then i would like to keep The AA Cycle

The Tree carJKmotobike Fell Down then I would like to keep The Tree JK Fell Down

A Wind carTUmotobike Came then I would like to keep  A Wind TU Came.

 

The capital letters I require are always between the words car and motorbike so preferably would like to use the formula Regex_replace([name],"[\l\s],'',0).

Labels