An American Editor

May 5, 2014

Lyonizing Word: Removing Spaces at the End of Table Cells

Removing Spaces at the End of Table Cells

by Jack Lyon

Authors do funny things. Sometimes these things are inadvertent; sometimes they’re the result of trying to “prettify” documents for publication. In either case, editors have to clean up what the authors have done.

One such problem is spaces at the ends of table cells. A table cell should end with the text it contains. If there are spaces after that text, they can cause alignment (and other) problems if they’re allowed to persist into typesetting.

It should be a simple matter to clean up the extraneous spaces: Search for a space followed by an end-of-cell marker and replace with just an end-of-cell marker. But what magic code can we use to find or replace an end-of-cell marker? As it turns out, there isn’t one. But we can still get rid of those spaces with a macro. Here it is, with comments about what’s going on (text following a single quotation mark is a “comment”, which is also in green for clarity):

The Macro

Sub CleanCellEndSpaces()

‘Define variables (that is, containers for information)
Dim aTable As Table
Dim aCell As Cell
Dim aRow As Row
Dim aColumn As Column
Dim aRange As Range ‘That is, a specific area of the document
Dim aLen As Integer ‘That is, a number
Dim LastChar As String ‘That is, a string of characters (text)

Dim Tracking As Boolean ‘True or False
Tracking = ActiveDocument.TrackRevisions ‘Get setting of revision tracking
ActiveDocument.TrackRevisions = False ‘Turn off revision tracking

On Error Resume Next ‘In case of tables with “vertically merged” cells
‘Cycle through tables, rows, and cells

For Each aTable In ActiveDocument.Tables
For Each aRow In aTable.Rows
For Each aCell In aRow.Cells

CheckAgain:

Set aRange = aCell.Range ‘Set aRange variable to the contents of the current cell
aRange.End = aRange.End – 1 ‘Don’t include the end-of-cell marker
aLen = Len(aRange.Text) ‘Get the length of the cell’s text
aString = aRange.Text ‘Assign the text to a variable
LastChar = Right(aString, 1) ‘Get the last character of the text
If LastChar = ” ” Then ‘If the last character is a space

aRange.Text = Left(aRange.Text, aLen – 1) ‘Set the text to be itself minus the trailing space
GoTo CheckAgain ‘Go back and check for another space (there may be several)

End If
Next aCell
Next aRow
Next aTable

ActiveDocument.TrackRevisions = Tracking ‘Set revision tracking back to its original state

End Sub

The Explanation

Here’s how the macro works.

We start by “dimensioning” (defining) our variables, like this:

Dim aTable As Table

“aTable” is an arbitrary name; I just made it up. But that whole line tells Word that aTable will represent a table in our document. The other “Dim” statements follow suit, with “aCell” representing a table cell, “aRow” representing a table row, and so on.

These three lines deserve special attention:

Dim Tracking As Boolean
Tracking = ActiveDocument.TrackRevisions
ActiveDocument.TrackRevisions = False

Dimensioning the “Tracking” variable as Boolean tells Word that the variable will be either true or false; those are the only two values it can hold.

Next, we set “Tracking” to the value of ActiveDocument.TrackRevisions. If revision tracking is on, “Tracking” will be set to “True.” If revision tracking is off, “Tracking” will be set to “False.” We do that to remember the current setting for revision tracking, because the next line, “ActiveDocument.TrackRevisions = False” actually turns revision tracking off (we’ll reset it later). This is necessary because (1) tracking the deletion of those extraneous spaces isn’t needed, and (2) using revision tracking may send this macro into an endless loop as it keeps “finding” the character that it just deleted (but is still there as a tracked revision).

The next line —

On Error Resume Next

— needs to be there in case a table includes “merged” cells, which will cause an error when the macro runs. If that happens, the macro will skip to the next line, which means that tables with “merged” cells will be ignored. There may be a better way to deal with such tables, but I don’t know what it is.

After that line, things get really interesting:

For Each aTable In ActiveDocument.Tables

This tells Word to work on “Each” table in ActiveDocument.Tables. “What’s that?” you ask. Well, obviously “ActiveDocument” is the active document — the document that’s open in Word with our cursor moving around in it. (Other documents may be open but not active.) In the active document, there’s a collection of objects called “Tables” — which are, of course, the tables in the document.

