help with RegEx tool

[s_das]

I am trying to separate extension numbers from the Phone No column. Attached are my input and desired output. What would be the regular expression to do this? 

Thank you for your help in advance.

[binuacs]

@s_das One way of doing this

[s_das]

Thanks so much. 

[s_das]

when extension number is more than 2 digits, how to change the formula in that case? Thanks

[binuacs]

@s_das it should work for any length of the extension number, make sure there is a space just before the extension number

[s_das]

Wanted to remove all kinds of special character from multiple fields at a time. I have done it by each field with a filter tool using "if [contains (field1),"*")]". How can I optimize 8 filter tools with regex tool and multi-field formula tool. Should I create a text file with all possible special characters and join? Thank you for your help. 

[AndrewDMerrill · Accepted solution]

Are you removing special characters from a string or removing rows that have strings with special characters in them? I'm not sure how the filter tool is helping you. For each case:

1.a) Remove special characters from a string: You can use Multi-Field Formula Tool with Regex_Replace([_CurrentField_] ,'[^a-zA-z0-9]','')

1.b) Depending on what you mean by special characters, you can use the Data Cleansing Tool as well and check "Punctuation" under "Remove Unwanted Characters"

2. Transpose Tool to get all columns of interest into the same Value column, then you can use Formula Tool to check for special characters, Summarize to find which rows should be filtered and Cross Tab to get back original data.

Hopefully this provides a general framework. More specifics will help to tailor help for your specific use-case.

[s_das]

Thanks so much. It helps me a lot to tackle unwanted characters from my variables. Appreciate.