Special character (copied from Word to Excel)

[UHsoccer]

I copied a word document into Excel and need to process it with VB

I need to remove special characters that do not display in Excel. In word when I turn on the display of hidden characters they display as a little circle with a line pointing outward from the circle at 45, 135, 225 and 316 degrees. They are all in table cells.

I have tried to display it in the screen but was unable to do so.

Any help is appreciated

 

[pgc01]

Hi UHsoccer

Have you tried the CLEAN function?

If A1 has nonprintable characters

=CLEAN(A1)

Hope this helps
PGC

 

[UHsoccer]

That seems to remove it from the spreadsheet.

Is there an equivalent function in VB to accomplish that?

 

[Richard Schollar]

Hi

Application.WorksheetFunction.Clean(YourStringHere)

Richard

 

[UHsoccer]

Richard,

I am using the follwing code

For cur = 1 To 200   '  for the end
        Application.WorksheetFunction.Clean (Range("A" & cur))
        If Len(Trim(Range("A" & cur))) = 0  Then GoTo delRow
        GoTo nextRow  '  row not to be deleted
next cur

Once finished, I copied the content of cell A ro word and the strange characters are still there.

 

[UHsoccer]

According to the MS documentation

Clean: Removes the nonprintable character, CHAR(7)

 

[UHsoccer]

Update, here is the full text

Clean: Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed

 

[Richard Schollar]

Hi

I think you'll need to amend to:

For cur = 200 To 1 Step -1   '  for the end
        Range("A"&cur).Value = Application.WorksheetFunction.Clean (Range("A" & cur).Value)
        If Len(Trim(Range("A" & cur))) = 0  Then GoTo delRow
        GoTo nextRow  '  row not to be deleted
next cur

Best regards

Richard