Help with Special characters

Frank3923

Board Regular
Joined
Jan 20, 2003
Messages
244
I have a worksheet, with imported text. I would like to be able to do a find and search for the text in "A1". Locate the text as described, then be able to clear the columns of text in columns "B" thru "F". The "HTML" does show the special character, before PAGE, it is the box [] character. Any suggestions, or help would be appreciated.
MrEXCEL-Xample.xls
ABCDEF
1PAGE218:008:4602MAR2004
2RPTDATEDRIVER..DFMDTOLMILESOMILES
3
42/12/2004FARRATLAXSAC3780
5SACRNO1550
6
7
82/9/2004BISHORRNOSRO2390
9SRORNO2390
Sheet1
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi,

I can't see what character appears before 'PAGE':-

BORDER-BOTTOM: #d4d0c8 0.5pt solid">PAGE</TD>

You could try selecting a cell that contains one of these characters and running the macro below to identify the ASCII value of the rogue char. Post back when you have that info.

Code:
Sub GetCharacters()
    Dim lng As Long
    Dim strMsg As String

    For lng = 1 To Len(ActiveCell.Value)

        strMsg = strMsg & Asc(Mid$(ActiveCell.Value, lng, 1)) & ", "

    Next lng


    strMsg = "The ASCII values of the characters in the active cell are:" & vbCr & strMsg

    MsgBox strMsg

End Sub
 
Upvote 0
If I'm guessing right you want to do this lotsa times, that's the issue? If so, you can select the used area (End, Home, Ctrl+Shift+Home) and Data | Filter | Autofilter. Pick your []Page off the list (or pick Custom, Contains, Page) then just select and clear the cells you want. Does that help?
 
Upvote 0
Thank you in advance for the response's.

DK - I ran the macro you provided, I received the following information from the macro

The ASCII value of the characters in the cell are 12,80,65,71,69
 
Upvote 0
OK, that would indicate that 12 is the unprintable character - I think it's a new page/form feed character. Anyway, this single line macro should get rid of them:-

Code:
Sub RemoveUnprintable()
ActiveSheet.UsedRange.Replace what:=Chr$(12), replacement:=""
End Sub
 
Upvote 0
An other way...

Try to use CLEAN function to remove the nonprintable character, CHAR(12), from the text.

Select a blank cell enter :

=CLEAN(A1)

Then,>>copy>>paste special>>values-->> shift back to A1

HTH
 
Upvote 0
DK- Your solution, worked great. It removed the special character. Is there a simple macro, that would remove the contents of columns B through F, is A1 consisted of the word "Page"
 
Upvote 0
One of the easiest ways of determining the characters in a cell is to use Chip Pearson’s “Cell View” add-in. The add-in allows you to see the exact characters in a cell (including non-printable characters), as well as their character codes.
http://www.cpearson.com/excel/CellView.htm

When you install this add in, it will add an item to your View menu called "View Cell Contents". Select a cell then click the menu item to display a dialog box that shows the cell’s contents, together with the character codes.

Regards,

Mike
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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
Back
Top