Macro to Replace HTML Entities with Readable Text

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

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, " ", " ")

  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.

---