Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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
16 - Nebula

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

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 restart. Order shall return.
Please Subscribe to my youTube channel.
Qiu
20 - Arcturus
20 - Arcturus

@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
20 - Arcturus
20 - Arcturus

@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