- Excel Version
- 365
- 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!
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 | ||||||||
|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | |||
| 1 | text string: | gfpak(&LR--Ysdf/fhV+125,Ufd*^@3+ | ||||||
| 2 | reduced text string: | gfpakLRYsdffhV125Ufd3 | ||||||
| 3 | ||||||||
| 4 | ||||||||
| 5 | Permitted UNICODE | Corresponding UNICHAR | all UNICHAR and UNICODES | |||||
| 6 | 48 | 0 | 1 | |||||
| 7 | 49 | 1 | 2 | |||||
| 8 | 50 | 2 | 3 | |||||
| 9 | 51 | 3 | 4 | |||||
| 10 | 52 | 4 | 5 | |||||
| 11 | 53 | 5 | 6 | |||||
| 12 | 54 | 6 | 7 | |||||
| 13 | 55 | 7 | 8 | |||||
| 14 | 56 | 8 | 9 | |||||
| 15 | 65 | A | 10 | |||||
| 16 | 66 | B | 11 | |||||
| 17 | 67 | C | 12 | |||||
| 18 | 68 | D | 13 | |||||
| 19 | 69 | E | 14 | |||||
| 20 | 70 | F | 15 | |||||
| 21 | 71 | G | 16 | |||||
| 22 | 72 | H | 17 | |||||
| 23 | 73 | I | 18 | |||||
| 24 | 74 | J | 19 | |||||
| 25 | 75 | K | 20 | |||||
| 26 | 76 | L | 21 | |||||
| 27 | 77 | M | 22 | |||||
| 28 | 78 | N | 23 | |||||
| 29 | 79 | O | 24 | |||||
| 30 | 80 | P | 25 | |||||
| 31 | 81 | Q | 26 | |||||
| 32 | 82 | R | 27 | |||||
| 33 | 83 | S | 28 | |||||
| 34 | 84 | T | 29 | |||||
| 35 | 85 | U | 30 | |||||
| 36 | 86 | V | 31 | |||||
| 37 | 87 | W | 32 | |||||
| 38 | 88 | X | ! | 33 | ||||
| 39 | 89 | Y | " | 34 | ||||
| 40 | 97 | a | # | 35 | ||||
| 41 | 98 | b | $ | 36 | ||||
| 42 | 99 | c | % | 37 | ||||
| 43 | 100 | d | & | 38 | ||||
| 44 | 101 | e | ' | 39 | ||||
| 45 | 102 | f | ( | 40 | ||||
| 46 | 103 | g | ) | 41 | ||||
| 47 | 104 | h | * | 42 | ||||
| 48 | 105 | i | + | 43 | ||||
| 49 | 106 | j | , | 44 | ||||
| 50 | 107 | k | - | 45 | ||||
| 51 | 108 | l | . | 46 | ||||
| 52 | 109 | m | / | 47 | ||||
| 53 | 110 | n | 0 | 48 | ||||
| 54 | 111 | o | 1 | 49 | ||||
| 55 | 112 | p | 2 | 50 | ||||
| 56 | 113 | q | 3 | 51 | ||||
| 57 | 114 | r | 4 | 52 | ||||
| 58 | 115 | s | 5 | 53 | ||||
| 59 | 116 | t | 6 | 54 | ||||
| 60 | 117 | u | 7 | 55 | ||||
| 61 | 118 | v | 8 | 56 | ||||
| 62 | 119 | w | 9 | 57 | ||||
| 63 | 120 | x | : | 58 | ||||
| 64 | 121 | y | ; | 59 | ||||
| 65 | < | 60 | ||||||
Punc | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2 | C2 | =TEXTJOIN("",,IFNA(UNICHAR(XLOOKUP(UNICODE(MID(C1,SEQUENCE(LEN(C1)),1)),T_codes[Permitted UNICODE],T_codes[Permitted UNICODE])),"")) |
| F6:F50005 | F6 | =UNICHAR(SEQUENCE(50000)) |
| G6:G50005 | G6 | =UNICODE(F6#) |
| D6:D64 | D6 | =UNICHAR([@[Permitted UNICODE]]) |
| Dynamic array formulas. | ||
