Macro to Replace HTML Entities with Readable Text

Add to Favorites

Excel macro that converts all HTML entities into their readable equivalent. This includes things like   and " and over 100 more.

6b6f31b96d0529422f1f70bcca0823b5.png

Sections:

The Macro

Use the Macro

Notes

The Macro

(If the macro code here isn't working, make sure to download the sample file and get the code from there.)

Sub Convert_Html_Entities()
''''''''''''''  TeachExcel.com  ''''''''''''''
'Convert HTML Entities into readable text.

Application.ScreenUpdating = False

For Each c In Selection

    'Replace the entities - one per line.
    c.Value = Replace(c.Value, """, """")
    c.Value = Replace(c.Value, "&", "&")
    c.Value = Replace(c.Value, "'", "'")
    c.Value = Replace(c.Value, "&lt;", "<")
    c.Value = Replace(c.Value, "&gt;", ">")
    c.Value = Replace(c.Value, "&nbsp;", " ")
    c.Value = Replace(c.Value, "&iexcl;", "¡")
    c.Value = Replace(c.Value, "&cent;", "¢")
    c.Value = Replace(c.Value, "&pound;", "£")
    c.Value = Replace(c.Value, "&curren;", "¤")
    c.Value = Replace(c.Value, "&yen;", "¥")
    c.Value = Replace(c.Value, "&brvbar;", "¦")
    c.Value = Replace(c.Value, "&sect;", "§")
    c.Value = Replace(c.Value, "&uml;", "¨")
    c.Value = Replace(c.Value, "&copy;", "©")
    c.Value = Replace(c.Value, "&ordf;", "ª")
    c.Value = Replace(c.Value, "&laquo;", "«")
    c.Value = Replace(c.Value, "&not;", "¬")
    c.Value = Replace(c.Value, "&reg;", "®")
    c.Value = Replace(c.Value, "&macr;", "¯")
    c.Value = Replace(c.Value, "&deg;", "°")
    c.Value = Replace(c.Value, "&plusmn;", "±")
    c.Value = Replace(c.Value, "&sup2;", "²")
    c.Value = Replace(c.Value, "&sup3;", "³")
    c.Value = Replace(c.Value, "&acute;", "´")
    c.Value = Replace(c.Value, "&micro;", "µ")
    c.Value = Replace(c.Value, "&para;", "¶")
    c.Value = Replace(c.Value, "&middot;", "·")
    c.Value = Replace(c.Value, "&cedil;", "¸")
    c.Value = Replace(c.Value, "&sup1;", "¹")
    c.Value = Replace(c.Value, "&ordm;", "º")
    c.Value = Replace(c.Value, "&raquo;", "»")
    c.Value = Replace(c.Value, "&frac14;", "¼")
    c.Value = Replace(c.Value, "&frac12;", "½")
    c.Value = Replace(c.Value, "&frac34;", "¾")
    c.Value = Replace(c.Value, "&iquest;", "¿")
    c.Value = Replace(c.Value, "&Agrave;", "À")
    c.Value = Replace(c.Value, "&Aacute;", "Á")
    c.Value = Replace(c.Value, "&Acirc;", "Â")
    c.Value = Replace(c.Value, "&Atilde;", "Ã")
    c.Value = Replace(c.Value, "&Auml;", "Ä")
    c.Value = Replace(c.Value, "&Aring;", "Å")
    c.Value = Replace(c.Value, "&AElig;", "Æ")
    c.Value = Replace(c.Value, "&Ccedil;", "Ç")
    c.Value = Replace(c.Value, "&Egrave;", "È")
    c.Value = Replace(c.Value, "&Eacute;", "É")
    c.Value = Replace(c.Value, "&Ecirc;", "Ê")
    c.Value = Replace(c.Value, "&Euml;", "Ë")
    c.Value = Replace(c.Value, "&Igrave;", "Ì")
    c.Value = Replace(c.Value, "&Iacute;", "Í")
    c.Value = Replace(c.Value, "&Icirc;", "Î")
    c.Value = Replace(c.Value, "&Iuml;", "Ï")
    c.Value = Replace(c.Value, "&ETH;", "Ð")
    c.Value = Replace(c.Value, "&Ntilde;", "Ñ")
    c.Value = Replace(c.Value, "&Ograve;", "Ò")
    c.Value = Replace(c.Value, "&Oacute;", "Ó")
    c.Value = Replace(c.Value, "&Ocirc;", "Ô")
    c.Value = Replace(c.Value, "&Otilde;", "Õ")
    c.Value = Replace(c.Value, "&Ouml;", "Ö")
    c.Value = Replace(c.Value, "&times;", "×")
    c.Value = Replace(c.Value, "&Oslash;", "Ø")
    c.Value = Replace(c.Value, "&Ugrave;", "Ù")
    c.Value = Replace(c.Value, "&Uacute;", "Ú")
    c.Value = Replace(c.Value, "&Ucirc;", "Û")
    c.Value = Replace(c.Value, "&Uuml;", "Ü")
    c.Value = Replace(c.Value, "&Yacute;", "Ý")
    c.Value = Replace(c.Value, "&THORN;", "Þ")
    c.Value = Replace(c.Value, "&szlig;", "ß")
    c.Value = Replace(c.Value, "&agrave;", "à")
    c.Value = Replace(c.Value, "&aacute;", "á")
    c.Value = Replace(c.Value, "&acirc;", "â")
    c.Value = Replace(c.Value, "&atilde;", "ã")
    c.Value = Replace(c.Value, "&auml;", "ä")
    c.Value = Replace(c.Value, "&aring;", "å")
    c.Value = Replace(c.Value, "&aelig;", "æ")
    c.Value = Replace(c.Value, "&ccedil;", "ç")
    c.Value = Replace(c.Value, "&egrave;", "è")
    c.Value = Replace(c.Value, "&eacute;", "é")
    c.Value = Replace(c.Value, "&ecirc;", "ê")
    c.Value = Replace(c.Value, "&euml;", "ë")
    c.Value = Replace(c.Value, "&igrave;", "ì")
    c.Value = Replace(c.Value, "&iacute;", "í")
    c.Value = Replace(c.Value, "&icirc;", "î")
    c.Value = Replace(c.Value, "&iuml;", "ï")
    c.Value = Replace(c.Value, "&eth;", "ð")
    c.Value = Replace(c.Value, "&ntilde;", "ñ")
    c.Value = Replace(c.Value, "&ograve;", "ò")
    c.Value = Replace(c.Value, "&oacute;", "ó")
    c.Value = Replace(c.Value, "&ocirc;", "ô")
    c.Value = Replace(c.Value, "&otilde;", "õ")
    c.Value = Replace(c.Value, "&ouml;", "ö")
    c.Value = Replace(c.Value, "&divide;", "÷")
    c.Value = Replace(c.Value, "&oslash;", "ø")
    c.Value = Replace(c.Value, "&ugrave;", "ù")
    c.Value = Replace(c.Value, "&uacute;", "ú")
    c.Value = Replace(c.Value, "&ucirc;", "û")
    c.Value = Replace(c.Value, "&uuml;", "ü")
    c.Value = Replace(c.Value, "&yacute;", "ý")
    c.Value = Replace(c.Value, "&thorn;", "þ")
    c.Value = Replace(c.Value, "&yuml;", "ÿ")
    c.Value = Replace(c.Value, "&OElig;", "Œ")
    c.Value = Replace(c.Value, "&oelig;", "œ")
    c.Value = Replace(c.Value, "&Scaron;", "Š")
    c.Value = Replace(c.Value, "&scaron;", "š")
    c.Value = Replace(c.Value, "&Yuml;", "Ÿ")
    c.Value = Replace(c.Value, "&fnof;", "ƒ")
    c.Value = Replace(c.Value, "&circ;", "ˆ")
    c.Value = Replace(c.Value, "&tilde;", "˜")
    c.Value = Replace(c.Value, "&thinsp;", "")
    c.Value = Replace(c.Value, "&zwnj;", "")
    c.Value = Replace(c.Value, "&zwj;", "")
    c.Value = Replace(c.Value, "&lrm;", "")
    c.Value = Replace(c.Value, "&rlm;", "")
    c.Value = Replace(c.Value, "&ndash;", "–")
    c.Value = Replace(c.Value, "&mdash;", "-")
    c.Value = Replace(c.Value, "&lsquo;", "'")
    c.Value = Replace(c.Value, "&rsquo;", "'")
    c.Value = Replace(c.Value, "&sbquo;", "‚")
    c.Value = Replace(c.Value, "&ldquo;", """)
    c.Value = Replace(c.Value, "&rdquo;", """)
    c.Value = Replace(c.Value, "&bdquo;", "„")
    c.Value = Replace(c.Value, "&dagger;", "†")
    c.Value = Replace(c.Value, "&Dagger;", "‡")
    c.Value = Replace(c.Value, "&bull;", "•")
    c.Value = Replace(c.Value, "&hellip;", "…")
    c.Value = Replace(c.Value, "&permil;", "‰")
    c.Value = Replace(c.Value, "&lsaquo;", "‹")
    c.Value = Replace(c.Value, "&rsaquo;", "›")
    c.Value = Replace(c.Value, "&euro;", "€")
    c.Value = Replace(c.Value, "&trade;", "™")

Next c

Application.ScreenUpdating = True

End Sub

Where to Install: Module

Use the Macro

This macro works on whatever cell you select before you run it.

If you select a single cell, it will work on that single cell.

If you select 100 cells, it will run on 100 cells.

Note: If you run this macro on a lot of rows at once, depending on the speed of your computer, it make take a long time.

Add, Edit or Remove Symbols

To add, edit or remove symbols, you want to change the lines that look like this:

c.Value = Replace(c.Value, "&nbsp;", " ")

&nbsp; is the value to replace.

" " holds the symbol that will be used to replace the above value. In this case,   will be replaced with a space.

All symbols and replacements should be placed within quotation marks.

You can add or remove lines depending on your data set or you can change existing lines.

For a good list of HTML entities, you can checkout this website.

Notes

Download the attached file to get the macro in Excel and test it out.






Similar Content on TeachExcel
test 1
: Code: Sub Convert_Html_Entities() ''''''''''''''  TeachExcel.com  '''''''''''''' 'Conver...
test 2 🙈 国
: Sub Convert_Html_Entities() ''''''''''''''  TeachExcel.com  '''''''''''''' 'Convert HTML...
Remove Spaces Between Text in Excel
Tutorial: How to remove spaces from the middle of text in Excel.  This includes removing all spaces...
Filter Results in Excel to Show Only Those that End With Specified Text or Words - AutoFilter
Macro: This free Excel macro filters data to display results that end with certain words, text, o...
Filter Data to Display the Results that Begin With Specified Text or Words in Excel - AutoFilter
Macro: This Excel macro automatically filters a set of data based on the words or text that are c...
Quickly Replace A Lot of Data in Excel
Tutorial: The Find & Replace works much the same way as Find and is located in the same place. (...



How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Tutorial Details
Downloadable Files: Excel File
Similar Content
test 1
: Code: Sub Convert_Html_Entities() ''''''''''''''  TeachExcel.com  '''''''''''''' 'Conver...
test 2 🙈 国
: Sub Convert_Html_Entities() ''''''''''''''  TeachExcel.com  '''''''''''''' 'Convert HTML...
Remove Spaces Between Text in Excel
Tutorial: How to remove spaces from the middle of text in Excel.  This includes removing all spaces...
Excel Forum