Re: Square-box character in data import

From: Mark (jklot_at_hotmail.com)
Date: 02/18/04 

Date: Wed, 18 Feb 2004 16:06:00 -0000

Sadly, this doesn't work for me. It's not a formatting problem I don't
think - it's a character that just shouldn't be there! I Cut a sample of the
character and Pasted it into the command =CODE("x") in place of the x and
Excel reported the number 10 i.e. ASC(10), the line feed character I think.
It looks like Excel is displaying the line feed character as a square box.

Unfortunately, =SUBSTITUE(c1,CHAR(10),,) just leaves me with a mailing
address on a single line, still with square boxes...

"Chris O" <c.nospamo@aol.com> wrote in message
news:c101jg$ltt$1@hercules.btinternet.com...
> Hi Mark,
>
> All I did was click on the new cell with the 2 lines, then click on the
> "Format Painter" button on the toolbar (next to the Copy and Paste symbols
> on my toolbar), drag the paintbrush symbol to the cell you want to change,
> and "click"
> Or I was able to drag the symbol to highlight the whole column and click,
> and that fixed the format down the whole column.
>
> HTH
>
> Chris O
> ________________________________________________________________-
> "Mark" <jklot@hotmail.com> wrote in message
> news:ehGejTj9DHA.1948@TK2MSFTNGP12.phx.gbl...
> > Thanks, Chris - with your help I'm nearly there!
> >
> > I've done the first half of what you suggest and got a cell with two
lines
> > and no square-box character. But then I got lost with your second step -
I
> > copied that cell and did a past special format into one of my problem
> cells,
> > but nothing changed - I still get the square-box. What am I missing?
> >
> > Alternatively, how do I fill in the following formula?
> >
> > =SUBSTITUTE(c1,CHAR(10),??) where ?? is the code for Alt+Enter
> > "Chris O" <c.nospamo@aol.com> wrote in message
> > news:c0vvkv$95v$1@sparta.btinternet.com...
> > > There is probably a much more elegant way than the following, but it
> > worked
> > > for me -
> > >
> > > In an empty cell type some text, hit ALT + Enter and then some more
> text,
> > > then hit Enter. You should get 2 lines of text in the cell. Paste the
> > format
> > > of this cell to the cell with your square box char - or to the column
> with
> > > all your square box chars. The square box(es) should then disappear
and
> > > multiple lines will display.
> > >
> > > Chris O.
> > >
_______________________________________________________________________-
> > > "Mark" <jklot@hotmail.com> wrote in message
> > > news:OYldKwi9DHA.3348@TK2MSFTNGP09.phx.gbl...
> > > > Hi, I've got a puzzling one. When importing a data file into Excel I
> see
> > a
> > > > square-box character at the end of each line. I've tried
> copying/cutting
> > > one
> > > > of these characters to paste into the search/replace dialog, but
> nothing
> > > > pastes and so I can't get rid of it. What do I do? I've got 9000
> > records,
> > > > each with a multi-line entry for a mailing address in Column C and
> each
> > of
> > > > those lines (apart from the last for each record's mailing address)
> has
> > > this
> > > > unwanted character. It's obviously the CrLf character created when I
> > press
> > > > the keyboard's Enter key...
> > > >
> > > > The original file was created in my database and exported in ASCII
> > > > tab-delimited format. I did a right-click Open with Microsoft Excel
to
> > get
> > > > the file into Excel with the data in the correct columns. (Doing an
> > Excel
> > > > File Open command and stepping through the data import wizard left
> each
> > > > element of the mailing address on a separate line as though it were
a
> > new
> > > > record, with first line of mailing address in Column C and
subsequent
> > ones
> > > > in Column A - even worse than having the square-box character!)
> > > >
> > > > FYI, I've tried exporting the file from the db in ASCII CSV, DIF and
> > dBase
> > > > formats, always with the square-box character appearing. Only when I
> > saved
> > > > the data out of the database as HTML was I able to get it into Excel
> > > without
> > > > the square-box character, but then again I had each element of the
> > mailing
> > > > address on a separate line of the work***...
> > > >
> > > > Finally, just to be sure it wasn't my database, I created a simple
> text
> > > file
> > > > in Notepad and right-click Open with Microsoft Excel - and got the
> > square
> > > > box character!!
> > > >
> > > > I'm using Excel 2002 SP-2. Thanks for your help.
> > > >
> > > >
> > >
> > >
> >
> >
>
>