Cleaning Up Your Item Descriptions

Cleaning Up Your Item Descriptions

I have noticed that some XLEcom users are not entering in the Item Descriptions correctly and therefore the integrated Site Search feature does not work properly. In order to fix the problem, I created the following Data_Clean_Up VBA macro that will clean up your Item Descriptions by removing all non-printable characters, line breaks, and extra spaces. Below is a before and after of a sample Item Description.

Sample Item Description - Bad Format
Even though the HTML code is correct, the following item description contains too many line breaks. This is usually a result of users copying and pasting their item descriptions directly into the Database sheet from another application. In this case, the item description needs to be cleaned up.

    <b>Description:</b><br>
    The Acer TravelMate C300 is the convertible Tablet that you'll want to use as your primary computer. With a big 14.1" XGA display - a first for a Tablet PC - full-size keyboard and internal, modular optical drive, it's similar to the standard notebook you're probably using now.<br><br>
    The TravelMate C300, though, offers so much more. The screen becomes a writing surface when you rotate it and fold it back over the keyboard, perfect for taking notes, annotating documents, completing online forms and so much more.<br><br>
    The integrated Smart Vision light sensor automatically adjusts screen brightness to one of four default settings based on available ambient light, or you can easily choose your own setting. This is done via hotkeys in notebook mode or by using the Acer Soft Button in Tablet mode.<br><br>
    The on-screen Soft Button feature gives you easy, centralized access to applications, wireless and modem connections, volume control and screen rotation.<br><br>
    If convertible Tablet PCs have intrigued you but you've been put off by their size, you owe it to yourself to consider the Acer TravelMate C300.<br><br>
    <b>Specifications:</b><br>
    <ul>
    <li>Product Description - Acer TravelMate C300 - Pentium M 1.5 GHz - 14.1" TFT
    <li>Recommended Use - Small business, corporate business, education
    <li>Dimensions (WxDxH) - 12.8 in x 10.7 in x 1.4 in
    <li>Weight - 6.2 lbs
    <li>Localization - English / United States
    <li>Mobile Technology - Intel Centrino
    <li>System Type - Tablet PC - convertible
    <li>Built-in Devices - Stereo speakers
    <li>Processor - Intel Pentium M 1.5 GHz
    <li>Cache Memory - 1 MB - L2 cache
    <li>RAM - 512 MB (installed) / 2 GB (max) - DDR SDRAM - PC2700 - 333 MHz
    <li>Hard Drive - 1 x 40 GB
    <li>Optical Storage - CD-RW / DVD-ROM combo - plug-in module
    <li>Display - 14.1" TFT active matrix - 24-bit (16.7M colors)
    <li>Graphics Controller - Intel 855GME
    <li>Audio Output - Sound card
    <li>Telecom - Fax / modem - 56 Kbps
    <li>Networking - Network adapter - Ethernet, Fast Ethernet, Gigabit Ethernet, IEEE 802.11b
    <li>Input Device - Keyboard, digitizer, touchpad, 4-way scroll button
    <li>Battery - Lithium ion
    <li>Average Run Time - 5 hour(s)
    <li>Operating System - Microsoft Windows XP Tablet PC Edition
    <li>Manufacturer Warranty - 1 year warranty
    </li>
    </ul>

