Extracting value between two values

[Masond3]

HI All , 

I have a column Called "internal id" which has the following values ; 

Input 

Internal ID

ALT_366060CNYF_LATIN

ALT_338520CNYF_GV

ALT_GSS10690CNYF_CL

Aim : Remove ALT_ and and any character  after CNYF from the internal ID, and return the chars between those two values 

output: 

Internal ID

366060

338520

GSS1069

Regards
Masond3

[MilindG · Accepted solution]

@Masond3 

[Hammad_Rashid]

To achieve the desired output in Alteryx Designer, you can use the "Text to Columns" tool along with some text manipulation functions. Follow these steps:

1. Text to Columns:

   • Use the "Text to Columns" tool to split the "Internal ID" column based on the "_" delimiter. This will create two new columns, and you can remove the original column if needed.

2. Formula Tool:

   • Use the "Formula" tool to create a new column that removes the "ALT_" prefix and any characters after "CNYF" in the second part of the split.

   • In the Formula tool, create a new field (let's call it "Cleaned ID") with the following expression:

     plaintextCopy code

     Trim(Replace([SecondPart], 'CNYF.*', ''))

   This expression replaces everything after "CNYF" with an empty string.

3. Final Adjustment:

   • Use another "Text to Columns" tool to split the "Cleaned ID" column based on "CNYF" to get the values you want.

Here's a workflow representation:

Input Data -> Text to Columns (Delimiter: "_") -> Formula Tool -> Text to Columns (Delimiter: "CNYF")

This workflow should give you the desired output. Make sure to adjust column names and field types as needed in your specific use case.

[rohit782192]

@Masond3  The Simple way to work is use a Data Cleansing Tool and Select Letter and Punctuation.

It will solve all your problem.

[Qiu]

@Masond3 
I will do the same as @MilindG