Delete leading/training spaces in table cells?

E

Ed

I am using Convert Text to Table on a large amount of text. The table will
have over 14,000 rows. When the text is pulled into the doc, the code grabs
a certain number of character spaces, even if they're empty. It's not safe
to just Find/Replace three consecutive spaces with one space until Execute =
False. These unwanted spaces will always show up, though, as leading or
training spaces in the table cells after conversion.

Is there an easy way to delete all leading or trailing spaces in every table
cell?

Ed
 
M

mbaird

You can use the following wildcard search on the text before you convert the
data to a table.

Mark Baird

With Selection.Find
.Text = " {1,}(^13)"
.ClearFormatting
.Replacement.Text = "\1"
.Replacement.ClearFormatting
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True

.Execute Replace:=wdReplaceAll

.Text = "(^13) {1,}"
.Replacement.Text = "\1"

.Execute Replace:=wdReplaceAll
End With
 
E

Ed

Thanks for the reply, Max, but I don't think it's going to help. I am very
leery of doing a bulk Find/Replace on the text before it's converted because
it would be possible to replace multiple spaces that are an integral part of
the text of a cell. That's why I thought it might be easier to go to each
cell and delete leading and training spaces; that way, I won't touch
anything within the cell text.

If your code does not have this danger, then I apologize for my inexperience
and misunderstanding of what you wrote. I can't see where it would avoid
this possibility.

Ed
 
J

Jean-Guy Marcil

Bonjour,

Dans son message, < Ed > écrivait :
In this message said:
I am using Convert Text to Table on a large amount of text. The table will
have over 14,000 rows. When the text is pulled into the doc, the code grabs
a certain number of character spaces, even if they're empty. It's not safe
to just Find/Replace three consecutive spaces with one space until Execute =
False. These unwanted spaces will always show up, though, as leading or
training spaces in the table cells after conversion.

Is there an easy way to delete all leading or trailing spaces in every table
cell?
Try this (Put the cursor anywhere in the table):

'_______________________________________
Application.ScreenUpdating = False

Dim MyCell As Cell
Dim MyTable As Table
Dim CellText As String

Set MyTable = Selection.Tables(1)

For Each MyCell In MyTable.Range.Cells
With MyCell.Range
CellText = Left(.Text, Len(.Text) - 2)
CellText = Trim(CellText)
.Text = CellText
End With
Next MyCell

Application.ScreenRefresh
Application.ScreenUpdating = False
'_______________________________________

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org
 
G

garfield-n-odie

Try selecting all of the columns in the table, then click on the Center
alignment button on the Formatting toolbar, then click on the Left
alignment button on the Formatting toolbar. Word should automatically
strip all leading and trailing spaces from the table cells that contain
text, and delete all spaces from otherwise empty table cells.
 
G

garfield-n-odie

Try selecting all of the columns in the table, then click on the Center
alignment button on the Formatting toolbar, then click on the Left
alignment button on the Formatting toolbar. Word should automatically
strip all leading and trailing spaces from the table cells that contain
text, and delete all spaces from otherwise empty table cells.
 
M

mbaird

This will only remove the spaces before a carriage return and the spaces
after the carriage returns. It will not remove the spaces between words.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top