How To Filter Non Alphanumerics Characters From Your String And Its Application In Excel

0
(0)

If you are like me, I have to deal with text a lot in my work. As a programmer, I need to find and match string. Recently, a friend of mine, who is an accountant gave me a list of books’ titles. Most of them are fine. However, there are some contains strange characters and she wanted to remove such titles.

How To Filter Non Alphanumerics Characters From Your String And Its Application In Excel 9

As you can see in the picture, there titles with @ and Chinese characters. Using filters doesn’t work because there are more than 1000 rows. That was when I said to myself: “I need a tool”.

So, I made one for this specific purpose: “to help my friend remove all the strange characters in her Excel workbook”.

Here is how the tool look like:

How To Filter Non Alphanumerics Characters From Your String And Its Application In Excel 10

As you can see, you can put the original text in the source string box, adjust a few options above and click on filter. The result will be shown in the Result string box:

How To Filter Non Alphanumerics Characters From Your String And Its Application In Excel 11

As you can see that all the rows that contain strange characters are removed.

However, what if you have some special characters that is allowed to appear in the title? No worries! You can put that into the Allowed characters box and separated them by spaces.

For example, It is OK for me to have the character @ in the title, I would put @ into the Allowed characters box. In addition, that title Blog@books.de contains the character dot (.) so you need to put that into the list of allowed characters too.

Let’s see what we’ll have:

How To Filter Non Alphanumerics Characters From Your String And Its Application In Excel 12

As you can see now, the title with . and @ is accepted.

There are also two checkboxes that allowed you to filter the text further. You can decide to allow numbers or spaces in the title by checking these checkboxes.

I put the full source code for the tool here:

https://github.com/datmt/Remove-Lines-Contain-NonAlphaNumeric-Characters

If you are not a programmer, you can download the application here to start using it:

Download the .jar file

If you have any suggestions, please let me know. Thank you very much!

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.


Leave a Reply

Your email address will not be published. Required fields are marked *