Acronyms in cells

Is there a way to pick out the spelled-out version of acronyms in a cell?

For instance, the below sentences are in one cell.

'Bob is a great salesman because he Always Be Closing (ABC). He has a brilliant future in sales.'

I would need Alteryx to see that 'Always Be Closing' is spelled out for the acronym 'ABC'. The acronyms will always be in parentheses and the spelled-out version should always precede the acronym in parentheses.

Thanks!

Preparation

Accepted answers

OTrieger

@jenettd
\u\l+\s\u\l+\s.*\s$\<\w+\>$

This will give you the spell out and acronym, then you can get rid of the acronym

OTrieger

(\u\l+\s\u\l+\s.*)\s$\<\w+\>$

Without the acronym

OTrieger

When using parenthesis you can define what you would like to be tokenize our of the whole code

flying008

Hi, @jenettd

FYI.

Spoiler

((?:[A-Z][a-z]+\s)+)(\([A-Z]+\))

录制_2025_02_25_11_26_18_547.gif

Txt	Full	Acronyms
such as Accounting Unit (AU).	Accounting Unit	(AU)
Bob is a great salesman because he Always Be Closing (ABC). He has a brilliant future in sales.	Always Be Closing	(ABC)

Yoshiro_Fujimori

@jenettd

If the length of acronym may vary, I would dynamically retrieve the relevant words according to its length.

I hope it helps.

Input Data

Txt

such as Accounting Unit (AU).

Bob is a great salesman because he Always Be Closing (ABC). He has a brilliant future in sales.

The North Atlantic Treaty Organization (NATO) has its roots in the Atlantic Charter.

Workflow

Each formula tool has one expression to show you how it works.

Once you are comfortable with the process, you can put them together.

Formula

There might be more elegant solution with RegEx. Please take it as an alternative solution.

Acronym = REGEX_Replace([Txt], ".*($\u+$).*", "$1") // get the acronym

WordCount = REGEX_CountMatches([Acronym], "\u") // get the length of acronym

temp = Left([Txt], FindString([Txt], [Acronym])) // get the string before the acronym

temp = ReverseString([temp]) // reverse the string

temp2 = GetWord([temp], [WordCount]) // get the word before the name

temp = Left([temp], FindString([temp], [temp2])) // get the string for the acronym

temp = ReverseString([temp]) // reverse back

temp = Trim([temp]) // trim leading/trailing space

Output Data

Acronym	Full
(AU)	Accounting Unit
(ABC)	Always Be Closing
(NATO)	North Atlantic Treaty Organization

workflow.yxmd

All comments

OTrieger

@jenettd
Will the spelled out will always start with Capital Letter?

jenettd

Yes, the spelled out will always start with a capital letter.

OTrieger

@jenettd
\u\l+\s\u\l+\s.*\s$\<\w+\>$

This will give you the spell out and acronym, then you can get rid of the acronym

OTrieger

(\u\l+\s\u\l+\s.*)\s$\<\w+\>$

Without the acronym

OTrieger

When using parenthesis you can define what you would like to be tokenize our of the whole code

jenettd

Thank you. Would I put this formula in the Regex parse tool?

OTrieger

yes

jenettd

This worked great; however, I noticed it isn't working where the acronyms have 2 characters, such as Accounting Unit (AU). It worked great on the 3-character acronyms but the 2 character ones aren't pulling. Would you mind incorporating the fix for 2 character acronyms as well? If the formula is separate from the 3 character one, that's fine too. I appreciate your help.

OTrieger

@jenettd
Ok I deleted one \s, which indicating space

(\u\l+\s\u\l+\s.*)$\<\w+\>$

The reason that it did not work was that if there are only 2 Words then it was looking for \s\s, double space. Is it possible to have an Acronym of one word?

OTrieger

As if you have a scenario for 1 word, then you will need to add one more RegEx tool and run it again with this code
(\u\l+\s)$\<\w+\>$

flying008

Hi, @jenettd

FYI.

Spoiler

((?:[A-Z][a-z]+\s)+)(\([A-Z]+\))

录制_2025_02_25_11_26_18_547.gif

Txt	Full	Acronyms
such as Accounting Unit (AU).	Accounting Unit	(AU)
Bob is a great salesman because he Always Be Closing (ABC). He has a brilliant future in sales.	Always Be Closing	(ABC)

Yoshiro_Fujimori

@jenettd

If the length of acronym may vary, I would dynamically retrieve the relevant words according to its length.

I hope it helps.

Input Data

Txt

such as Accounting Unit (AU).

Bob is a great salesman because he Always Be Closing (ABC). He has a brilliant future in sales.

The North Atlantic Treaty Organization (NATO) has its roots in the Atlantic Charter.

Workflow

Each formula tool has one expression to show you how it works.

Once you are comfortable with the process, you can put them together.

Formula

There might be more elegant solution with RegEx. Please take it as an alternative solution.

Acronym = REGEX_Replace([Txt], ".*($\u+$).*", "$1") // get the acronym

WordCount = REGEX_CountMatches([Acronym], "\u") // get the length of acronym

temp = Left([Txt], FindString([Txt], [Acronym])) // get the string before the acronym

temp = ReverseString([temp]) // reverse the string

temp2 = GetWord([temp], [WordCount]) // get the word before the name

temp = Left([temp], FindString([temp], [temp2])) // get the string for the acronym

temp = ReverseString([temp]) // reverse back

temp = Trim([temp]) // trim leading/trailing space

Output Data

Acronym	Full
(AU)	Accounting Unit
(ABC)	Always Be Closing
(NATO)	North Atlantic Treaty Organization

workflow.yxmd

jenettd

Thank you all for the solutions. You've been very helpful.

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167