RegEx Question

[XxparsegodxX]

Hi Alteryx Community!

I am currently using the following RegEx expression to extract only words. I.e. to take out groupings of words until the phrase hits a digit. (\D+)

Input: Example (Example) Text ($M) (7) 235,547 225,788 218,227 4 8

However, the RegEx doesn't work when there is an unwanted brackets number i.e. the (7)

Current Output: Assets Under Management (AUM) - average ($M) (

Desired Output: Assets Under Management (AUM) - average ($M)

I then use a Right & Length formula to extract the numbers from the cell.

Let me know if any of the above doesn't make sense and I can help clarify...thanks everyone!! (first time posting on alteryx community so super excited :D)

[estherb47]

Hi @XxparsegodxX 

Your RegEx statement is working, in that it's stopping as soon as it finds a digit, which is after the open parenthesis.

Does the rest of your data follow the same pattern? That is, numbers in brackets followed by groups of numbers? If so, I'd use a Formula tool with the following 2 expressions:

First: Regex_Replace([TextField],"\d","") to replace all of the numbers with nothing

Then: Replace([TextField],"()","") to get rid of the empty parentheses.

A Data Cleansing tool can get rid of all of the extra spaces.

I'm sure there's a more elegant solution, but my travel-addled brain isn't coming up with one at the moment.

Let me know if that helps.

Cheers!

Esther

[fmvizcaino · Accepted solution]

Hi @XxparsegodxX ,

For that, I think you can use the following regular expression: ([^\d]+)\s.*?\d

Best,

Fernando Vizcaino

[XxparsegodxX]

Hi @fmvizcaino

This is just what I was looking for! Can you explain the logic please? I'm not great with regex and would love to learn

[fmvizcaino]

Sure @XxparsegodxX !

\s.*?\d

\s.*     This means to identify an space followed by any character multiple times (in our case, it could be .{0,1} which means any character 0 or 1 time

?\d     Until it finds the first digit

Best,

Fernando Vizcaino