Missed the Q4 Fall Release Product Update? Watch the on-demand webinar for more info on the latest in Designer 24.2, Auto Insights Magic Reports, and more!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Extract data from the text

SH_94
11 - Bolide

Hi community,

 

I have set of data as below and would like to seek for assistance how to extract the data below and presneted in the desired output.

 

SH_94_0-1678618382893.png

 

 

 

Many thanks in advance

4 REPLIES 4
BS_THE_ANALYST
14 - Magnetar
14 - Magnetar

@SH_94 here's one way using RegEx:

BS_THE_ANALYST_1-1678621977668.png

 

 

All the best,
BS

LinkedIN

Bulien
danilang
19 - Altair
19 - Altair

Hi @SH_94 

 

Since the single space before the identifier is not always present, this is not possible with a Text to Column tool and possibly do-able with regex but by someone with more skill than I have.  A fairly simple, round-about way to do focus on the words immediately before the IDs, "for", "after", "remaining", etc.  Add a Record ID to your data and put the word list in a table. Use a Find Replace tool to replace the words from the list with a pipe "|".  Split on the pipe and remove the leading space from the ID if needed.  Join this back to your input data on RecordID and use a formula too to replace the separated ID fields in the input text with an empty string ("")

 

Dan

  

danilang
19 - Altair
19 - Altair

Excellent job @BS_THE_ANALYST

 

Regex is all about pattern matching and I missed the pattern.  Kudos to you.

 

Dan  

BS_THE_ANALYST
14 - Magnetar
14 - Magnetar

@danilang I actually think your approach is more user-friendly though. It's gearing individuals up to solve the problem on their own, should any issues come up further down the line. 

It's given me food for thought, thanks for that! ðŸ˜ƒ

 

All the best,
BS

LinkedIN

Bulien
Labels
Top Solution Authors