Search for Square Characters

[Guest]

Someone has asked me to go through a very large Excel document and remove all
of the square characters. I don't know how they got there, but they are Arial
font and they appear at the end of some sentences after periods. Sometimes
there is one, sometimes there are two. Is there some way I can do this more
quickly? Any help is appreciated.

 

[Chip Pearson]

Use the CLEAN function. Insert a column next to the column
containing the square characters (these are unprintable
characters, probably line breaks), and enter =CLEAN(A1) where A1
is the first cell with the unprintable characters. Copy this
formula down as far as you need to go. Then copy these cells, and
Paste Special Values back on top of the original data.

 

[Guest]

Try this:
Edit one of the cells
Select only the square character
Hold down the [Ctrl] key and press C (that will copy the character)
Press [ESC] (to stop editing the cell)

Select any single cell
Edit>Replace
Find what: (Hold down the [Ctrl] key and press V here to paste the character)
Replace with: (leave this blank)
Click the [Replace All] button

That should replace all of that type of character with nothing.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Gord Dibben]

BCBC

These are most likely line-feeds commonly known as carriage returns.

Do they disappear when you enable wrap-text?

If so................

Try Edit>Replace

what: ALT + 0010(on the numpad)

With: nothing or space

ALT + 0010 is achieved by holding the ALT key and typing 0010 on the numpad
which is located at right side of keyboard.

If no joy, try 0013


Gord Dibben Excel MVP

 

[Peo Sjoblom]

If this is a common task you might use a little macro

Sub CleanSquares()
Selection.Replace What:=Chr(10), _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub


press alt + F11, click insert module and paste the above, press alt + Q to
close the VBE, now select the range and press alt + F8 to run the macro

This will replace it with a blank (not a space) so if you want a space you
can change Replacement:="" to Replacement:=" " and if it doesn't work you
can try to change Chr(10) to Chr(13)

for info on how to install macros see

http://www.mvps.org/dmcritchie/excel/getstarted.htm

http://www.mvps.org/dmcritchie/excel/install.htm

 

[Dave Peterson]

One of the things that shows up as a square is the alt-enter (alt-0010). This
is used to force a new line within the cell.

If you have format|cells|alignment tab|wrap text unchecked, you may want to
check it to see if the text lines up nicer.

If it doesn't help (or it's already checked), then it's not the alt-enter.

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

It may help with the code that Peo posted.

 

[Michael J. Strickland]

....

Try making a new column to the right of each existing column and use the
Substitute function to replace Char(10) with spaces (or whatever you want to
use.

Example: For column A, insert a column to the right of it and put:

SUBSTITUTE(A1,CHAR(10)," ")

Then use Copy & Paste Special to copy the values of column B into column A.


--