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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

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 ========================================================================
 

Watch MrExcel Video

Forum statistics

Threads
1,133,157
Messages
5,657,159
Members
418,363
Latest member
Debating_Earth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top