Help with Regex Replace to remove characters

TSP

Hi everyone,

I have a field called "State|City" in a spreadsheet, which I need to cleanse.

I need to make sure that other than the following symbols all other non alpha numberic symbols are removed.

The following are allowed:

" " space

"-" dash

"|" pipe

I think I need to use the regex replace tool but I'm unsure how to write the code.

The characters to be removed do not need replacing with any alternative character.

Many thanks,

Regex

Accepted answers

philipmannering

Try using the regex tool with the following setup,

Thanks,

Philip

estherb47

Hi @TSP

Your logic is great, to use the RegEx Replace formula, specifying everything that you don't want to replace.

Try this function in a Formula tool. It's just a simplified version of what you've already done.

REGEX_Replace([State|City], "[^\w\|\-\s]", "")

It says replace everything that isn't alpha-numeric, a pipe, a hyphen, or a space, with nothing.

Let me know if that helps.

Cheers!

Esther

All comments

TSP

Though trial and error I have got the following to work ...

[^"|"|"-"|" "|\w]

Could anyone tell me if that's all I need or are there any risks to using that code?

philipmannering

Try using the regex tool with the following setup,

Thanks,

Philip

NickSm

Hey @TSP ,

The most efficient way to do this might depend on how flexible / how many potential characters there are that need to be removed. Are you able to post a sample of what a couple records might look like?