Is there an easier way?

[Bigmonki]

Hi All,

I have several workflows where i need to add a suffix to a field based on the description. The suffix is either the last 1, 2 or 3 digits of the description field.

So an example:

 The field name for each description is the same, but there are several occurrences, so the field PRICEPRI in the example occurs 10 times. The description "Edit price priority 1" etc. has the occurrence at the end, so I want to take the number on the end and add it to the field and make it price PRICEPRI_1

The number may be preceded by _ # . or a simple space. I have worked around the problem, but to do so I have created a formula for each individual field. 

There must be a simpler solution to achieve the same end, can anyone help.

PS. My Regex skills are extremely limited hence the reason I have avoided them in this instance.

TIA

[jacob_kahn · Accepted solution]

Hey @Bigmonki 

I built you two solutions using RegEx. Both examples with the RegEx tool are easy, so even with a limited understanding, you should be good.

Let me know if I can clarify in any way.

Please see both workflows in the attached workflow - one assumes there will never be a number in the description of the description field besides the number you want to append to the other field, while the second workflow assumes that there may be a number included.

Let me know your thoughts and please like and accept if this helps :))

J

[RobertOdera · Accepted solution]

Hi, @Bigmonki 

Try this.

Please mark as acceptable solution + like, if it works for you.

The workflow is attached.

Cheers!

[Bigmonki · Accepted solution]

@RobertOdera 

@jacob_kahn 

Hi Folks, either tool seems to work fine, until.....

I came across some fields where the description has two sets of digits and the parse sent out the first set rather than the last set on the right. So an example:

Demo 1 description#9

Demo 2 description#1

Demo 2 description#2

Here I am getting 1 or 2 and not the value on the right. To be honest, there aren't that many fields composed in this way, but sometimes there can be 60 to 70K rows of data. Is it possible to get it to find digits and just select the values on the right hand side?

If my understanding is correct, where you have ( ) this means look at all the data in the field you have chosen to parse. the \d means find anything line a digit? Not sure what the + means?

The solutions you both provided have resolved 95% of what I was wanting to do, just wished I had reached out to you earlier, could have saved myself 2 days of typing formulas 🙂

[RobertOdera · Accepted solution]

Hi, @Bigmonki 

Yes, correct.

(\d+) means:

For future, you can leverage https://regexr.com/ to break down what the regex is doing.

Looks like we just have to iterate to accommodate the extended requirement. Are there any other situations/ variations or just the one?

Cheers!

[RobertOdera · Accepted solution]

Hi, @Bigmonki 

So, before we go-get some nice elegant treatments.

Here is a brute force approach:

1. we know what we want is always on the right hand end

2. we know that it's a number prefixed to text by #, _, or a space

3. we will nest the field that we want to treat with TRIM() to remove all leading/ trailing/ duplicate white spaces

4. we know that the number will likely not be over 1000, right?

So:

1. create new field = Right(TRIM(Field we need to parse),6)

2. regex new field as before (\d+)

3. continue on as per before = Field being prefixed +'_'+ ToString(Regexed Digits Out)

Is this helpful?

Cheers.

[Bigmonki]

@RobertOdera Brilliant, thank you for the help. Trimming the end sorted it as there is never more than 3 characters I need to look at. Also appreciate the link to the Regex webpage. I am going to have to invest some time to understand parse and stuff.

Many thanks

[RobertOdera]

You're most welcome @Bigmonki !

Cheers.