Rookie Question- How to delete extra characters

[leo2017]

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

[KaneG · Accepted solution]

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]

Thank you! problem solved.