Remove Special Characters by Excel formula

Excel formulas are always fun, especially when newer version of excel getting lot more new formulas and Dynamic arrays.

In earlier Excels, We never had a way to concat array of strings. Newer version of excel got TEXTJOIN function.

TEXTJOIN function can concat all string as we pass as Array or Range. We will use this function to in our Remove Special Characters formula.

Look at the two tables in the screenshot below. first table shows all the characters we want to remove. Second table have two column. First column is our input text. Notice that it has couple special characters.

How to create Formula?

From the first column of second table, lets take the first cell as Input text. We need to identify the characters which aren’t matching in the first table and then concat them.

we need to split every characters of input text, so that we can match them all in our first table. see how to split characters.

MID(D5,ROW(INDIRECT(“A1:A”&LEN(D5))),1)

Read more about “How to create Array formula”

Above formula will give output like this

{“S”;”o”;”r”;”e”;” “;”w”;”a”;”s”;” “;”I”;” “;”e”;”r”;”e”;” “;”I”;” “;”s”;”a”;”w”;” “;”E”;”r”;”o”;”s”;”.”}

Now we need to match this array in first table, by using this formula

MATCH(MID(D5,ROW(INDIRECT(“A1:A”&LEN(D5))),1),’Example of text’!$B$5:$B$35,0)

it will result this

{#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;28}

Notice that input text have only one special character at last, which is Dot (.). So that last element of match is showing number as 28.

At this point wherever it says #N/A, we know that those are the characters we need to concat. here is the next piece of formulas.

N(ISERROR(MATCH(MID(D5,ROW(INDIRECT(“A1:A”&LEN(D5))),1),’Example of text’!$B$5:$B$35,0)))*ROW(INDIRECT(“A1:A”&LEN(D5)))

It will give the the position of the characters in inputtext  which we need to concat.

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;0},

Lets extract all character on these position and Join them. we will use this formula.

IFERROR(MID(D5,N(ISERROR(MATCH(MID(D5,ROW(INDIRECT(“A1:A”&LEN(D5))),1),’Example of text’!$B$5:$B$35,0)))*ROW(INDIRECT(“A1:A”&LEN(D5))),1),””)

it will result this.

{“S”;”o”;”r”;”e”;” “;”w”;”a”;”s”;” “;”I”;” “;”e”;”r”;”e”;” “;”I”;” “;”s”;”a”;”w”;” “;”E”;”r”;”o”;”s”;””}

Notice that the last element is coming as blank now. Now, we will use TEXTJOIN and will pass this array.

=TEXTJOIN(“”,FALSE,IFERROR(MID(D5,N(ISERROR(MATCH(MID(D5,ROW(INDIRECT(“A1:A”&LEN(D5))),1),’Example of text’!$B$5:$B$35,0)))*ROW(INDIRECT(“A1:A”&LEN(D5))),1),””))

Download Working File from here

RemoveSpecialCharacters.xlsx (111 downloads)

Download Quick Guide for VBA Free!!

Join Our Premium WhatsApp Group

Because learning right is important