Parsing a set of numbers from a string

Question

Hi Alteryx experts,

I am noob on using regex to parse within strings and got the following issue when developing a workflow.

My sample input is as below. I know the patterns of strings are really messy and have multiple formats.

intervention_descriptionDose Level 1 = 1 x 10e6 T cells/kg; Dose Level 2 = 3 x 10e6 T cells/kg; Dose Level 3 = 1 x 10e7 T cells/kg.9 x 10^10 vector genomes (vg) of AAV-hAADC-2 in a single dose of 200 µL bilaterally infused over 4 striatal targetsParticipant assigned to a dose level of NK cells based on when joined study. Starting dose level of NK cells 1 x 10^7 NK cells/kg given by vein on Day 0.CERE-120 5.4 x 10^11 vg

What i wanted to do is to parse out 10-related numbers from these strings. Desired output should be something like below:

intervention_descriptionOutputDose Level 1 = 1 x 10e6 T cells/kg; Dose Level 2 = 3 x 10e6 T cells/kg; Dose Level 3 = 1 x 10e7 T cells/kg.1 x 10e6，3 x 10e6，1 x 10e79 x 10^10 vector genomes (vg) of AAV-hAADC-2 in a single dose of 200 µL bilaterally infused over 4 striatal targets9 x 10^10Participant assigned to a dose level of NK cells based on when joined study. Starting dose level of NK cells 1 x 10^7 NK cells/kg given by vein on Day 0.1 x 10^7The only "common" thing i found across the strings are that my desired output is something relating to "10". So i was trying to write a syntax that has "10" in it and trying to parse out numbers around "10", not sure if this should be the right direction and how exactly the regex expression should look like. Any guidance would be really appreciated!

Thanks in advance for your help!

Best,

XO

joshbennett · Accepted Answer

Hi @apoptoticbody,

Based on your desired output, it looks like the same REGEX_Replace expression can be used for each record except for your first one starting with "Dose Level". Thus, I think this formula should do the trick:

IIF(left([input], 10) = "Dose Level", REGEX_Replace([input], "(.*)(\d+\sx\s\d+e\d)(.*)(\d+\sx\s\d+e\d)(.*)(\d+\sx\s\d+e\d)(.*)", "$2" + ", " + "$4" + ", " + "$6"), REGEX_Replace([input], "(.*)(\d+\sx\s\d+\^\d+)(.*)", "$2"))

This formula is giving me these results for your sample data:

Note: If your data to be parsed ever includes decimals (e.g., "9.5 x 10^10" for record 2, for example), you would need to tweak the syntax a bit more to accommodate.

Attached the workflow as well for your reference. Hope this helps!

PS - I would highly recommend using regexr.com when building RegEx syntax for Alteryx functions. It lets you provide sample text to be parsed and instantly highlights the character(s) specified by your expression as you build it, making it a lot easier to see what you are doing.