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
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=523214

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??


--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=523214

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

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"bxc2739" wrote in
message ...

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
------------------------------------------------------------------------
bxc2739's Profile:
http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=523214

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

Thanks


--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=523214

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 and
Replace"
fields in Excel so I can get rid of the THOUSANDS of these scattered
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/


--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=523214

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

-------
Sometimes those funny characters don't work in the edit|Find dialog.
alt-0010 (alt-enters) work ok. char(13) have never worked for me.



bxc2739 wrote:

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 and
Replace"
fields in Excel so I can get rid of the THOUSANDS of these scattered
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/

--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=523214

--

Dave Peterson

EditReplace

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

On Thu, 16 Mar 2006 13:12:00 -0600, bxc2739
wrote:


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 and
Replace"
fields in Excel so I can get rid of the THOUSANDS of these scattered
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/