I spent considerable time unsuccessfully looking for solution (formula-based) to remove non-alphanumeric characters. Here is a solution that will hopefully help others with a similar problem.

- Removes characters not in the embedded valid character string (
**"0123456789abcdefghijklmnopqrstuvwxyz"** in this example formula).
- Handles any string regardless of length
- Uses ROW(INDIRECT("1:"&LEN(B2))) to generate an index to wa
- Uses the functions MID, LEN, IFERROR, FIND, ROW, INDIRECT, and LEN
- Replaces characters not in the valid character string ""

Assuming B2 has the string to be filtered, use the following ARRAY FORMULA (you must enter by holding CTRL-SHIFT-ENTER):

Rich (BB code):

`=TEXTJOIN("",1,MID(B2,ROW(INDIRECT("1:"&LEN(B2)))*IFERROR(FIND(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1),"0123456789abcdefghijklmnopqrstuvwxyz")>0,LEN(B2)+1),1))`

**Explanation:**
Assuming B2 contains

**"Hello! This is the test string"**, the formula will yield

**"ello This is the test string"**
You may add upper case to the validation string to preserve upper case or simple use LOWER of B2 if you prefer.

This utilizes Excel Array features. The

*sub-formula*,

Rich (BB code):

`ROW(INDIRECT("1:"&LEN(B2)))`

creates the array {1, 2, 3...} of indices for each character in B2. It simply gives an index value for

so we can evaluate each character in the string against our valid characters via

. As FIND returns the index (a positive value means it found the character in the validation string), that is passed back into a results array for each character in the B2 string. Values greater than zero (see >0 following the FIND function). This returns an array of logical TRUE (1) values when a character is found. For characters not found, the IFERROR returns a multiplicative value guaranteed to be larger than the string in B2. The result in the MID function indexes beyond the end of the string in B2. In this case, it always returns NULL or "".

Enjoy