Keep only Capitals of String

[AJKENDALL95]

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,

[jdunkerley79]

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 · Accepted solution]

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

[MarqueeCrew]

James,

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

[jdunkerley79 · Accepted solution]

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]

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]

@2704ARR ,

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

 Cheers,

 mark

[2704ARR]

Hi, 

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

Many thanka

[MarqueeCrew]

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

cheers,

 mark

[2704ARR]

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).