Check for keyword in String and test if number follows

[fiorano]

HI.

I am trying to cleanse a data field and in particular get an amount into a separate field...

eg,  for Shipment Of 10048 tonnes

So,  I would need too test for 'Of' and then if the following characters are numbers then put these into a seperate field.  I guess I could just search for numbers but then if I had 3 Shipments of 10048 tonnes then things could get messy :-).

Is this possible?

Many thanks,

Fiorano

[MarqueeCrew · Accepted solution]

Inside a formula tool this will get you the numbers:

Regex_replace([text field],".*?\sof\s(\d+).*",'$1')

Specifically it finds the first numbers after the word "of".

Cheers,
Mark

[danrh]

Another option:

[fiorano]

Thanks for this - worked perfectly.

Could we expand on this???   If there are some strings where there is :

Shipment Of 10048 tonnes

Shipment Of 10048 tonnes

Shipment Of 10048 tonnes

Shipment Of 10048 tonnes x 3 

Shipment Of 10048 tonnes

Shipment Of 10048 tonnes x 5

Shipment Of 10048 tonnes (5)

Shipment Of 10048 tonnes x 5

Is there a way to get the amount after 'Of' and then also the multiplier  value of 3 or 5 in these examples?  Again often there could be some punctuation, brackets at the end of the data field so a simple case of 'taking the last numeric value' of the string may not always work... Unless the last 'part' of the string can be tested as a numeric value.

SO I would ideally have extra fields of:

10048 | 

10058 | 

10048 | 

10048 | 3

10048 | 

10048 | 5

10048 | 5

10048 | 

Thanks again,

Fiorano

[MarqueeCrew]

".*?\sof\s(\d+).*(\d*).*",'$1|$2'

Cheers,
Mark

[fiorano]

HI,  

Thanks for the the reply.   This doesn't seem to work for me.  It is only bringing back '10048 |'

Any thoughts?

Also, would it be possible to put the values into separate fields (this is my mistake in my initial request)

So

Value_QTY                                   Value_Multiplier                                     

--------                                              --------------------

10048 

10058  

10048  

10048                                                          3

10048  

10048                                                          5

10048                                                          5

10048 

Thanks again - really appreciate it.

Fiorano

[danrh · Accepted solution]

Another option:

[fiorano]

@danrh  Great stuff - this is perfect.

Thanks also to all other users who helped!

Fiorano

[fiorano]

Hi @danrh

I've just noticed a few values that are just '10048x3' 

could a formula work to extract '10048' and '3' ?

Thanks again,

Fiorano

[danrh]

Drop a Formula tool before the Record ID tool with Replace([YourData], 'x', ' ').  The formula will replace all x's with spaces, so the rest of the workflow works as is.