{"id":30693,"date":"2023-09-01T03:10:58","date_gmt":"2023-09-01T01:10:58","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=30693"},"modified":"2024-04-12T22:10:45","modified_gmt":"2024-04-12T20:10:45","slug":"special-characters-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/special-characters-in-excel\/","title":{"rendered":"Top 3 Methods to Find Special Characters in Excel"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/09\/6CA0BE54-F46C-4B32-8DAB-274E7246FDE0.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-30794\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/09\/6CA0BE54-F46C-4B32-8DAB-274E7246FDE0-1024x576.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"1024\" height=\"576\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/09\/6CA0BE54-F46C-4B32-8DAB-274E7246FDE0-1024x576.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/09\/6CA0BE54-F46C-4B32-8DAB-274E7246FDE0-300x169.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/09\/6CA0BE54-F46C-4B32-8DAB-274E7246FDE0-768x432.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/09\/6CA0BE54-F46C-4B32-8DAB-274E7246FDE0.png 1280w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>When we import large datasets from other sources to Excel, we may come across<strong> special characters such as punctuation marks, mathematical operators, currency signs, and more<\/strong>. These special characters in Excel<strong> aren&#8217;t always needed<\/strong> and can make it tricky to work with the data, like when we want to sort or organize it.<\/p>\n<p>To achieve <strong>clean data, finding and managing these special characters can be crucial<\/strong>. These characters might need to be <strong>located to ensure consistent and accurate data management<\/strong>.<\/p>\n<p>In this article, we will cover the following topics in detail &#8211;<\/p>\n<div class=\"contents\">\n<h2>Table of Contents<\/h2>\n<ul>\n<li><a href=\"#1\"><strong>Method 1 &#8211; Using Search Function<\/strong><\/a><\/li>\n<li><a href=\"#2\"><strong>Method 2 &#8211; Using User-Defined Function<\/strong><\/a><\/li>\n<li><a href=\"#3\"><strong>Method 3 &#8211; Using Power Query<\/strong><\/a><\/li>\n<\/ul>\n<\/div>\n<p>Let us explore these methods one by one!<\/p>\n<p>&nbsp;<\/p>\n<h4>Download the Excel Workbook below to follow along and understand how to find special characters in Excel \u2013<\/h4>\n<p><a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/09\/Find-Special-Character-in-Excel.xlsm\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\"><strong>download excel workbook<\/strong><\/span><span class=\"postTitle px-3\">Find-Special-Character-in-Excel.xlsm<\/span><\/a><\/p>\n<p>&nbsp;<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li><a href=\"#\" class=\"scroll-to-download\">Free Practice Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/special-characters-in-excel\/#Method_1_%E2%80%93_Using_Search_Function\" title=\"Method 1 &#8211; Using Search Function\">Method 1 &#8211; Using Search Function<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/special-characters-in-excel\/#Method_2_%E2%80%93_Using_User_Defined_Function\" title=\"Method 2 &#8211; Using User Defined Function\">Method 2 &#8211; Using User Defined Function<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/special-characters-in-excel\/#Method_3_%E2%80%93_Using_Power_Query\" title=\"Method 3 &#8211; Using Power Query\">Method 3 &#8211; Using Power Query<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/special-characters-in-excel\/#Conclusion\" title=\"Conclusion\">Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Method_1_%E2%80%93_Using_Search_Function\"><\/span><strong><span id=\"1\" style=\"color: #ff0000\">Method 1 &#8211; Using Search Function<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>We need to use a <strong>combination of functions to check the presence of special characters<\/strong> in Excel. In this example, we will be using <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/sumproduct-in-excel\/\" target=\"_blank\"><strong>SUMPRODUCT<\/strong><\/a>, <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/isnumber-formula-in-excel\/\" target=\"_blank\"><strong>ISNUMBER<\/strong><\/a>, and <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/search-formula-in-excel\/\" target=\"_blank\"><strong>SEARCH<\/strong><\/a> functions to achieve the desired result.<\/p>\n<p>In this example, we have a<strong> list of employee IDs that need to be cleaned<\/strong>. Employee IDs should<strong> only contain alphabets, numbers<\/strong>. We need to check which of the employee IDs are correct and which are not. Incorrect IDs would contain all special characters like &#8220;#&#8221;, &#8220;-&#8220;, &#8220;&amp;&#8221;, &#8220;_&#8221;, &#8220;!&#8221;, &#8220;@&#8221;, &#8220;^&#8221;, and &#8220;$&#8221;.<\/p>\n<p>Follow the steps below to find special characters in Excel &#8211;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 1:\u00a0<\/span><\/strong> Enter the SEARCH function with two arguments &#8211;<\/p>\n<ol>\n<li>The first argument is all the special characters (within quotes, separated by comma and enclosed in curly brackets)<\/li>\n<li>The second argument is the cell containing the text i.e. A2<\/li>\n<\/ol>\n<p><strong>=SEARCH({&#8220;_&#8221;,&#8221;&amp;&#8221;,&#8221;!&#8221;,&#8221;#&#8221;,&#8221;$&#8221;,&#8221;%&#8221;,&#8221;(&#8220;,&#8221;)&#8221;,&#8221;^&#8221;,&#8221;@&#8221;,&#8221;[&#8220;,&#8221;]&#8221;,&#8221;{&#8220;,&#8221;}&#8221;,&#8221;-&#8220;},A2)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-23.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30735\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-23.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"1365\" height=\"587\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-23.png 1365w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-23-300x129.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-23-1024x440.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-23-768x330.png 768w\" sizes=\"(max-width: 1365px) 100vw, 1365px\" \/><\/a><\/p>\n<p>This <a class=\"wpil_keyword_link\" title=\"formula\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" data-wpil-keyword-link=\"linked\">formula<\/a> will <strong>search the content of cell A2<\/strong> for any of the specified special characters enclosed in the curly braces and <strong>return the position of the first occurrence of each character<\/strong>. If <strong>no match is found<\/strong>, it returns an <strong>error<\/strong>.<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 2:\u00a0<\/span><\/strong> Enter the <strong>ISNUMBER<\/strong> function.<\/p>\n<p><strong>=ISNUMBER(SEARCH({&#8220;_&#8221;,&#8221;&amp;&#8221;,&#8221;!&#8221;,&#8221;#&#8221;,&#8221;$&#8221;,&#8221;%&#8221;,&#8221;(&#8220;,&#8221;)&#8221;,&#8221;^&#8221;,&#8221;@&#8221;,&#8221;[&#8220;,&#8221;]&#8221;,&#8221;{&#8220;,&#8221;}&#8221;,&#8221;-&#8220;},A2))<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-24.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30736\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-24.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"1368\" height=\"598\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-24.png 1368w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-24-300x131.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-24-1024x448.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-24-768x336.png 768w\" sizes=\"(max-width: 1368px) 100vw, 1368px\" \/><\/a><\/p>\n<p>This function will return <strong>TRUE<\/strong> if a <strong>match is found (i.e., the position of the special character is a number)<\/strong>, and <strong>FALSE<\/strong> if<strong> no match is found<\/strong>.<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 3:\u00a0<\/span><\/strong> Enter the <strong>double minus sign (&#8211;)<\/strong> in front of the ISNUMBER function.<\/p>\n<p><strong>=&#8211;ISNUMBER(SEARCH({&#8220;_&#8221;,&#8221;&amp;&#8221;,&#8221;!&#8221;,&#8221;#&#8221;,&#8221;$&#8221;,&#8221;%&#8221;,&#8221;(&#8220;,&#8221;)&#8221;,&#8221;^&#8221;,&#8221;@&#8221;,&#8221;[&#8220;,&#8221;]&#8221;,&#8221;{&#8220;,&#8221;}&#8221;,&#8221;-&#8220;},A2))<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-25.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30737\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-25.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"1374\" height=\"583\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-25.png 1374w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-25-300x127.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-25-1024x434.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-25-768x326.png 768w\" sizes=\"(max-width: 1374px) 100vw, 1374px\" \/><\/a><\/p>\n<p>This function will convert the Boolean values into 1s and 0s. So, TRUE becomes 1, and FALSE becomes 0.<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 4:\u00a0<\/span><\/strong> Enter the <strong>SUMPRODUCT<\/strong> function.<\/p>\n<p><strong>=SUMPRODUCT(&#8211;ISNUMBER(SEARCH({&#8220;_&#8221;,&#8221;&amp;&#8221;,&#8221;!&#8221;,&#8221;#&#8221;,&#8221;$&#8221;,&#8221;%&#8221;,&#8221;(&#8220;,&#8221;)&#8221;,&#8221;^&#8221;,&#8221;@&#8221;,&#8221;[&#8220;,&#8221;]&#8221;,&#8221;{&#8220;,&#8221;}&#8221;,&#8221;-&#8220;},A2)))<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-26.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30738\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-26.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"1005\" height=\"585\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-26.png 1005w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-26-300x175.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-26-768x447.png 768w\" sizes=\"(max-width: 1005px) 100vw, 1005px\" \/><\/a><\/p>\n<p>This function will add up all the 1s and 0s from the previous step.<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 5:\u00a0<\/span><\/strong> Enter &#8220;=0&#8221; at the end of the formula.<\/p>\n<p><strong>=SUMPRODUCT(&#8211;ISNUMBER(SEARCH({&#8220;_&#8221;,&#8221;&amp;&#8221;,&#8221;!&#8221;,&#8221;#&#8221;,&#8221;$&#8221;,&#8221;%&#8221;,&#8221;(&#8220;,&#8221;)&#8221;,&#8221;^&#8221;,&#8221;@&#8221;,&#8221;[&#8220;,&#8221;]&#8221;,&#8221;{&#8220;,&#8221;}&#8221;,&#8221;-&#8220;},A2)))=0<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-27.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30739\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-27.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"1039\" height=\"582\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-27.png 1039w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-27-300x168.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-27-1024x574.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-27-768x430.png 768w\" sizes=\"(max-width: 1039px) 100vw, 1039px\" \/><\/a><\/p>\n<p>If the <strong>summation is equal to zero<\/strong> that means there are<strong> no special characters<\/strong> in the contents of cell A2, the <strong>function will return TRUE<\/strong>. If there is <strong>at least one of the specified special characters<\/strong> found in the cell content, <strong>the function will return FALSE<\/strong>.<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 6:\u00a0<\/span><\/strong> Copy the formula down.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-28.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30740\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-28.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"1026\" height=\"592\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-28.png 1026w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-28-300x173.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-28-1024x591.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-28-768x443.png 768w\" sizes=\"(max-width: 1026px) 100vw, 1026px\" \/><\/a><\/p>\n<p>If the cell contents are free from any of the specified characters then the function will return TRUE or else it will return TRUE.<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Method_2_%E2%80%93_Using_User_Defined_Function\"><\/span><strong><span id=\"2\" style=\"color: #ff0000\">Method 2 &#8211; Using User Defined Function<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>As <strong>Excel lacks a dedicated function for locating special characters<\/strong>, we can create one to do the task. We can easily <strong>create a User Defined Function<\/strong> to find special characters in Excel by <strong>following the steps below<\/strong> &#8211;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 1:\u00a0<\/span><\/strong> Go to <strong>Developer Tab &gt; Visual Basic<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-8.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30711\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-8.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"1087\" height=\"162\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-8.png 1087w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-8-300x45.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-8-1024x153.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-8-768x114.png 768w\" sizes=\"(max-width: 1087px) 100vw, 1087px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 2:\u00a0<\/span><\/strong> Go to <strong>Insert &gt; Module<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-9.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30712\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-9.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"838\" height=\"517\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-9.png 838w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-9-300x185.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-9-768x474.png 768w\" sizes=\"(max-width: 838px) 100vw, 838px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 3:\u00a0<\/span><\/strong> Enter the <strong>code<\/strong> mentioned below &#8211;<\/p>\n<pre class=\"brush: vb; title: ; notranslate\" title=\"\">\r\nPublic Function Check(inputString As String) As String\r\nDim length As Long\r\nDim charAtIndex As String\r\nCheck = &amp;amp;amp;amp;amp;amp;quot;Correct&amp;amp;amp;amp;amp;amp;quot;\r\n\r\nFor length = 1 To Len(inputString)\r\ncharAtIndex = Mid(inputString, length, 1)\r\nIf charAtIndex Like &amp;amp;amp;amp;amp;amp;quot;[0-9a-zA-Z]&amp;amp;amp;amp;amp;amp;quot; Then\r\nElse\r\nCheck = &amp;amp;amp;amp;amp;amp;quot;Incorrect&amp;amp;amp;amp;amp;amp;quot;\r\nExit Function\r\nEnd If\r\nNext length\r\nEnd Function\r\n<\/pre>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-10.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30713\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-10.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"1593\" height=\"523\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-10.png 1593w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-10-300x98.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-10-1024x336.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-10-768x252.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-10-1536x504.png 1536w\" sizes=\"(max-width: 1593px) 100vw, 1593px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 4:\u00a0<\/span><\/strong> Press <strong>Ctrl + S<\/strong> to save the file as a macro-enabled workbook. Click <strong>No<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-11.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30714\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-11.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"706\" height=\"221\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-11.png 706w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-11-300x94.png 300w\" sizes=\"(max-width: 706px) 100vw, 706px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 5:\u00a0<\/span><\/strong> In the drop-down, select <strong>Excel Macro Enabled Workbook<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-12.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30715\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-12.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"975\" height=\"1055\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-12.png 975w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-12-277x300.png 277w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-12-946x1024.png 946w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-12-768x831.png 768w\" sizes=\"(max-width: 975px) 100vw, 975px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 6: <\/span><\/strong>Enter the <strong>Check function<\/strong> with cell <strong>A2 as the argument<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-14.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30717\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-14.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"460\" height=\"594\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-14.png 460w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-14-232x300.png 232w\" sizes=\"(max-width: 460px) 100vw, 460px\" \/><\/a><\/p>\n<p>Here, the function will display<strong> &#8220;Correct&#8221; when the contents of the cell contain only alphabet and letters<\/strong> and<strong> &#8220;Incorrect&#8221;<\/strong> when it contain<strong>s special characters<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-15.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30718\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-15.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"475\" height=\"608\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-15.png 475w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-15-234x300.png 234w\" sizes=\"(max-width: 475px) 100vw, 475px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Method_3_%E2%80%93_Using_Power_Query\"><\/span><strong><span id=\"3\" style=\"color: #ff0000\">Method 3 &#8211; Using Power Query<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Excel&#8217;s Power Query is a robust tool that lets you <strong>perform data transformations without complex <a class=\"wpil_keyword_link\" title=\"formulas\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" data-wpil-keyword-link=\"linked\">formulas<\/a> or manual work<\/strong>. It&#8217;s like a game-changer for managing data, and can also be used to<strong> find special characters<\/strong>.<\/p>\n<p>Power Query can be used to <strong>locate and <\/strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/remove-last-character-from-strings\/\" target=\"_blank\" rel=\"noopener\">remove special characters from<\/a> the contents of the cells. Follow the<strong> steps below<\/strong> to achieve the same &#8211;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 1: <\/span><\/strong>Go to Data &gt; From Table\/Range.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-16.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30719\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-16.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"1383\" height=\"169\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-16.png 1383w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-16-300x37.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-16-1024x125.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-16-768x94.png 768w\" sizes=\"(max-width: 1383px) 100vw, 1383px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 2: <\/span><\/strong>In the Create Table dialog box, select the data and press OK.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-17.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30720\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-17.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"553\" height=\"611\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-17.png 553w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-17-272x300.png 272w\" sizes=\"(max-width: 553px) 100vw, 553px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 3: <\/span><\/strong>Go to <strong>Add Column &gt; Custom Column<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-18.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30721\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-18.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"1723\" height=\"833\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-18.png 1723w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-18-300x145.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-18-1024x495.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-18-768x371.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-18-1536x743.png 1536w\" sizes=\"(max-width: 1723px) 100vw, 1723px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 4: <\/span><\/strong>In the Custom Column dialog box, enter &#8211;<\/p>\n<ul>\n<li><strong>Cleaned Employee IDs<\/strong> as New column name.<\/li>\n<li><strong>=Text.Select([Employee IDs],{&#8220;A&#8221;..&#8221;Z&#8221;,&#8221;0&#8243;..&#8221;9&#8243;})<\/strong> as Custom column formula.<\/li>\n<\/ul>\n<p>Press <strong>OK<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-19.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30729\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-19.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"869\" height=\"543\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-19.png 869w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-19-300x187.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-19-768x480.png 768w\" sizes=\"(max-width: 869px) 100vw, 869px\" \/><\/a><\/p>\n<p>A new column with cleaned data containing only alphabets and numbers will be added.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-20.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30730\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-20.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"1377\" height=\"781\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-20.png 1377w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-20-300x170.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-20-1024x581.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-20-768x436.png 768w\" sizes=\"(max-width: 1377px) 100vw, 1377px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 5: <\/span><\/strong>Go to Home &gt; Close &amp; Load.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-21.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30731\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-21.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"1376\" height=\"181\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-21.png 1376w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-21-300x39.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-21-1024x135.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-21-768x101.png 768w\" sizes=\"(max-width: 1376px) 100vw, 1376px\" \/><\/a><\/p>\n<p>The table will now be added as a new worksheet in the file.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-22.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30732\" title=\"Top 3 Methods to Find Special Characters in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-22.png\" alt=\"Top 3 Methods to Find Special Characters in Excel\" width=\"409\" height=\"589\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-22.png 409w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/Special-Characters-in-Excel-22-208x300.png 208w\" sizes=\"(max-width: 409px) 100vw, 409px\" \/><\/a><\/p>\n<p>Now, Employee IDs are free from special characters, making them suitable for sorting, filtering, and other data operations without any hindrance.<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span><strong><span style=\"color: #ff0000\">Conclusion<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>When importing large datasets into Excel, special characters like symbols and signs can appear, making data handling complex. By embracing the techniques mentioned above, data cleanliness and efficient processing can be achieved<\/p>\n<p>To address this, we can use a combination of SUMPRODUCT, ISNUMBER, and SEARCH functions to locate special characters. Another method includes creating a User-Defined Function (UDF) and the third method is to use Power Query.<\/p>\n<p><strong>Further Learning:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/turn-text-to-values-with-excel-paste-special-values\/\" target=\"_blank\" rel=\"noopener\">Turn Text To Values With Paste Special Values<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/find-errors-with-go-to-special-constants-excel\/\" target=\"_blank\" rel=\"noopener\">Find Errors with Go to Special Constants<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/offset-function-in-excel\/\" target=\"_blank\" rel=\"noopener\">How to use OFFSET Function in Excel<\/a><\/li>\n<\/ul>\n<p><a href=\"https:\/\/learn.microsoft.com\/en-us\/powerquery-m\/text-select\" target=\"_blank\" rel=\"nofollow noopener\"><strong>Click here<\/strong><\/a> to learn more about the data cleaning function in Power Query.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When we import large datasets from other sources to Excel, we may come across special characters such as punctuation marks, mathematical operators, currency signs, and more. These special characters in Excel aren&#8217;t always needed and can make it tricky to work with the data, like when we want to sort or organize it. To achieve [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":30794,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"Top 3 Methods to Find Special Characters in Excel","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[56,121,641,485,285],"tags":[1342,1341,1340],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/30693"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=30693"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/30693\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/30794"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=30693"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=30693"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=30693"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}