Removing Non-Alphanumeric Characters

[gray_b]

Removing Non-Alphanumeric Characters.

Been trying to use the code in thread at Removing Non-Alphanumeric Characters (without using macros or script!)

But I cannot get anything to work

as in the code associated with "Function CleanCode" also the code "=UPPER(CLEAN(SUBSTITUTE("

I want to clean out Non Alpha Chjaracters such as
(LA73A) = LA73A
‘white’ = white
though) = through
UK’s = UKs

Any help and guidance plz

 

[kweaver]

Why doesn't it work? Are you using it as: =CleanCode(A1) for example?

 

[gray_b]

Hi

I have

A1='white'
and
B1=CleanCode(A1)

 

[kweaver]

You still haven't said why it doesn't work.

 

[gray_b]

You still haven't said why it doesn't work.

Ok - I have found the answer to my problem.

1 - My original data had row1 formatted as a 'text' row. No matter what i did nothing worked. However all the other rows were formatted as 'general'. Changed cell formatting to 'general' and it worked OK.
2 - But in copying the original code =CleanCode(A1) from cell B1, and pasting anywhere on the sheet, nothing happened. As it copies the formatting of the cells with it. In setting the copied cell to 'general' it worked OK.
3- My whole worksheet now set to 'general' cell formatting

Many thanks for assistance.

Maybe the first line of coding should have set the cells to the correct cell formatting required. But maybe due to my in experience as well.

 

[lrobbo314]

Here is a way using dynamic array formulas.

GRAY
	I	J
32	(LA73A)	LA73A
33	‘white’ dog	white dog
34	though)	though
35	UK’s	UKs
Data

Cell Formulas
Range	Formula
J32:J35	J32	=ALPHANUMONLY(I32:I35)
Dynamic array formulas.

Here are the formulas.

ALPHANUMONLY

=LAMBDA(vector,
    MAP(vector,
        LAMBDA(t,
            LET(
                x,ARRAYFROMTEXT(t),
                TRIM(CLEAN(TEXTJOIN("",,FILTER(x,(ISALPHA(x)+ISNUMBER(x+0)+(x=" "))>0))))
            )
        )
    )
)

ARRAYFROMTEXT

=LAMBDA(text,[to_rows],
    LET(
        a,text,
        n,LEN(a),
        r,MID(a,SEQUENCE(n),1),
        IF(ISOMITTED(to_rows),r,IF(to_rows,r,TRANSPOSE(r)))
    )
)

ISALPHA

=LAMBDA(text,
    LET(
        c,CODE(text),
        ((c>64)*(c<91))+((c>64)*(c<91))+((c>96)*(c<123))>0
    )
)