Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Parse Member ID from Multi Line Text Field

RifferX
8 - Asteroid

Hi all,

 

I've tried going at this a few different ways with no luck. I am pretty raw yet with Regex and am hoping this current business need will spring my skillset further with some help from the Community here. I have also tried Text to Columns with no luck. 

 

I need to parse out the Member ID in the dummy example string below into a separate column. Whatever method that can locate and extract it I am open to, whether it's via Regex or other methods. The Member ID can be different lengths, but there is consistency with the line it is contained in and the 4 spaces prior to the number after the line text, which is "TAT Goal: 5 Days Member Details :    2931317250"

 

Here is what the Comments field looks like and I have also attached a screenshot with the desired output. I have about 26,000 rows in my dataset. Any help on this would be appreciated immensely and I am not concerned about what happens to the original Comments field. *Cannot post the text here without the system picking it up as spam, so attaching via Notepad.

15 REPLIES 15
RifferX
8 - Asteroid

Bumping this since it got buried due to spam warnings.

 

 

 

 

atcodedog05
22 - Nova
22 - Nova

Edit: Posted amended workflow below

RifferX
8 - Asteroid

Thanks @atcodedog05 but I need any Member ID, not just this one in the example. They can also be different lengths, but most are 9 digits with 4 white spaces after the colon. There is also a white space before the colon.

atcodedog05
22 - Nova
22 - Nova

Hi @RifferX 

 

Oops sorry my bad. I guess i forgot to replace number with \d+. Here is the actual workflow let me know if you face any issues.

 

Workflow:

atcodedog05_0-1641830384271.png

 

Hope this helps : )

 

RifferX
8 - Asteroid

@atcodedog05 very much appreciate your assistance. 

 

When I first ran your latest solution, I didn't return all the Member IDs. When I compared the field strings, I found that there were instances of a different line string, so I shortened the Regular Expression to just .*Member Details :\D+(\d+).* and it worked like a charm!

 

Thanks again for this, now I need to figure out the logic behind the Regex language because I was way off in what I was trying to do.

atcodedog05
22 - Nova
22 - Nova

Happy to help : ) @RifferX 

Cheers and have a nice day!

 

Helpful resource on Regex : 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-RegEx/ta-p/37689 

https://regexr.com/

RifferX
8 - Asteroid

I have a similar situation here where I am trying to pull the Provider ID info and split it out into separate columns. I'm successful at pulling just the number back, but in some instances there is also a Provider name in from of the number (but not all the time). I'm pulling the number back in one column with a Regex parse, but I need to also pull back the name into another column (if a name exists and it can be upper or lower case). 

 

Dummy data below: Keep in mind, this is just one line in the entire text cell.

Provider Details : BEST HEALTH Care DUMMY 067675131

 

I am parsing the number part with the code below and it works well.

 .*Provider Details :\D+(\d+).*

RifferX
8 - Asteroid

@atcodedog05 do you have any feedback for my new question above?

atcodedog05
22 - Nova
22 - Nova

Hi @RifferX 

 

Can you provide sample data and expected output for both of the scenarios?

 

 

Labels