Remove Unwanted Characters from a Textstring

DRSteele · Sep 15, 2022

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 · Sep 23, 2022

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

Excel Formula:

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

Peter_SSs · Sep 23, 2022

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 · Oct 15, 2022

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

Remove Unwanted Characters from a Textstring

DRSteele

Well-known Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

DRSteele

Well-known Member

Forum statistics

Share this page

Remove Unwanted Characters from a Textstring

DRSteele

Well-known Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

DRSteele

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock