How to compress html in Excel?

Helios9

Board Regular
Joined
Oct 15, 2006
Messages
127
I have a column of hundreds of cells each containing a page of html. I need to compress the html, removing all line breaks and white space. Find/Replace does not work - it only gives the error 'Formula is too long'. I've tried cutting and pasting into and out of Dreamweaver and Word but with only limited results and with many repeats of find/replace. I've also tried html tools such as Emsa Whitespace Remover and Absolute html Compressor. These work but a single html file must first be saved then processed then reopened and cut and pasted back into Excel. This would be an extremely time consuming process even for a few dozen cells.

Is there a quick way to compress the html? Can a macro do it? A two-step into another application and back into Excel would be fine.

Thanks in advance for any help.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Perhaps this is something like what you want ... ?
Code:
'========================================================================================
'- FIND AND REPLACE TEXT (white space) IN CELLS
'- Uses Regular Expressions
'- This version removes white space from cells in column A
'- ** For "safety" and testing this puts results into column  B      ***
'- ** Change the code below to replace column A                      ***
'========================================================================================
'- Brian Baulsom August 2010
'========================================================================================
Sub CELL_TEXT_REPLACE()
    Dim MyRegExp As Object
    Dim MyString As String
    Dim NewString As String
    Dim MyRow As Long
    Dim LastRow As Long
'========================================================================================
'- REGULAR EXPRESSION PATTERN TO FIND TEXT TO REPLACE
    Const MyPattern As String = "\t|\n"
    'Const MyPattern As String = "\s"   ' this version removes intervening spaces too
'========================================================================================
    Set MyRegExp = CreateObject("VbScript.RegExp")
    Application.Calculation = xlCalculationManual
    '------------------------------------------------------------------------------------
    '- LOOP WORKSHEET ROWS
    LastRow = Range("A65536").End(xlUp).Row
    For MyRow = 2 To LastRow
        Application.StatusBar = MyRow - 1 & " / " & LastRow - 1
        MyString = Cells(MyRow, "A").Value
        '-------------------------------------------------------------------------------
        '- EXECUTE REGULAR EXPRESSION REPLACE
        With MyRegExp
            .Global = True
            .pattern = MyPattern
            .ignorecase = True
            NewString = .Replace(MyString, "")
        End With
        '-------------------------------------------------------------------------------
        '- RESULTS TO CELL
        '-------------------------------------------------------------------------------
        ' Cells(MyRow, "A").Value = NewString    ' replaces text in the cell
        Cells(MyRow, "B").Value = NewString      ' result to column B
        MyString = ""
        NewString = ""
    Next
    '-----------------------------------------------------------------------------------
    MsgBox ("Done")
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
End Sub
'========== EOP ========================================================================
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,996
Messages
5,508,639
Members
408,689
Latest member
SamSan78

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top