How to replace the end of string only if is a certain word

I have to clean a dataset but the data are messed up. For example, I want to replace the last word in a string only if it is "KG." into "KILOGRAM". I do not know how to write the regex for that.

I also wonder how to extract certain conditional data to a new column. For example, I want to extract the whole string that is a FRUIT. I want. my data to look like the following:

DATA DATA NEW DATA_FRUIT ID

255 KG. 255 KILOGRAM

FRUIT ID 978 FRUIT ID 978

119 KG 119 KG

Should I use find and replace or REGEX?

Workflow

Regex

Accepted answers

Tyro_abc

You can use 'RejEx' to get the required result. I have used an extra Row where ‘KG.’ is not the end of line, to satisfy your need.

Or you can use simple formula to get the result.

Let me know for any explanation.

rejex_.yxmd

All comments

AngelosPachis

Hi @basketoforange ,

A simple replace function I believe would be enough for this use case. You can use a conditional if statement, (for example If endswith[name of field],"KG" then Replace(...) ) to make it more precise, if you are sure that the unit will always appear after the fruit.