Extracting Data in Cell that contains Specific Numerical Data

Question

I am trying to automate an email using an email template that I've created. The template was created using the Report Text Tool and it takes data from cells and inputs them into the template. There is a comments column, that is written manually, that contains ID #'s in each cell that I need inputted in the email that I am trying to send, however those ID's are scattered all each cell. The only thing these ID's have in common is that they all begin with 30 or 40 and have 8 digits in total.

For example, I want a portion of my email to read "Customer A consists of ID#'s 30000011, 30000022, & 40000011."  I am trying to come up with a code that reads the first 2 digits and pulls in the rest of the numbers that follow. On top of that, some customers only have 1 ID# so there wouldn't be a reason to use a comma and & to separate the ID#'s. I'm unsure if this is possible so I am reaching out to see if anyone has any suggestions for me

arbencukaj · Answer

Hi Christine,

Thank you!! I will try this out during my free time this week and will follow up to let you know if this worked.

Christine_Assaad · Answer

Hello @arbencukaj

One option could be to use Regex to get only the numerical values. Then you can use the Summarize tool to concatenate all values using comma.

After that you can count the number of commas and decide to replace the last comma (if total count is >=1) by '&' so it becomes readable when pushed to the email body.

Before writing the data to an output, you can use a select tool to get rid of extra columns.

I am not sure how your data looks like but hope the attached mock up can help.

Cheers!

Extracting Numerical data.yxmd