entfernen Sie HTML-tags aus Zelle Saiten : excel-Formel

Ich habe die Daten mit HTML-tags in excel-Tabelle wie unten: 

<b>This is test data<br>Nice
<div> Go on this is next Cell
Very goood <b>.....</b>

ALSO, im Grunde möchte ich Sie löschen oder ersetzen Sie alle html-tags mit Leerzeichen im excel-Blatt.

InformationsquelleAutor apaleja | 2013-02-05
  1. 37

    Gelten Replace All mit <*> Muster:

    entfernen Sie HTML-tags aus Zelle Saiten : excel-Formel

    Öffnen gehen Sie zum Menüband Home > Find & Select > Replace... oder einfach drücken Sie STRG+H.

    Extra Räume weiter entfernt TRIM Funktion. Viel Glück!

    • Sie können zu Replace with: einem Leerzeichen, oder etwas, oder Links, so werden die tags einfach entfernt.
    InformationsquelleAutor Peter L.
  2. 5

    Öffnen VBA in Excel (Alt +F11), klicken Sie auf den Namen des Projekts (Tabellen-Namen) im Projekt-explorer auf der rechten Seite. Einfügen -> Neues Modul. Fügen Sie die benutzerdefinierte Funktion in der von unten in das Modul-Fenster. Sparen Sie als .XLSM, die Makros zulassen.

    geben Sie die Funktion '=StripHTML(A2)' vorausgesetzt, Ihre Daten in Zelle A2. Sie können auch laden Sie ein funktionierendes Beispiel hier:

    http://jfrancisconsulting.com/how-to-strip-html-tags-in-excel/

    Function StripHTML(cell As Range) As String

Dim RegEx As Object
Set RegEx = CreateObject(“vbscript.regexp”)
Dim sInput As String
Dim sOut As String
sInput = cell.Text

sInput = Replace(sInput, “\x0D\x0A”, Chr(10))
sInput = Replace(sInput, “\x00″, Chr(10))

‘replace HTML breaks and end of paragraphs with line breaks
sInput = Replace(sInput, “</P>”, Chr(10) & Chr(10))
sInput = Replace(sInput, “<BR>”, Chr(10))

‘replace bullets with dashes
sInput = Replace(sInput, “<li>”, “-”)

