Skip to main content

Strip or remove html tags from text strings

If there are multiple text strings which are surrounded with the html tags, to remove all of the html tags, the methods in this article may do you a favor.


Strip or remove all simple html tags with formula

If your text strings are surrounded with some simple html tags, the MID function in Excel can help you to solve this job. The generic syntax is:

=MID(string,text_start,LEN(string)-tag_len)
  • string: the text string or cell value that you want to remove the html tags from.
  • text_start: the number of the position that the first character located which you want to use.
  • tag_len: the length of the html tags within the text string.

Please copy or enter the following formula into a blank cell:

=MID(A2,4,LEN(A2)-7)

Then, drag the fill handle down to the cells that you want to apply this formula, and all the html tags have been removed from the cells as below screenshot shown:


Explanation of the formula:

LEN(A2)-7: This LEN function is used to calculate the length of the text string in cell A2, and subtracts 7 (the number of the html tags, both the beginning and ending tags) means to get the number of characters that you want to extract which exclude the html tag. The returned value will be used as the num_chars argument within the MID function.

MID(A2,4,LEN(A2)-7): This MID function is used to extract all the characters that start at the fourth character, and the length of the string is the num-chars that returned by the LEN function.


Strip or remove some complex html tags with VBA code

If there are multiple html tags within the text string as below screenshot shown, the above formula may not work correctly, in this case, the following VBA code can help you to deal with some complex html tags in text strings.

1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following VBA code in the Module Window.

Sub RemoveTags()
'updateby Extendoffice 
    Dim xRg As Range
    Dim xCell As Range
    Dim xAddress As String
    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("please select data range", "Kutools for Excel", xAddress, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    xRg.NumberFormat = "@"
    With CreateObject("vbscript.regexp")
        .Pattern = "\<.*?\>"
        .Global = True
        For Each xCell In xRg
            xCell.Value = .Replace(xCell.Value, "")
        Next
    End With
End Sub

3. Then, press F5 key to run this code, and a prompt box is popped out, please select the cells that you want to remove the html tags, see screenshot:

4. And then, click OK button, and all of the html tags have been removed from the selected cells, see screenshot:


Relative functions used:

  • LEN:
  • The LEN function returns the number of characters in a text string.
  • MID:
  • The MID function is used to find and return a specific number of characters from the middle of given text string.

More articles:

  • Remove Line Breaks From Cells In Excel
  • This tutorial provides three formulas to help you removing line breaks (which are occurred by pressing Alt + Enter keys in a cell) from specific cells in Excel.

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations