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

[DarrenMcMahon]

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

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

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!

[nagakavyasri]

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