Delete leading/training spaces in table cells?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ed - 06 Oct 2004 18:28 GMT

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

Reply to this Message

mbaird - 06 Oct 2004 19:07 GMT

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

> 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
[quoted text clipped - 7 lines]
>
> Ed

Reply to this Message

Ed - 06 Oct 2004 19:29 GMT

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

> You can use the following wildcard search on the text before you convert the
> data to a table.
[quoted text clipped - 34 lines]
> >
> > Ed

Reply to this Message

mbaird - 12 Oct 2004 22:15 GMT

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.

> 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
[quoted text clipped - 53 lines]
> > >
> > > Ed

Reply to this Message

Jean-Guy Marcil - 07 Oct 2004 00:50 GMT

Bonjour,

Dans son message, < Ed > ?crivait :
In this message, < Ed > wrote:

> 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
[quoted text clipped - 5 lines]
> 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
'_______________________________________

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Reply to this Message

garfield-n-odie - 07 Oct 2004 00:56 GMT

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.

> 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
[quoted text clipped - 7 lines]
>
> Ed

Reply to this Message

garfield-n-odie - 07 Oct 2004 01:03 GMT

On second thought... with 14,000 rows, you might have to do this one
column at a time.

> 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
[quoted text clipped - 18 lines]
>>
>> Ed

Reply to this Message