Problem with the format of cells after impoting from Access
 

rkI am a new user with vision impairment, so it is extremely likely that I
missed the help that has already been offered. For that I apologize and thank
you for your input.

I was working on a database in Access, but was having a problem because of
my vision. It was suggested that I would have better luck working in Excel
and since I have worked with it before I decided to try.

I exported my worksheet to Excel but when I openned it in Excel I
immediately noticed something was very different. It is as though every cell
has been "formatted as a mini document, complete with "wrap around". While I
can see the usefulness of this, and wouldn't mind being able to figure out
how to do that, this is not the place where I want such formating to be.

If someone can tell me where I made my mistake and help me get this back
working the way a new worksheet would work and look I would be very thankful.

Thanks again for your attention.
--
Siuan

Problem with the format of cells after impoting from Access
 

I should also have said that in some records is appears as if there is also
some sort of hard return imbedded as there are no cells containing enough
information to warrant a wrap around. This makes over half of the worksheet
filled with empty space.

Thanks again for your attention.
--
Siuan

Problem with the format of cells after impoting from Access
 

If the data has alt-enters (char(10)'s or line feeds), then you'll see square
boxes if the cell isn't formatted to wrap text (select all the cells,
format|Cells|alignment tab to toggle wrap text).

But there are other characters that can cause those square characters.

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(10), Chr(13)) '<--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 (or ctrl-j) (aka: alt-enters) work ok. char(13) has never worked for
me.

==============

But I have no idea what will happen if/when you put your data back into Access.

Siuan wrote:

I should also have said that in some records is appears as if there is also
some sort of hard return imbedded as there are no cells containing enough
information to warrant a wrap around. This makes over half of the worksheet
filled with empty space.

Thanks again for your attention.
--
Siuan

--

Dave Peterson