ansaurus

Question

Answer 1

A:

in excel select all
open replace (ctrl+f)
replace <>
with nothing

or use perl regex
$line = "This is some text with HTML and words";
$line =~ s/<(.?)>//gi;

Prateek 2010-06-28 18:36:25

the replace doesnt work, its literal. and im not using perl.

kacalapy 2010-06-28 18:40:53

I don't know why my above post does not display the asterisk between <>. replace accepts wild cards. not sure what you mean by literal. excel atleast works on the example you posted.

Prateek 2010-06-28 18:55:49

Answer 2

+1 A:

Set a reference to MS Forms 2.0 to use the DataObject object.

Public Function StripHTML(sInput As String) As String

    Dim rTemp As Range
    Dim oData As DataObject

    Set oData = New DataObject
    oData.SetText "<html><style>br{mso-data-placement:same-cell;}</style>" & sInput & "</html>"
    oData.PutInClipboard

    Set rTemp = Workbooks.Add.Worksheets(1).Range("a1")
    rTemp.Parent.PasteSpecial "Unicode Text"
    StripHTML = rTemp.Text

    rTemp.Parent.Parent.Close False
    Set rTemp = Nothing
    Set oData = Nothing

End Function

See http://www.dailydoseofexcel.com/archives/2005/02/23/html-in-cells-ii/ for more info.

Dick Kusleika 2010-06-28 18:51:05

+1, but I don't think that's exactly what the OP was looking for. I think @kacalapy wants to strip out the HTML, not convert it to valid formatting.

technomalogical 2010-06-28 20:06:36

Converting it to valid formatting is just a side effect. Because the function returns the Text property of the Range object, the formatting won't be relevant. It will however remove the html tags as it converts it.

Dick Kusleika 2010-06-28 20:33:28

Answer 3

A:

You can try VBScripts Regular Expression Support.

http://www.regular-expressions.info/vbscriptexample.html

phimuemue 2010-06-28 19:00:23

Answer 4

A:

guys this is a simple set of string functions. as i am not up to speed on excel string functions this is a pain for me to figure out...

in a programing language this would go somethign like mid(start, length) to get the value from the full html string- the trick is to get the length as the start possition minus the lenth of th ending html lenght)

mid( pos(">")+2, len(string)-pos(">") )

kacalapy 2010-06-29 13:53:20

ansaurus

tags:

views:

answers:

excel formula to strip html

related questions