Mystery ALT key character illudes all methods of extraction! HELP!

[bxc2739]

Hi,

I am in need of help with something I have never encountered before in
Excel. At work, I have an extremely long (over 20,000+) list in excel.
The problem is that there is sporadic and unpredicatable places in the

cells that have a BLANK BOX character much like : □ . Except,
HOWEVER,
it is slightly TALLER, thinner box. I can't copy and paste it here for
you to see, becuase it won't transfer! HERE-> '
' The mysteries BOX is between the 'quotes'. Its just half line of
empty
space and it cannot be seen.

I need to be able to COPY and paste that empty box (looks like □,
expect taller and thinner and unable to replicate in Word, Excel, or
Notepad) Basically there are thousands of these annoying little things
in the thousands of cells that I have in this one excel sheet. It would
be MUCH more efficent use of time if I could SEARCH for the Empy Box and
replace it with Blank characters to get rid of /erase the annoying
boxes. But I am unable to do [search/replace] that in Excel (or word or
notepad for that matter) becuase I CANNOT copy and paste the character
into any fields, or anywhere! I have already searched on the internet
about this matter but to no avail, I have went through all of excels
related help docutments but still no use. There must be a clever way to
do this! I have tried using the MS CHaracter Map but the boxes are still
not exactly like the one I have in excel, so I cannot use it. I have
tried everyone of the alt key characters from 1 to 256 and none of them
match the one I have in excel,

can anyone please help!

Thanks,
Bo

 

[bxc2739]

another thing,
No, I just need to do a SEARCH and REPLACE, to search out all the
empty box characters and erase them from the cells. (which contain
customer information) These annoying useless empty box characters are
in between the words and letters sometimes and looks very ugly, I want
them gone.

I can't SEARCH for it becuase it cannot be copy/pasted, and I don't
know
the Alt-key for the very SPECIAL and unique elongated-version-of-a-
-blank-box-character. Basically, I DON"T HAVE ACCESS TO DO, I can
delete it manually but it will take VERY LONG.

It's no use writting a macro, becuase again, I don't have
copy/paste/selection access of it, if I did, then search and replace is
FASTEST. How can I 'select' it??

 

[Bernard Liengme]

Try http://www.cpearson.com/excel/CellView.htm
and http://www.mvps.org/dmcritchie/excel/join.htm#trimall

best wishes

 

[bxc2739]

thanks I will try your links, I hope it works.
If not, I'll post back.

Thank

 

[bxc2739]

Thanks.
Using 'Cell View' I have been able to know what character it is,

it is 013, immediately followed by another 010 in DEC form.

My question now is, how do I enter this into the "FIND/Search an
Replace"
fields in Excel so I can get rid of the THOUSANDS of these scattere
throughout all at once without doing it manually one by one?

Again, 013, 010, BUT how do I reproduce this? Using alt key commands
something else? If so, how?

thanks.

Here is a link to the screenshot of the info cell view gave
http://www.freewebs.com/bxc2739

 

[Dave Peterson]

Saved from a previous post:

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

Since you do see a box, then you can either fix it via a helper cell or a macro:

=substitute(a1,char(13),"")
or
=substitute(a1,char(13)," ")

Replace 13 with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(9), Chr(160)) '<--What showed up in CellView?

myGoodChars = Array(" "," ") '<--what's the new character?

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Gord Dibben]

Edit>Replace

What: ALT + 0013 (typed on the the num pad)

With: nothing

Replace all.

Do same for ALT + 0010

If these fail, try this macro.

Sub Remove_CR_LF()
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub


Gord Dibben MS Excel MVP