How to search for end-of-cell marker

[bxb7668]

I often need to do a find-and-replace in a table where I need to use a
special character to indicate the end-of-cell marker. An example would
be to remove all trailing spaces in the cells. A paragraph special
code ( ^p ) doesn't work. Is there such a code?

Brian

 

[JoAnn Paules [MSFT MVP]]

Why not turn on your non-printing characters?

 

[bxb7668]

That's what I do now. With approximately 200 cells, it takes a long
time to manually remove the spaces. The extra spaces cause the cells
to be odd sizes. I cannot simply remove multiple spaces because there
are places in the middle of the data that need those spaces. I tried
converting the table to text with tab separators, but the formatting
is all messed up when converting back to the table.

Brian

Why not turn on your non-printing characters?

JoAnn Paules
MVP Microsoft [Publisher]

I often need to do a find-and-replace in a table where I need to use
a special character to indicate the end-of-cell marker. An example
would be to remove all trailing spaces in the cells. A paragraph
special code ( ^p ) doesn't work. Is there such a code?

 

[Dian D. Chapman, MVP]

This macro will remove all the spaces in all the cells in a table.
Just put the cursor somewhere within the table and run this code...

Sub TrimCellSpaces()

Application.ScreenUpdating = False

Dim myCell As Cell
Dim myTable As Table
Dim strCellContent As String

Set myTable = Selection.Tables(1)

For Each myCell In myTable.Range.Cells
With myCell.Range
strCellContent = Left(.Text, Len(.Text) - 2)
strCellContent = Trim(strCellContent)
.Text = strCellContent
End With
Next myCell

Application.ScreenRefresh
Application.ScreenUpdating = False


End Sub

Note...if you don't know how to use this code...read this TechTrax
article:

Sharing Macros
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=166

Dian D. Chapman
Technical Consultant, Microsoft MVP
MOS Certified, Editor/TechTrax

View Hurricane Humane Updates: http://www.mousetrax.com/pets.html
Free Microsoft Tutorials: http://www.mousetrax.com/techtrax
Learn VBA the easy way: http://www.mousetrax.com/techcourses.html

That's what I do now. With approximately 200 cells, it takes a long
time to manually remove the spaces. The extra spaces cause the cells
to be odd sizes. I cannot simply remove multiple spaces because there
are places in the middle of the data that need those spaces. I tried
converting the table to text with tab separators, but the formatting
is all messed up when converting back to the table.

Brian

Why not turn on your non-printing characters?

JoAnn Paules
MVP Microsoft [Publisher]

I often need to do a find-and-replace in a table where I need to use
a special character to indicate the end-of-cell marker. An example
would be to remove all trailing spaces in the cells. A paragraph
special code ( ^p ) doesn't work. Is there such a code?