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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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?
 

Frank3923

Board Regular
Joined
Jan 20, 2003
Messages
244
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
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942

ADVERTISEMENT

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
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
  1. 2019
Platform
  1. Windows
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
 

Frank3923

Board Regular
Joined
Jan 20, 2003
Messages
244
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"
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

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