And now, a brief digression: As far as macros are concerned, a Microsoft Word document is “simply” a collection of various objects, such as tables, headers, footers, footnotes, endnotes, paragraphs, words, and much, much more. All of these objects have certain “properties.” For example, a paragraph may have the property of FirstLineIndent set to “True” — in other words, its first line is indented. Objects can also contain other objects. For example, a table always contains at least one row and one column. So, in our macro, we have this:

For Each aRow In aTable.Rows

That tells Word to work on each row in the current table. Similarly, this —

For Each aCell In aRow.Cells

— tells Word to work on each cell in the current row.

Next, we’re going to set the range of text we want to use (that is, aRange) to be the current cell:

Set aRange = aCell.Range

Then we’ll reduce the end of that range by one character, so we don’t include the end-of-cell marker:

aRange.End = aRange.End – 1

And, using the Len command, we’ll find out the length (number of characters) included in the range’s text:

aLen = Len(aRange.Text)

Now let’s get that text by putting it into the variable called “aString”:

aString = aRange.Text

And we’ll use the Right command to find out the last character of the text string (that is, the first character on the right of the string):

LastChar = Right(aString, 1)

That looks a little complicated, but it’s actually fairly simple. Let’s say our text string is “Hi, Mom!” The “1” tells the Right command at which character to start counting (from the right of the string). In other words, LastChar is assigned the last character of the string, which in this case is an exclamation mark (“Hi, Mom!”).

But what if the last character is a space? That’s what we really we want to know. The next line will tell us if that’s the case:

If LastChar = ” ” Then

If the last character is a space, we need to get rid of it, which we can do like this:

aRange.Text = Left(aRange.Text, aLen – 1)

That line changes the text of our range to itself minus its last character (if the previous line identified its last character as a space). But what if there’s more than one space? We want to get rid of those spaces too! And that’s where the next line comes in:

GoTo CheckAgain

That sends the macro back to the “label” we’ve created at this line:

CheckAgain:

And the operation is repeated on the cell until no more spaces remain at the end of the cell.

All of the “Next” commands that follow repeat the whole operation for every cell in every row in every table of the active document. Powerful stuff!

Finally, the macro restores revision tracking to its original setting as stored in the “Tracking” variable:

ActiveDocument.TrackRevisions = Tracking

As they taught us in kindergarten, it’s good to clean up after yourself.

This article is a brief introduction to manipulating Word “objects” with macros. Future articles may explore more of those objects, along with their “properties” and “methods.” If that’s more than you want to know, you may still find the macros themselves to be useful.

How to Add Macro to Word & to the QAT

Here’s how to put this macro (or any other) into Microsoft Word so it will be available when you need it:

  1. Copy the text of the macro, starting with the first “Sub” and ending with the last “Sub.”
  2. Click the “View” tab on Microsoft Word’s ribbon.
  3. Click the “Macros” button.
  4. Type a name for the macro in the “Macro name” box — probably the name used after the first “Sub.” For this macro, that’s “CleanCellEndSpaces.”
  5. Click the “Create” button.
  6. Delete the “Sub [macro name]” and “End Sub” lines that Word created in the macro window. The macro window should now be completely empty (unless you already have other macros in there).
  7. Paste the macro text at the current insertion point.
  8. Click “File,” then “Close and Return to Microsoft Word.”

To actually use the macro:

  1. Place your cursor at the beginning of the document.
  2. Click the “View” tab on Microsoft Word’s ribbon.
  3. Click the “Macros” button.
  4. Click the name of your macro to select it.
  5. Click the “Run” button. (If you wanted to delete the macro, you could press the “Delete” button instead.)

Here’s how to put the macro on Word’s QAT (Quick Access Toolbar):

  1. Locate the QAT (it’s probably on the top left of your screen either above or below Word’s Ribbon interface).
  2. Right-click the QAT.
  3. Click “Customize Quick Access Toolbar.”
  4. Under “Choose commands from:” click the dropdown list and select “Macros.”
  5. Find and select your macro in the list on the left.
  6. Click the “Add” button to add it to the QAT.
  7. Click the “OK” button to finish.

