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

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

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

5 REPLIES 5
AngelosPachis
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,

 

Angelos

 

 

MarqueeCrew
19 - Altair
19 - Altair

Just for a regex answer....

 

 because you could have:

 

5 kg is smaller than 10kg

 

Regex_Replace([text],".*(kg\.)",'kilogram')

 

 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. 

 

 cheers,

 

 mark

Alteryx ACE & Top Community Contributor

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

@basketoforange 

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

Just want to confrim

KG. -->KILOGRAM

KG -->KG

1213-basketoforange.PNG

Qiu
17 - Castor

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

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

 

 

arundhuti726_0-1607862220344.png

 

Or you can use simple formula to get the result.

 

arundhuti726_0-1607862504092.png

Let me know for any explanation.

 

Labels