We are updating the requirements for Community registration. As of 7/21/21 all users will be required to register a phone number with their My Alteryx accounts. If you have already registered, you will be prompted on your next login to add your phone number.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

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

5 - Atom

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?

15 - Aurora

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.


Hope that helps,





19 - Altair
19 - Altair

Just for a regex answer....


 because you could have:


5 kg is smaller than 10kg




 everything until the last kg followed by a period and replace the kg. With kilogram. 

you might want (kg\.*) in case the period is not there. 





Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Please Subscribe to my youTube channel.
17 - Castor


Add to @MarqueeCrew  and @AngelosPachis , I consider the case sensitivity here and your 2nd part.

Just want to confrim


KG -->KG


17 - Castor

If you think our answers help, appreciate you would mark them as accept.😁

11 - Bolide

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.