Import problems from access to excel

[barry]

I'm importing data (text,numbers,date) from an access
table to excel using copy/paste. When i paste the data
into excel, it includes in some of the text fields, a
square symbol, wheather the cell is empty or not. This
only occurs in some of the columns.
Also if i manually delete the square symbols and then
format the cells to remove word wrap, the squares re-
appear or double if i hadn't removed them.

 

[Debra Dalgleish]

They may be line break and carriage return characters. You can use
Edit>Replace to remove them:

1. Choose Edit>Replace
2. Find What: hold the Alt key, and on the number keypad, type 0010
(nothing will appear in the Find What box)
3. Replace With: press the spacebar to enter a space character
4. Click Replace All
5. Find What: hold the Alt key, and on the number keypad, type 0013
6. Replace With: press the spacebar to enter a space character
7. Click Replace All

 

[barry]

Thanks for the help!
The 0010 removed any single squares or in the case of
double-squares, removed one of the squares.
However this would only work the first time and any
further replaces were unsuccessful.

The 0013 had no effect.
Any other ideas on how remove the final squares?

 

[Dave Peterson]

I've never had real good luck using alt-0013 in the Replace dialog. (alt-0010
works fine, though.)

I've always used an extra helper cell and a worksheet formula:
=substitute(a1,char(13),"")

or a macro:
Option Explicit
Sub testme()
With worksheets("sheet1")
.Cells.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub


==
You could use a formula like this to determine what that box character really
is:
=code(mid(a1,x,1))
(vary x = 1 to the len of a1)

And if you do this a lot, you'll want a copy of Chip Pearson's CellView addin.
http://www.cpearson.com/excel/CellView.htm

(In fact, it might be even better for you if you don't do it a lot!)