Formula to use to remove certain data in a cell in desktop designer x64?

Hi Alteryx community,

I have a data set where I want to remove any information that is in the apprentices/brackets. Below is an example of the data.

Lot Status

12345 (Available)

6789 (Not Available)

2468 (Available)

1357 (Available)

111111 (Not Available)

2222222 (Not Available)

What would be the formula needed to clean up the data, so it looks like this:

Lot Status

12345

6789

2468

1357

111111

2222222

Datasets

Common Use Cases

Accepted answers

mmichaelson

Use the RegEx tool with the regular expression \s?$[^)]*$.

Explanation:

\s?: Matches zero or one space character before the opening parenthesis.
\(: Matches the literal opening parenthesis (.
[^)]*: Matches any character except the closing parenthesis ) (repeated zero or more times).
\): Matches the literal closing parenthesis ).

Setting the Output Method to Replace and leaving the Replacement Text blank will remove the matched content.

Full disclosure - I don't understand regex very well, so I typically use ChatGPT to come up with an expression that works.

Hope this helps!

Screenshot 2024-11-19 094306.png

Screenshot 2024-11-19 094646.png

All comments

DataNath

Hey @DarrenMcMahon - here's a couple of options I would suggest. First can be used if the value in parentheses will always sit at the end. Second is if they can be within the main string. For both options, it doesn't matter if there's no '(xxx)' value - as you can see from the testing '22222' row I put in.

//String function - extra at end of field

Left([Lot Status],FindString([Lot Status], '(')-1)

//RegEx - extra can be within

Trim(REGEX_Replace([Lot Status], '\((.*)\)', ''))

mmichaelson

Use the RegEx tool with the regular expression \s?$[^)]*$.

Explanation:

\s?: Matches zero or one space character before the opening parenthesis.
\(: Matches the literal opening parenthesis (.
[^)]*: Matches any character except the closing parenthesis ) (repeated zero or more times).
\): Matches the literal closing parenthesis ).

Setting the Output Method to Replace and leaving the Replacement Text blank will remove the matched content.

Full disclosure - I don't understand regex very well, so I typically use ChatGPT to come up with an expression that works.

Hope this helps!

Screenshot 2024-11-19 094306.png

Screenshot 2024-11-19 094646.png

nagakavyasri

@DarrenMcMahon Try this: REGEX_Replace([Lot Status], '(\d+)\s(.*)', '$1')

Screenshot 2024-11-19 121128.png

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703