Remove Unwanted Characters from a Textstring

[DRSteele]

DRSteele submitted a new Excel article:

Remove Unwanted Characters from a Textstring - We can get rid of things like punctuation from a textstring in a cell.

Getting rid of unwanted characters in a cell's textstring can be achieved easily by creating a Table of acceptable UNICODEs and their corresponding UNICHARs. The Table (here named T_codes) can be expanded or contracted as needed. Here, we have just the usual set of English letters (26 Upper Case and 26 Lowers plus the Numerals), but it can be altered to suit other needs.

Also listed is a formula that spills down 50,000 rows to show ALL the UNICODEs. It might be more manageable to look at...

Read more about this Excel article...

 

[Peter_SSs]

Hi Don
I would just suggest instead of using TEXTJOIN & specifying a null joiner, swap to CONCAT instead.

=CONCAT(IFNA(UNICHAR(XLOOKUP(UNICODE(MID(C1,SEQUENCE(LEN(C1)),1)),T_codes[Permitted UNICODE],T_codes[Permitted UNICODE])),""))

 

[Peter_SSs]

Actually, if you are making a list of allowed characters, do you need their codes as well?

22 09 24.xlsm
	B	C
1	text string:	gfpak(&LR--Ysdf/fhV+125,Ufd*^@3+
2	reduced text string:	gfpakLRYsdffhV125Ufd3
3
4
5		Characters
6		0
7		1
8		2
9		3
10		4
11		5
12		6
13		7
14		8
15		A
16		B
17		C
18		D
19		E
20		F
21		G
22		H
23		I
24		J
25		K
26		L
27		M
28		N
29		O
30		P
31		Q
32		R
33		S
34		T
35		U
36		V
37		W
38		X
39		Y
40		a
41		b
42		c
43		d
44		e
45		f
46		g
47		h
48		i
49		j
50		k
51		l
52		m
53		n
54		o
55		p
56		q
57		r
58		s
59		t
60		u
61		v
62		w
63		x
64		y
65
Sheet3

Cell Formulas
Range	Formula
C2	C2	=LET(t,MID(C1,SEQUENCE(LEN(C1)),1),CONCAT(IF(ISNUMBER(FIND(t,CONCAT(Allowed[Characters]))),t,"")))

 

[DRSteele]

All very good observations as usual, Peter. Thanks tons!