+ Reply to Thread
Results 1 to 2 of 2

Convert HTML in Excel Column to Plain Text

Hybrid View

  1. #1
    Registered User
    Join Date
    08-07-2014
    Location
    Opijnen
    MS-Off Ver
    2010
    Posts
    2

    Convert HTML in Excel Column to Plain Text

    Hi,
    I used a macro to convert html to plain text and it does the job great but I want to leave the html tag <CTRL> in the text.
    Can I exclude this?


    The script I used is:

    Sub StripTags()
    ' adapted from Rick Rothestein at
    ' http://groups.google.com/group/micro...b8e6462c11746c
    Dim cell As Range
    Dim s As String
    Dim asWd() As String
    Dim iWd As Long

    For Each cell In Intersect(Selection.Cells, ActiveSheet.UsedRange)
    s = Replace(cell.Value, Chr(160), " ")
    s = Replace(s, ">", "<")
    s = Replace(s, vbCr, vbLf)

    asWd = Split(s, "<")
    s = vbNullString
    For iWd = 0 To UBound(asWd) Step 2
    s = s & " " & asWd(iWd)
    Next iWd

    Do While InStr(s, vbLf & " ")
    s = Replace(s, vbLf & " ", vbLf)
    Loop

    Do While InStr(s, vbLf & vbLf)
    s = Replace(s, vbLf & vbLf, vbLf)
    Loop

    cell.Value = WorksheetFunction.Trim(s)
    Next cell
    End Sub

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Convert HTML in Excel Column to Plain Text

    if instr(1,s,"CTRL")>0 then
    
     asWd = Split(s, "<")
     s = vbNullString
     For iWd = 0 To UBound(asWd) Step 2
     s = s & " " & asWd(iWd)
     Next iWd
     
    Do While InStr(s, vbLf & " ")
     s = Replace(s, vbLf & " ", vbLf)
     Loop
     
    Do While InStr(s, vbLf & vbLf)
     s = Replace(s, vbLf & vbLf, vbLf)
     Loop
    
    end if
    or even if s<>"<ctrl>" at the start of the loops.
    Last edited by nathansav; 08-07-2014 at 07:14 AM. Reason: code tags
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Convert HTML in Excel Column to Plain Text
    By jimlines in forum Excel General
    Replies: 9
    Last Post: 08-07-2014, 03:24 AM
  2. convert formated excel column to html source text with html tags
    By julia81 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-19-2011, 04:22 PM
  3. how to convert an html link embedded in an icon to plain text??
    By Angelina Vidal in forum Excel General
    Replies: 1
    Last Post: 03-17-2011, 11:18 PM
  4. Convert Formula To Plain Text
    By tomyknoker in forum Excel General
    Replies: 3
    Last Post: 11-15-2007, 08:18 AM
  5. Pasting HTML formatted text as plain text in Excel
    By JeffCutter in forum Excel General
    Replies: 1
    Last Post: 09-21-2007, 07:43 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1