Sample Item Description - Cleaned Up in Proper Format
The single line item description below is the same item description as above in the proper format. It is how it should appear within the Database spreadsheet cell.

    <b>Description:</b><br>The Acer TravelMate C300 is the convertible Tablet that you'll want to use as your primary computer. With a big 14.1" XGA display - a first for a Tablet PC - full-size keyboard and internal, modular optical drive, it's similar to the standard notebook you're probably using now.<br><br>The TravelMate C300, though, offers so much more. The screen becomes a writing surface when you rotate it and fold it back over the keyboard, perfect for taking notes, annotating documents, completing online forms and so much more.<br><br>The integrated Smart Vision light sensor automatically adjusts screen brightness to one of four default settings based on available ambient light, or you can easily choose your own setting. This is done via hotkeys in notebook mode or by using the Acer Soft Button in Tablet mode.<br><br>The on-screen Soft Button feature gives you easy, centralized access to applications, wireless and modem connections, volume control and screen rotation.<br><br>If convertible Tablet PCs have intrigued you but you've been put off by their size, you owe it to yourself to consider the Acer TravelMate C300.<br><br><b>Specifications:</b><br><ul><li>Product Description - Acer TravelMate C300 - Pentium M 1.5 GHz - 14.1" TFT<li>Recommended Use - Small business, corporate business, education<li>Dimensions (WxDxH) - 12.8 in x 10.7 in x 1.4 in<li>Weight - 6.2 lbs<li>Localization - English / United States<li>Mobile Technology - Intel Centrino<li>System Type - Tablet PC - convertible<li>Built-in Devices - Stereo speakers<li>Processor - Intel Pentium M 1.5 GHz<li>Cache Memory - 1 MB - L2 cache<li>RAM - 512 MB (installed) / 2 GB (max) - DDR SDRAM - PC2700 - 333 MHz<li>Hard Drive - 1 x 40 GB<li>Optical Storage - CD-RW / DVD-ROM combo - plug-in module<li>Display - 14.1" TFT active matrix - 24-bit (16.7M colors)<li>Graphics Controller - Intel 855GME<li>Audio Output - Sound card<li>Telecom - Fax / modem - 56 Kbps<li>Networking - Network adapter - Ethernet, Fast Ethernet, Gigabit Ethernet, IEEE 802.11b<li>Input Device - Keyboard, digitizer, touchpad, 4-way scroll button<li>Battery - Lithium ion<li>Average Run Time - 5 hour(s)<li>Operating System - Microsoft Windows XP Tablet PC Edition<li>Manufacturer Warranty - 1 year warranty</li></ul>

You can download the Data_Clean_Up VBA Macro here (Data_Clean_Up.xls): Data_Clean_Up.zip

    To utilize the Data_Clean_Up macro, do the following:

      (1) Make sure that both your XLEcom program and Data_Clean_Up.xls files are open.
      (2) Switch to the XLEcom Database sheet.
      (3) Run the Data_Clean_Up macro. (You can access the macro via Excel's Tools menu)
      (4) You can also repeat the process on your Items Names if necessary.

For those who want to manually create the macro, use the following instructions:

    To create the Data_Clean_Up macro function, do the following:

      (1) Create a new Excel file.
      (2) Open the VBA editor and add a new module.
      (3) Copy and paste the following Data_Clean_Up VBA macro code into the module.
      (4) Save the Excel file as Data_Clean_Up.xls.

    To utilize the Data_Clean_Up macro, do the following:

      (1) Make sure that both your XLEcom program and Data_Clean_Up.xls files are open.
      (2) Switch to the XLEcom Database sheet.
      (3) Run the Data_Clean_Up macro. (You can access the macro via Excel's Tools menu)
      (4) You can also repeat the process on your Items Names if necessary.

The Data_Clean_Up VBA Macro

    Sub Data_Clean_Up()
    ' By Sam Raheb at http://xlecom.com
    ' Removes all non-printing ASCII characters, Line Breaks, and Extra Spaces.
      
       Dim Selected_Cells As Range
       On Error Resume Next
       Application.DisplayAlerts = False
      
       Set Selected_Cells = Application.InputBox(Prompt:="Removes all non-printing ASCII characters, Line Breaks, and Extra Spaces from the selected range of cells." & vbNewLine & vbNewLine & "Select a Range of Cells:", Title:="Data Clean Up", Type:=8)
       On Error GoTo 0
       Application.DisplayAlerts = True
          
         If Selected_Cells Is Nothing Then
           Exit Sub
         Else
           Application.ScreenUpdating = False
           Application.Worksheets(ActiveSheet.Name).Activate
           For Each XCell In Selected_Cells
           If Not IsEmpty(XCell.Value) Then
             XCell.Value = Trim(Application.WorksheetFunction.Clean(XCell.Value))
           End If
           Next
           Application.ScreenUpdating = True
           MsgBox "Data Clean Up finished."
         End If

    End Sub

Note: This Data_Clean_Up function is included in XLEcom Professional and Webmaster version 6.2.

Sam Raheb
XLEcom Program Developer