‘add back all of the special characters
sInput = Replace(sInput, “&ndash;”, “–”)
sInput = Replace(sInput, “&mdash;”, “—”)
sInput = Replace(sInput, “&iexcl;”, “¡”)
sInput = Replace(sInput, “&iquest;”, “¿”)
sInput = Replace(sInput, “&quot;”, “”)
sInput = Replace(sInput, “&ldquo;”, ““”)
sInput = Replace(sInput, “&rdquo;”, “””)
sInput = Replace(sInput, “”, “‘”)
sInput = Replace(sInput, “&lsquo;”, “‘”)
sInput = Replace(sInput, “&rsquo;”, “’”)
sInput = Replace(sInput, “&laquo;”, “«”)
sInput = Replace(sInput, “&raquo;”, “»”)
sInput = Replace(sInput, “&nbsp;”, ” “)
sInput = Replace(sInput, “&amp;”, “&”)
sInput = Replace(sInput, “&cent;”, “¢”)
sInput = Replace(sInput, “&copy;”, “©”)
sInput = Replace(sInput, “&divide;”, “÷”)
sInput = Replace(sInput, “&gt;”, “>”)
sInput = Replace(sInput, “&lt;”, “<”)
sInput = Replace(sInput, “&micro;”, “µ”)
sInput = Replace(sInput, “&middot;”, “·”)
sInput = Replace(sInput, “&para;”, “¶”)
sInput = Replace(sInput, “&plusmn;”, “±”)
sInput = Replace(sInput, “&euro;”, “€”)
sInput = Replace(sInput, “&pound;”, “£”)
sInput = Replace(sInput, “&reg;”, “®”)
sInput = Replace(sInput, “&sect;”, “§”)
sInput = Replace(sInput, “&trade;”, “™”)
sInput = Replace(sInput, “&yen;”, “¥”)
sInput = Replace(sInput, “&aacute;”, “á”)
sInput = Replace(sInput, “&Aacute;”, “Á”)
sInput = Replace(sInput, “&agrave;”, “à”)
sInput = Replace(sInput, “&Agrave;”, “À”)
sInput = Replace(sInput, “&acirc;”, “â”)
sInput = Replace(sInput, “&Acirc;”, “”)
sInput = Replace(sInput, “&aring;”, “å”)
sInput = Replace(sInput, “&Aring;”, “Å”)
sInput = Replace(sInput, “&atilde;”, “ã”)
sInput = Replace(sInput, “&Atilde;”, “Ô)
sInput = Replace(sInput, “&auml;”, “ä”)
sInput = Replace(sInput, “&Auml;”, “Ä”)
sInput = Replace(sInput, “&aelig;”, “æ”)
sInput = Replace(sInput, “&AElig;”, “Æ”)
sInput = Replace(sInput, “&ccedil;”, “ç”)
sInput = Replace(sInput, “&Ccedil;”, “Ç”)
sInput = Replace(sInput, “&eacute;”, “é”)
sInput = Replace(sInput, “&Eacute;”, “É”)
sInput = Replace(sInput, “&egrave;”, “è”)
sInput = Replace(sInput, “&Egrave;”, “È”)
sInput = Replace(sInput, “&ecirc;”, “ê”)
sInput = Replace(sInput, “&Ecirc;”, “Ê”)
sInput = Replace(sInput, “&euml;”, “ë”)
sInput = Replace(sInput, “&Euml;”, “Ë”)
sInput = Replace(sInput, “&iacute;”, “í”)
sInput = Replace(sInput, “&Iacute;”, “Í”)
sInput = Replace(sInput, “&igrave;”, “ì”)
sInput = Replace(sInput, “&Igrave;”, “Ì”)
sInput = Replace(sInput, “&icirc;”, “î”)
sInput = Replace(sInput, “&Icirc;”, “Δ)
sInput = Replace(sInput, “&iuml;”, “ï”)
sInput = Replace(sInput, “&Iuml;”, “Ï”)
sInput = Replace(sInput, “&ntilde;”, “ñ”)
sInput = Replace(sInput, “&Ntilde;”, “Ñ”)
sInput = Replace(sInput, “&oacute;”, “ó”)
sInput = Replace(sInput, “&Oacute;”, “Ó”)
sInput = Replace(sInput, “&ograve;”, “ò”)
sInput = Replace(sInput, “&Ograve;”, “Ò”)
sInput = Replace(sInput, “&ocirc;”, “ô”)
sInput = Replace(sInput, “&Ocirc;”, “Ô”)
sInput = Replace(sInput, “&oslash;”, “ø”)
sInput = Replace(sInput, “&Oslash;”, “Ø”)
sInput = Replace(sInput, “&otilde;”, “õ”)
sInput = Replace(sInput, “&Otilde;”, “Õ”)
sInput = Replace(sInput, “&ouml;”, “ö”)
sInput = Replace(sInput, “&Ouml;”, “Ö”)
sInput = Replace(sInput, “&szlig;”, “ß”)
sInput = Replace(sInput, “&uacute;”, “ú”)
sInput = Replace(sInput, “&Uacute;”, “Ú”)
sInput = Replace(sInput, “&ugrave;”, “ù”)
sInput = Replace(sInput, “&Ugrave;”, “Ù”)
sInput = Replace(sInput, “&ucirc;”, “û”)
sInput = Replace(sInput, “&Ucirc;”, “Û”)
sInput = Replace(sInput, “&uuml;”, “ü”)
sInput = Replace(sInput, “&Uuml;”, “Ü”)
sInput = Replace(sInput, “&yuml;”, “ÿ”)
sInput = Replace(sInput, “”, “´”)
sInput = Replace(sInput, “”, “`”)

‘replace all the remaining HTML Tags
With RegEx
.Global = True
.IgnoreCase = True
.MultiLine = True
.Pattern = “<[^>]+>” ‘Regular Expression for HTML Tags.

End With
sOut = RegEx.Replace(sInput, “”)
StripHTML = sOut
Set RegEx = Nothing
End Function
    InformationsquelleAutor J Francis Consulting
  3. 0

    Da das makro oben hat bei mir nicht funktioniert habe ich Fix es selbst. Es ist mein erstes Skript, wenn Sie die Jungs können es verbessern, damit es schneller geht, fügen Sie mehr, dann sind Sie mehr als willkommen!

    Ok Jungs, ich habe keine vorherigen Erfahrungen in der Programmierung (außer für einige sehr grundlegende Java-6 Jahren), aber mit einiger Hilfe, viel zu raten (Stunden eigentlich) schaffte ich dieses Skript, es funktioniert wie ein Charme zu entfernen, die meisten und 8#text aber nicht ersetzen <BR> mit linebreak (Sie können dies tun, indem Sie STRG + H "suchen: <br>" "ersetzen: (jetzt ALT gedrückt halten nach unten, und verwenden Sie Typ 0010 mit Ihrem ZIFFERNBLOCK. Einen kleinen Punkt sollte blinken im ersetzen-Fenster, dann drücken Sie auf "alle ersetzen").

    Fügen Sie den folgenden code in ein Benutzer-Modul (alt +f11, Rechte Maustaste Tabelle1->einfügen->Modul->einfügen von code)

    Machen und eine Schaltfläche, indem Sie auf Datei->Optionen->Anpassen der Multifunktionsleiste-> überprüfen Sie das Kontrollkästchen Entwickler. Dann gehen Sie zu Registerkarte "Entwickler" ->Einfügen->Button-> setzen Sie dann die-Taste und Rechte Maustaste->makro zuweisen-> Wählen Sie RemoveTags. 

    Sub RemoveTags()
    Dim r As Range

    Selection.NumberFormat = "@"  'set cells to text numberformat

    With CreateObject("vbscript.regexp")
      .Pattern = "\<.*?\>"
      .Global = True

      For Each r In Selection
        r.Value = Replace(.Replace(r.Value, ""), "&#8217;", " ")
        r.Value2 = Replace(.Replace(r.Value2, ""), "&#8211;", " ")
      Next r

      For Each r In Selection
        r.Value = Replace(.Replace(r.Value, ""), "&#8216;", " ")
        r.Value2 = Replace(.Replace(r.Value2, ""), "&#8232;", " ")
      Next r

      For Each r In Selection
        r.Value = Replace(.Replace(r.Value, ""), "&#8233;", " ")
        r.Value2 = Replace(.Replace(r.Value2, ""), "&#146;s", " ")
      Next r
    End With
End Sub


Private Sub CommandButton1_Click()

End Sub
    InformationsquelleAutor Snaplit
Schreibe einen Kommentar