How to remove special characters and unwanted text

[ks20]

Hello Community,

I have following values in my field, I need to remove the special characters and unwanted text in script:

Field:

185458215 (185 abc)

871541543#

526542355/452153256

Unknown

+39854662215

Expected Output:

185458215

871541543

526542355

Unknown  'Discard Unknown field'

39854662215

Any assistance or direction would be greatly appreciated. Many thanks

[tresesco]

You can use keepchar() to retain only numbers. For cases like first value where you need to consider only the first part of the string, you can use subfield() with a separator space/ ' '.

[MayilVahanan · Accepted solution]

Hi

Its depends on the values, logic might be differ.. For your current example, try like below

Load *, Trim(if(SubStringCount(Numbers, '/'), SubField(Numbers, '/',1), if(SubStringCount(Numbers, '('), SubField(Numbers, '(',1), KeepChar(Numbers, 0123456789)))) as Numberss Inline [
Numbers
185458215 (185 abc)
871541543#
526542355/452153256
Unknown
+39854662215

];

[ks20]

Thank you @MayilVahanan for your prompt response. It works for me. Just a question, how does the '#' character gets discarded when we have not mentioned it in the script?

[tresesco]

It's like saying - 'Holiday (due to heat wave) for all except senior secondary'; not mentioning other students to come 🙂. Similarly, here keepchar() is telling to keep only numbers (0...9), rest would be discarded. 

[ks20]

Makes sense. Thanks @tresesco 🙂