Check for keyword in String and test if number follows

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

Common Use Cases

Accepted answers

MarqueeCrew

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:

solution.yxmd

All comments

MarqueeCrew

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:

solution.yxmd

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 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 |

Thanks again,

Fiorano

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703