'SUBSTITUTE or REPLACE all non-alphanumeric characters
This array formula (CTRL+SHIFT+ENTER) trim any non-alphanumeric characters:
{=TEXTJOIN("";1;MID(D2;ROW(INDIRECT("1:"&LEN(D2)))*IFERROR(SEARCH(MID(D2;ROW(INDIRECT("1:"&LEN(D2)));1);"-./ 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0;LEN(D2)+1);1))}
But I want to substitute/replace this non-alphanumeric characters with simple space, only with regular or array formula. Its possible?
Solution 1:[1]
It seems you can use the following:
The CSE-entered formula in B1:
=TRIM(CONCAT(IF(ISNUMBER(SEARCH(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1),"-./ 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")),MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1)," ")))
For ms365 users:
=LET(X,MID(A1,SEQUENCE(LEN(A1)),1),TRIM(CONCAT(IF(ISNUMBER(SEARCH(X,"-./ 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")),X," "))))
It will also prevent your formula from being volatile.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |