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

[fiorano]

@danrh

Hi, Thanks for this.  Just tried it as per your original workflow and it only seems to bring in the multiplier value  : 10048x6 only results in '6' coming through and not the preceeding 10048?

the 10048 is now attributed to the _Null_ field...

Any thoughts ?

Thanks again,

Fiorano

[danrh]

Not sure - it's working on my end.  Can you post your workflow?

[fiorano]

Hi,

Here you go.

Thanks!

[danrh]

Gotcha.  I was assuming when you said "just '10048x3'" you meant "Shipment Of 10048x3".  My bad.  The previous workflow doesn't work because you've lost the word "of", so it doesn't know to label '10048' as a number to keep.

Does the shipment size always come before the quantity?  Are these always the first 2 numbers in this field?  If both answers are yes, you can simplify your workflow to this:

This adds in the spaces where there's an "x", then strips out all letters, punctuation, and extra spaces, then splits the data into two columns.