2

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?

Losai
  • 329
  • 2
  • 9
  • Do you have sample data (multiple cases) with expected results? What about the non alpha-numeric characters you got in your current formula? – JvdV Mar 09 '22 at 16:54
  • Do you mean something like: `=TEXTJOIN("";1;IF(ISNUMBER(SEARCH(MID(D2;SEQUENCE(LEN(D2));1);"-./ 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"));MID(D2;SEQUENCE(LEN(D2));1);" "))`? – Rory Mar 09 '22 at 16:59
  • @Rory SEQUENCE is very cool function, but only for 365 office version, But i mean to substitute only non-alphanumeric characters, not entire cell. – Losai Mar 09 '22 at 17:16
  • @JvdV I expect this string "OSDNR$234@^55!" trim to "OSDNR 234 55 " – Losai Mar 09 '22 at 17:18
  • @JvdV in current formula we have 4 exceptions "-./ " – Losai Mar 09 '22 at 17:32

1 Answers1

8

It seems you can use the following:

enter image description here

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.

JvdV
  • 70,606
  • 8
  • 39
  • 70