Extract a specific string from a row and create a new column with it

[Bigmonki]

Hi Folks, I have a column that contains text. Within that text I wan to look for specific words, extract those words and add them to the row in a new column.

I am in your hands, only my second week of using Alteryx so still not sure of all the tools and everything. I would greatly appreciate your help.

[Kenda]

Hi @Bigmonki 

Welcome to the community!

Could you provide some specific examples of the format of the text you're trying to extract from?

[Bigmonki]

Hello🙂

Do I have a column called "Notes", within this column there is text and blank fields!

I want to search the string of text in the 'Notes' column and find specifically "QC" or "INC" and then take the QC plus the following 6 numbers, or take the INC and the following 6 numbers. Does that help?

[Kenda]

@Bigmonki 

Do you need to capture all of the instances if there are multiple within a single cell or just the first?

[DiganP · Accepted solution]

@Bigmonki I would say to use the text to column tool with a \s (space) as a delimiter then the filter tool to filter out all the records that don't have QC and INC in their name. The select tool helps us rename and deselect columns we don't need. Does this work for you?

Before DatasetAfter Results

[Bigmonki]

@BarnesK Just the first instance, minor complication is sometimes there is a gap between the QC and the number, but I think I can fix that.

🙂

[DiganP · Accepted solution]

@Bigmonki  No need to do that! You can just use the formula tool to add in that logic. You can repeat the same process for INC as well. Attached is the updated workflow. 

if [1] = 'QC' then [1]+[2] else [1] endif

[Bigmonki]

@DiganP @BarnesK

Many thanks for all your help. Digan, the workflow you attached fixed the issue and I got all the required information.

Appreciate your assistance.

[hbraunius]

@Bigmonki I'm late to the party, but here's a RegEx solution in case you want to use less tools. 

[Bigmonki]

Thank you, not too sure on RegEx and Parse etc. Being a visualiser, it helps when I see in action something that relates to work I am doing. Appreciate the help.