Remove Special Characters

[Austin]

Anyone got easy ways to make sure a text is only numbers and letters?

[Steve]

help.appsheet.com

SUBSTITUTE()

Text with replacements

[Austin]

If I use substitute I either need to know all characters I don’t want, or I have to put in 36 nested Substitutes together.

[Steve]

True!

[Bahbus]

You could maybe use EXTRACT for numbers and text as some sort of validity check, maybe?

[Bellave_Jayaram]

Is the text fixed width or variable width?

[Austin]

Currently it takes a variable width.

[Fabian_Weller · Accepted solution]

It’s very difficult to find all characters that you don’t want. Because there are many

You could also do this:

COUNT(
LIST(
MID([YourTextColumn],1,1),
MID([YourTextColumn],2,1),
MID([YourTextColumn],3,1),
MID([YourTextColumn],4,1),
MID([YourTextColumn],5,1),
MID([YourTextColumn],6,1),
MID([YourTextColumn],7,1),
MID([YourTextColumn],8,1),
MID([YourTextColumn],9,1),
MID([YourTextColumn],10,1)
)
-LIST(
"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z",
"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z",
"0","1","2","3","4","5","6","7","8","9"
)
)

If this is greater than 1, then there is a special character.
This is working for a text length up to 10. But you can expand it.

[Austin]

Whats the performance of such a formula?

[Fabian_Weller]

@Steve what do you think about performance? Will my expression be faster than a huge Substitute expression?

[Steve]

Fabian:

Will my expression be faster than a huge Substitute expression?

It depends on a variety of factors, including the length of the text values being processed, the number of values being processed, and the context in which the processing is happening (virtual column, valid_if, initial value, etc.).

My concern with you suggestion isn’t performance, but that it can’t handle values of an arbitrary length, so its applicability is limited.

[Austin]

I wouldn’t think it would be bad since it’s just a list difference and a count with some mid.

[Austin]

Would this only work for length 10 or would it work for anything less than length 10?

[Steve]

It’ll work with less than 10, but it’ll add list elements for the missing characters. To correct for that, add "" to the subtracted LIST().