Extract certain characters from string

[SH_94]

Dear Community,

I would like to ask how we can build the formula that able to extract the data as per screenshot below.

I also attached the excel file for your reference.

Many thanks in advance for the assistance

[JoaoLeiteV · Accepted solution]

Hello there,

The file you have shown can be split into 2 parts:

   1 - Strings that have spaces and can be easily broken down with a TextToColumns tool

   2 - Strings with no spacing with a pattern of having uppercase letters, a group of numbers, another upper case, and more numbers

I created a workflow where I use a Filter to separate those parts, the first one I just use a TextToColumns with a space as a delimiter, the second I used a Regex Tool using the Parse Method with the expression "([A-Z]+)(\d+)([A-Z]\d+)". This expression will get the first upper case letters, then the numbers, then the uppercase+numbers.

I used a select tool just to reorganize and rename stuff. Hope this helps!

[atcodedog05]

Nicely done @JoaoLeiteV 🙂 👍

I was not able to think through how to approach it. Splitting into 2 streams was great idea 🙂 wish I had thought of it 😅

[atcodedog05 · Accepted solution]

Hi @SH_94 

Inspired by @JoaoLeiteV 's approach here is my take on the usecase.

Workflow:

I am using contains([data]," ") to check the pattern type and extract 2 columns based on the pattern using Regex.

Hope this helps 🙂

[Vaithi]

Hi,

Is there any Document / Material to study about the regular expression (Formula)?

[JoaoLeiteV]

Regex is used in many programming languages and data analysis tools, so there's info all over the internet.

Try checking this Tool Mastery post and also Alteryx Help Documentation. Inside Alteryx Designer you can also open the Regex tool help to check some examples.

For testing purposes, I like using Regexr. There you can type in your text and check-in real-time what your expression is getting as a result.

[Vaithi]

Thanks JoaoLeiteV

[SH_94]

Dear @JoaoLeiteV ,

May i know how to build the formula below  if we have the upper and lower case or All lower case scenario?

Thank you.

[atcodedog05]

Hi @SH_94 

As you can see the highlighted option regex is case insensitive you need to uncheck to make case sensitive. You can click on the + button to find out the regex options

[JoaoLeiteV]

As @atcodedog05 showed, you can check the box inside the tool. You can also change the Regex to be [a-zA-Z] where you have [A-Z], this will consider every upper or lower case character.

Additionaly, you can use a formula tool before your regex that updates your column with a Uppercase([Field1]), so you'll only work with uppercases.