Special character (copied from Word to Excel)

UHsoccer

Well-known Member
Joined
Apr 3, 2002
Messages
1,023
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi UHsoccer

Have you tried the CLEAN function?

If A1 has nonprintable characters

=CLEAN(A1)

Hope this helps
PGC
 

UHsoccer

Well-known Member
Joined
Apr 3, 2002
Messages
1,023
That seems to remove it from the spreadsheet.

Is there an equivalent function in VB to accomplish that?
 

UHsoccer

Well-known Member
Joined
Apr 3, 2002
Messages
1,023

ADVERTISEMENT

Richard,

I am using the follwing code
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

Well-known Member
Joined
Apr 3, 2002
Messages
1,023
According to the MS documentation

Clean: Removes the nonprintable character, CHAR(7)
 

UHsoccer

Well-known Member
Joined
Apr 3, 2002
Messages
1,023
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

I think you'll need to amend to:

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

Forum statistics

Threads
1,141,705
Messages
5,707,973
Members
421,539
Latest member
zuniBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top