Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Rookie Question- How to delete extra characters

leo2017
5 - Atom

How to remove unwanted characters in  the string 1,285<sup id="cite_ref-4" class="reference"><a href="#cite_note-4">[4]</a></sup></td>

so that I get only 1285 in my cleaned data.

 

What function do I use?

 

Thanks

2 REPLIES 2
KaneG
Alteryx Alumni (Retired)

You have a couple of options.

 

The simplest to work in your situation is:

Replace(Left([Text],5),',','')

 

which will take the left 5 and then replace the comma with nothing. A more dynamic way would involve making the number part dynamic by using Findstring:

Replace(Left([Text],FindString([text],'<')),',','')

 

You could also do it with REGEX, allowing you to add in other patterns easily later. The 3 formulas that you would want are:

REGEX_Replace([text],'\[.*?\]','')  // Replaces square brackets and anything in between with nothing
REGEX_Replace([text],'[[:punct:]]','') // Replaces any punctuation with nothing
REGEX_Replace([text],'<.*?>',''// Replaces triangle brackets and anything in-between with nothing

And so nested together would be:

REGEX_Replace(
REGEX_Replace(
REGEX_Replace([text],'<.*?>','')
,'\[.*?\]','')
,'[[:punct:]]','')

 

leo2017
5 - Atom

Thank you! problem solved.

Polls
We’re dying to get your help in determining what the new profile picture frame should be this Halloween. Cast your vote and help us haunt the Community with the best spooky character.
Don’t ghost us—pick your favorite now!
Labels