{"id":12112,"date":"2011-12-29T20:19:45","date_gmt":"2011-12-29T20:19:45","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=1121"},"modified":"2022-08-07T00:58:03","modified_gmt":"2022-08-07T00:58:03","slug":"strip-html-how-to-remove-html-tags-from-a-string-in-vba","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2011\/12\/strip-html-how-to-remove-html-tags-from-a-string-in-vba\/","title":{"rendered":"Strip HTML : How to Remove HTML tags from a String in VBA"},"content":{"rendered":"
In this article I am going to write an User Defined Function (UDF) which is used to remove all HTMLs from a String Stored in Cell. It can be used as normal Inbuilt Formulas of Excel.<\/p>\n
For writing this function i have used VBScript Regular Expression to Replace all the HTML tags with BLANK “”. In code you can see i have defined a regular expression to replace all HTML tags.<\/p>\n
Regular Pattern which can be used to replace all HTML tags is : “<[^>]+>”<\/strong><\/p>\n Follow the below Steps to create this UDF (User Defined Function): <\/p>\n 4.<\/strong> Now Go to any Cell of any Sheet of your Workbook and use this formula =stripHTML(Cell)<\/strong> REMOVE HTML TAGS FROM A STRING<\/p><\/div><\/p>\n Below function can be basically used in VBA while writing some Macro. Syntax To Use this Function in your Macro:<\/strong><\/p>\n In this article I am going to write an User Defined Function (UDF) which is used to remove all HTMLs from a String Stored in Cell. It can be used as normal Inbuilt Formulas of Excel. For writing this function i have used VBScript Regular Expression to Replace all the HTML tags with BLANK “”. […]<\/p>\n","protected":false},"author":45,"featured_media":0,"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":[],"class_list":["post-12112","post","type-post","status-publish","format-standard","hentry","category-excel-functions","category-macro","category-udf"],"yoast_head":"\n
\n1.<\/strong> Open Visual Basic Editor (Alt+ F11)
\n2.<\/strong> Open an Existing Module or add a New Module
\n3.<\/strong> Copy Paste the below Code and Save the Excel.
\n<\/p>\nFunction – Input as Cell Address<\/h3>\n
\r\nFunction StripHTML(cell As Range) As String\r\n Dim RegEx As Object\r\n Set RegEx = CreateObject(\"vbscript.regexp\")\r\n\r\n Dim sInput As String\r\n Dim sOut As String\r\n sInput = cell.Text\r\n \r\n With RegEx\r\n .Global = True\r\n .IgnoreCase = True\r\n .MultiLine = True\r\n.Pattern = \"<[^>]+>\" 'Regular Expression for HTML Tags.\r\n End With\r\n\r\n sOut = RegEx.Replace(sInput, \"\")\r\n StripHTML = sOut\r\n Set RegEx = Nothing\r\nEnd Function\r\n<\/code><\/pre>\n
\nFunction – Pass whole String with HTML<\/h3>\n
\nFor example: At some point of time you recieve a string from some resource which has HTML tags with it and you want to remove all of them and want HTML tags free String then simply use this function.<\/p>\n\r\nhtmlFreeString=stripHTML(\"withHTMLString\")\r\n<\/code><\/pre>\n
\r\nFunction StripHTML(sInput As String) As String\r\n Dim RegEx As Object\r\n Set RegEx = CreateObject(\"vbscript.regexp\")\r\n\r\n Dim sOut As String\r\n With RegEx\r\n .Global = True\r\n .IgnoreCase = True\r\n .MultiLine = True\r\n.Pattern = \"<[^>]+>\" 'Regular Expression for HTML Tags.\r\n End With\r\n\r\n sOut = RegEx.Replace(sInput, \"\")\r\n StripHTML = sOut\r\n Set RegEx = Nothing\r\nEnd Function\r\n<\/code><\/pre>\n<\/span>","protected":false},"excerpt":{"rendered":"