• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk. If you have any questions regarding an article, please use the Article Discussion section.
DRSteele

Remove Unwanted Characters from a Textstring

Excel Version
  1. 365
  2. 2021
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 all the codes and their characters in this useful website application so that you can pick out the characters you like.

No doubt there are superior methods, but I found this one gets the job done. Feel free to comment and help us improve things!

delete punctuation.xlsx
BCDEFG
1text string:gfpak(&LR--Ysdf/fhV+125,Ufd*^@3+
2reduced text string:gfpakLRYsdffhV125Ufd3
3
4
5Permitted UNICODECorresponding UNICHARall UNICHAR and UNICODES
6480 1
74912
85023
95134
105245
115356
12546 7
135578
14568 9
1565A 10
1666B 11
1767C12
1868D 13
1969E14
2070F15
2171G16
2272H17
2373I18
2474J19
2575K20
2676L21
2777M22
2878N23
2979O24
3080P25
3181Q26
3282R27
3383S28
3484T29
3585U30
3686V31
3787W 32
3888X!33
3989Y"34
4097a#35
4198b$36
4299c%37
43100d&38
44101e'39
45102f(40
46103g)41
47104h*42
48105i+43
49106j,44
50107k-45
51108l.46
52109m/47
53110n048
54111o149
55112p250
56113q351
57114r452
58115s553
59116t654
60117u755
61118v856
62119w957
63120x:58
64121y;59
65<60
Punc
Cell Formulas
RangeFormula
C2C2=TEXTJOIN("",,IFNA(UNICHAR(XLOOKUP(UNICODE(MID(C1,SEQUENCE(LEN(C1)),1)),T_codes[Permitted UNICODE],T_codes[Permitted UNICODE])),""))
F6:F50005F6=UNICHAR(SEQUENCE(50000))
G6:G50005G6=UNICODE(F6#)
D6:D64D6=UNICHAR([@[Permitted UNICODE]])
Dynamic array formulas.
  • Like
Reactions: Tetonne
Author
DRSteele
Views
1,813
First release
Last update
Rating
5.00 star(s) 1 ratings

More Excel articles from DRSteele

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top