Jack Lyon (editor@editorium.com) owns and operates the Editorium, which provides macros and information to help editors and publishers do mundane tasks quickly and efficiently. He is the author of Microsoft Word for Publishing Professionals and of Macro Cookbook for Microsoft Word. Both books will help you learn more about macros and how to use them.

21 Comments »

  1. Thanks! This would have come in really handy last month when I was working on a report with tons of tables, with lots of extraneous spaces, especially at the end of cells. I know this will come in handy in the future, because I’m always getting tables with wacky formatted to edit.

    Like

    Comment by Teresa Barensfeld — May 5, 2014 @ 5:15 pm | Reply

  2. This macro is just brilliant! Thanks so much for solving an issue that bugs me.

    Now a question… My client uses borderless tables in the headers and footers and sometimes excess spaces end up in there too. How do I modify the range to include tables in the headers and footers too?

    Many thanks again.

    –Rhonda

    Like

    Comment by Rhonda — May 7, 2014 @ 1:50 am | Reply

    • Hi, Rhonda.

      Actually, the macro should take care of all tables in the document, no matter where they are. If that’s not occurring in one of your documents, I’d appreciate it if you could send me the document for a quick diagnosis.

      Thanks!

      Best wishes,
      Jack Lyon
      The Editorium

      Like

      Comment by Jack Lyon — May 15, 2014 @ 3:33 pm | Reply

  3. Thanks. I got the macro working after having to edit single and double quotes, and minus signs. However it is changing cell text styles. The document I am working with has multiple direct (aka hard) formats applied to words within each paragraph (for which Word [2003], as always, created its own styles). After running the macro, every cell that ended with a space has all its text changed to one style, although it isn’t necessarily the style of the first or last character in the cell before running the macro. Any ideas?

    Like

    Comment by Huw — July 8, 2014 @ 3:03 am | Reply

    • Sorry, but I don’t know why the style would change. I don’t think there’s anything in the macro that would do that. If you’ll send me the document in which this is happening, I’ll be happy to take a look.

      Like

      Comment by Jack Lyon — August 9, 2014 @ 12:22 pm | Reply

  4. Great idea but cannot get it to work. Have 1 or 2 spaces after text and blank line below text in Word table. Thank you.

    Like

    Comment by Paul — August 8, 2014 @ 9:02 pm | Reply

    • If the macro isn’t working for you, it may be because of formatting changes applied by WordPress. For example, slanted single quotation marks in the macro should be straight quotation marks. For example, this line–

      Dim aLen As Integer ‘That is, a number

      –should look like this:

      Dim aLen As Integer ‘That is, a number

      That’s difficult to see online, but it makes all the difference in the world.

      You may also encounter problems with “soft” returns after each line of the macro; they should be hard returns instead, and you may have to change them by hand.

      Sorry about the problems, but I don’t yet know a way of preventing them in WordPress. Thanks!

      Like

      Comment by Jack Lyon — August 9, 2014 @ 12:29 pm | Reply

      • Use WordPress’s line/paragraph style Preformatted to avoid the issues with apostrophes and hyphens.
        In the comments, use the HTML codes

        your code here
        
        

        Like

        Comment by Daniel Busoli — November 20, 2016 @ 8:48 pm | Reply

  5. Thank you for writing this macro and explaining every line, you rock!!

    I get errors in the following lines (Word 2013)

    aRange.End = aRange.End – 1
    “compile error: expected end of statement” the “-” sign is highlighted.

    aRange.Text = Left(aRange.Text, aLen – 1)
    “Compile error: Espected:list seperator or)” the “-” sign is highlighted.

    Like

    Comment by Ewald Fock — October 25, 2014 @ 6:23 pm | Reply

  6. Hi, Ewald. Please see my answer to question number 5, above.

    Like

    Comment by Jack Lyon — October 28, 2014 @ 7:52 pm | Reply

    • Thank you Jack, not sure why. but just retyping the”-” sign cleared the error.
      Maybe the – sign copied as an incorrect ASCI code?

      Like

      Comment by Ewald Fock — October 29, 2014 @ 4:52 pm | Reply

  7. What if I want to get rid of extra paragraph returns at the end of table cell?

    Like

    Comment by juliacochrane — November 5, 2015 @ 10:57 am | Reply

  8. This macro has accomplished something I have been attempting to solve for years. I tried it on a Word 2010 table I had just imported from Excel and it cleaned out all of the spaces before the cell end mark faster than I could see it. I have been able to do this outside tables with the replace command using ^p for the find and ^p for Replace, but the table space has forever stumped me. Thank you for your work.
    Rich

    Like

    Comment by Rich Marshall — May 4, 2016 @ 1:55 pm | Reply

    • Hi, Rich. Thanks for your comment. I’m glad you found this information useful.

      Like

      Comment by Jack Lyon — May 4, 2016 @ 3:22 pm | Reply

  9. Fantastic! I was just about to write my own macro and decided to search for a solution first.🙂 When compiling, since I use Option Explicit at the top of each module, it got upset as there was no dimension statement for aString. So I added:
    dim aString as String ‘Text of the cell
    to the list of variable definitions at the top. After that, it compiled and worked perfectly. Thank you!

    Like

    Comment by yogajourneyman — May 8, 2016 @ 5:25 pm | Reply

  10. I have just discovered a neat and simple trick to remove leading and trailing spaces from table cells. Just change the text alignment from its current state to centered and back again (e.g. change from left aligned to centered and back to left aligned), and voila to offending spaces are gone, and the styles remain. If the current (and desired) alignment is centered, change to left or right alignment and then back to centered.

    I found this tip in a number of places:
    * http://answers.microsoft.com/en-us/msoffice/forum/msoffice_word-mso_other/remove-white-space-at-the-end-of-a-word-or-set-of/fd587774-e001-429a-8a05-1aba8509c32f

    * http://www.wordbanter.com/showthread.php?t=62592

    Like

    Comment by YossiD — October 25, 2016 @ 2:45 am | Reply

  11. Hi Jack,

    Unfortunately, your Macro is very inefficient as it reads and writes to the document many times per cell (where there are multiple spaces), and sets and compares the lastChar variable even if the cell was empty.
    I’ve updated the macro to make better use of the aString variable, and also remove carriage returns [Chr(13)].

    Sub CleanCellEndSpaces()
        
        'Define variables (that is, containers for information)
        Dim aTable As Table
        Dim aCell As Cell
        Dim aRow As Row
        Dim aColumn As Column
        Dim aRange As Range 'That is, a specific area of the document
        Dim aLen As Integer 'That is, a number
        Dim LastChar As String 'That is, a string of characters (text)
        
        Dim Tracking As Boolean 'True or False
        Tracking = ActiveDocument.TrackRevisions 'Get setting of revision tracking
        ActiveDocument.TrackRevisions = False 'Turn off revision tracking
        
        On Error Resume Next 'In case of tables with “vertically merged” cells
        'Cycle through tables, rows, and cells
        
        For Each aTable In ActiveDocument.Tables
            For Each aRow In aTable.Rows
                For Each aCell In aRow.Cells
                
                    Set aRange = aCell.Range 'Set aRange variable to the contents of the current cell
                    aRange.End = aRange.End - 1 'Dont include the end-of-cell marker
                    aString = aRange.Text 'Copy the text to a variable
                    aLen = Len(aString) 'Get the length of the cell's text
                    
                    If aLen > 0 Then
                        LastChar = Right(aString, 1)
                        
                        Do While (LastChar = " ") Or (LastChar = Chr(13))
                            aLen = aLen - 1  'Set the string length to be one character (space or CR) shorter
                            aString = Left(aString, aLen) 'Set the string to be one character (space or CR) shorter
                            LastChar = Right(aString, 1)
                        Loop
                        
                        aRange.Text = aString 'Copy the new cell text back to the cell
                    End If
                
                Next aCell
            Next aRow
        Next aTable
        
        ActiveDocument.TrackRevisions = Tracking 'Set revision tracking back to its original state
        
    End Sub
    

    Like

    Comment by Daniel Busoli — November 20, 2016 @ 8:42 pm | Reply

  12. Reblogged this on Daniel's Public Blog.

    Like

    Comment by Daniel Busoli — November 20, 2016 @ 8:50 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: