{"id":12120,"date":"2012-01-19T10:39:53","date_gmt":"2012-01-19T10:39:53","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=1265"},"modified":"2022-08-06T20:21:22","modified_gmt":"2022-08-06T20:21:22","slug":"udf-to-extract-numbers-special-chars-alphabets","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/","title":{"rendered":"Excel UDF: To exctract All Numbers, Special Characters and Alphabets from a String"},"content":{"rendered":"

Sometimes you require to Extract All Numbers and Characters from a mixed String. I have written a UDF (User Defined Function) to extract them.<\/p>\n

    \n
  1. \n

    UDF Function to Extract Numbers from a String<\/a><\/h3>\n<\/li>\n
  2. \n

    UDF Function to Extract Special Characters from a String<\/a><\/h3>\n<\/li>\n
  3. \n

    UDF Function to Extract Alphabets from a String<\/a><\/h3>\n<\/li>\n<\/ol>\n

    Add the below Code in any of your Regular modules of the Excel VBA Editor. Now go to your Excel Workbook and Type this formula. It will extract All the Special Characters, Numbers, and Alphabets separately as shown below: <\/p>\n

    <\/p>\n

    \"Extract

    Extract Numbers, Strings, Special Characters<\/p><\/div>\n

    <\/p>\n

    \r\n\r\nFunction ExtractNumber(Cell As Range)\r\n\tDim ResultNum As Long\r\n\tDim ResultSpecialChar, ResultAlpha As String\r\n\tDim InputString As String\r\n\tInputString = Cell.Value\r\n\tFor i = 1 To Len(InputString)\r\n\t\tIf IsNumeric(Mid(InputString, i, 1)) = True Then\r\n\t\t\tResult = Result & Mid(InputString, i, 1)\r\n\t\t\t\r\n\t\tElseIf (Asc(Mid(InputString, i, 1)) <= 65 Or Asc(Mid(InputString, i, 1)) > 90) _\r\n\t\t\tAnd ((Asc(Mid(InputString, i, 1)) < 97 Or Asc(Mid(InputString, i, 1)) >= 122)) Then\r\n\t\t\t\r\n\t\t\tResultSpecialChar = ResultSpecialChar & Mid(InputString, i, 1)\r\n\t\tElse\r\n\t\t\tResultAlpha = ResultAlpha & Mid(InputString, i, 1)\r\n\t\tEnd If\r\n\tNext\r\n\tExtractNumber = \"Alphabets are:-  \" & ResultAlpha & \"  **  Numbers are: \" & Result & \"  **  Special Chars:\" & ResultSpecialChar\r\nEnd Function\r\n\r\n\r\n<\/code><\/pre>\n
    <\/a><\/p>\n
    If you want only Numbers to be extracted from the String then Use the below code in Module<\/h3>\n
    \r\n\r\nFunction ExtractNumber(Cell As Range)\r\n    Dim ResultNum As Long\r\n    Dim InputString As String\r\n    InputString = Cell.Value\r\n    For i = 1 To Len(InputString)\r\n        If IsNumeric(Mid(InputString, i, 1)) = True Then\r\n            ResultNum = ResultNum & Mid(InputString, i, 1)\r\n        End If\r\n    Next\r\n    ExtractNumber = ResultNum\r\nEnd Function\r\n<\/code><\/pre>\n
    <\/a><\/p>\n
    If you want only Special Characters to be extracted from the String then Use the below code in Module<\/h3>\n
    \r\n\r\nFunction ExtractSpecialChar(Cell As Range)\r\n\tDim ResultSpecialChar As String\r\n\tDim InputString As String\r\n\tInputString = Cell.Value\r\n\tFor i = 1 To Len(InputString)\r\n\t\tIf (Asc(Mid(InputString, i, 1)) <= 65 Or Asc(Mid(InputString, i, 1)) > 90) _\r\n\t\tAnd ((Asc(Mid(InputString, i, 1)) < 97 Or Asc(Mid(InputString, i, 1)) >= 122) _\r\n\t\tAnd IsNumeric(Mid(InputString, i, 1)) = False) Then\r\n\t\tResultSpecialChar = ResultSpecialChar & Mid(InputString, i, 1)\r\n\tEnd If\r\nNext\r\nExtractSpecialChar = ResultSpecialChar\r\nEnd Function\r\n<\/code><\/pre>\n
    <\/a><\/p>\n
    If you want only Alhabets to be extracted from the String then Use the below code in Module<\/h3>\n
    \r\n\r\n\r\n\r\n\r\n\r\nFunction ExtractAlphabets(Cell As Range)\r\n\tDim ResultAlphabet As String\r\n\tDim InputString As String\r\n\tInputString = Cell.Value\r\n\tFor i = 1 To Len(InputString)\r\n\t\tIf (Asc(Mid(InputString, i, 1)) >= 65 And Asc(Mid(InputString, i, 1)) <= 90) _\r\n\t\tOr ((Asc(Mid(InputString, i, 1)) >= 97 And Asc(Mid(InputString, i, 1)) <= 122)) Then\r\n\t\tResultAlphabet = ResultAlphabet & Mid(InputString, i, 1)\r\n\tEnd If\r\nNext\r\nExtractAlphabets = ResultAlphabet\r\nEnd Function\r\n<\/code><\/pre>\n<\/span>","protected":false},"excerpt":{"rendered":"
    Sometimes you require to Extract All Numbers and Characters from a mixed String. I have written a UDF (User Defined Function) to extract them. UDF Function to Extract Numbers from a String UDF Function to Extract Special Characters from a String UDF Function to Extract Alphabets from a String Add the below Code in any […]<\/p>\n","protected":false},"author":45,"featured_media":242451,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1673,1246,2056],"tags":[],"yoast_head":"\nExcel UDF: To exctract All Numbers, Special Characters and Alphabets from a String - Let's excel in Excel<\/title>\n<meta name=\"description\" content=\"Using this UDF function, you can simply extract all the Numbers, Special Characters and Alphabets in a String, which is kept in a Cell. It accepts Cell Address as Input and returns result.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel UDF: To exctract All Numbers, Special Characters and Alphabets from a String\" \/>\n<meta property=\"og:description\" content=\"Using this UDF function, you can simply extract all the Numbers, Special Characters and Alphabets in a String, which is kept in a Cell. It accepts Cell Address as Input and returns result.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2012-01-19T10:39:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-06T20:21:22+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/01\/Extract-numbers-alphabets-special-chars-etc.-2.png\" \/>\n\t<meta property=\"og:image:width\" content=\"400\" \/>\n\t<meta property=\"og:image:height\" content=\"250\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Excel UDF: To exctract All Numbers, Special Characters and Alphabets from a String\",\"datePublished\":\"2012-01-19T10:39:53+00:00\",\"dateModified\":\"2022-08-06T20:21:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/\"},\"wordCount\":168,\"commentCount\":4,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"Excel Functions\",\"Excel Macro\",\"User Defined Function\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/\",\"name\":\"Excel UDF: To exctract All Numbers, Special Characters and Alphabets from a String - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2012-01-19T10:39:53+00:00\",\"dateModified\":\"2022-08-06T20:21:22+00:00\",\"description\":\"Using this UDF function, you can simply extract all the Numbers, Special Characters and Alphabets in a String, which is kept in a Cell. It accepts Cell Address as Input and returns result.\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Functions\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-functions\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Excel UDF: To exctract All Numbers, Special Characters and Alphabets from a String\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Excel UDF: To exctract All Numbers, Special Characters and Alphabets from a String - Let's excel in Excel","description":"Using this UDF function, you can simply extract all the Numbers, Special Characters and Alphabets in a String, which is kept in a Cell. It accepts Cell Address as Input and returns result.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/","og_locale":"en_US","og_type":"article","og_title":"Excel UDF: To exctract All Numbers, Special Characters and Alphabets from a String","og_description":"Using this UDF function, you can simply extract all the Numbers, Special Characters and Alphabets in a String, which is kept in a Cell. It accepts Cell Address as Input and returns result.","og_url":"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2012-01-19T10:39:53+00:00","article_modified_time":"2022-08-06T20:21:22+00:00","og_image":[{"width":400,"height":250,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/01\/Extract-numbers-alphabets-special-chars-etc.-2.png","type":"image\/png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Excel UDF: To exctract All Numbers, Special Characters and Alphabets from a String","datePublished":"2012-01-19T10:39:53+00:00","dateModified":"2022-08-06T20:21:22+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/"},"wordCount":168,"commentCount":4,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["Excel Functions","Excel Macro","User Defined Function"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/","url":"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/","name":"Excel UDF: To exctract All Numbers, Special Characters and Alphabets from a String - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2012-01-19T10:39:53+00:00","dateModified":"2022-08-06T20:21:22+00:00","description":"Using this UDF function, you can simply extract all the Numbers, Special Characters and Alphabets in a String, which is kept in a Cell. It accepts Cell Address as Input and returns result.","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2012\/01\/udf-to-extract-numbers-special-chars-alphabets\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Functions","item":"https:\/\/vmlogger.com\/excel\/excel-functions\/"},{"@type":"ListItem","position":3,"name":"Excel UDF: To exctract All Numbers, Special Characters and Alphabets from a String"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12120"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=12120"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12120\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media\/242451"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12120"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12120"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12120"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}