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