Re: Square-box character in data import

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Chris O (c.nospamo_at_aol.com)
Date: 02/18/04 

Date: Wed, 18 Feb 2004 15:16:47 +0000 (UTC)

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 worksheet...
>
> 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.
>
>

Relevant Pages

  • Re: Square before beginning of a new line
    ... =char, chr, vbcr is a character that'll appear as that square. ... Debra Dalgleish has some notes how to implement macros here: ... a square can be observed just before the beginning of a new line. ... would to keep the multiline structure in the cell without the square. ...
    (microsoft.public.excel.programming)
  • Re: Unwanted Square Character in .txt file
    ... I believe Clean will get anything that shows as a square, ... discovering the ascii value of the character can ... allow you to use the replace method in your code rather than doing cell by ... Tom Ogilvy ...
    (microsoft.public.excel.programming)
  • Re: Square-box character in data import
    ... I've done the first half of what you suggest and got a cell with two lines ... and no square-box character. ... > all your square box chars. ... with first line of mailing address in Column C and subsequent ...
    (microsoft.public.excel.misc)
  • Re: Form Textbox Mulitline question
    ... I am using a form to enter data into a cell in another sheet. ... small square box as the last charater. ... Is there any way to not display the small square box character in the cell? ...
    (microsoft.public.excel.programming)
  • Re: SOLUTION Square-box character in data import
    ... As Mark and Michael have said, ... Assuming the mailing address with multiple lines terminated in square-box ... The square boxes will disappear, ... It's obviously the CrLf character created when I ...
    (microsoft.public.excel.misc)