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):

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.

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:

Copy the text of the macro, starting with the first “Sub” and ending with the last “Sub.”

Click the “View” tab on Microsoft Word’s ribbon.

Click the “Macros” button.

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.”

Click the “Create” button.

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).

Paste the macro text at the current insertion point.

Click “File,” then “Close and Return to Microsoft Word.”

To actually use the macro:

Place your cursor at the beginning of the document.

Click the “View” tab on Microsoft Word’s ribbon.

Click the “Macros” button.

Click the name of your macro to select it.

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):

Locate the QAT (it’s probably on the top left of your screen either above or below Word’s Ribbon interface).

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.

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. 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?

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.

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!

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

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!

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.

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