Square-box character in data import

[Mark]

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.

 

[Mark Graesser]

Mark
Try using the CLEAN function in a helper column. Then Copy and Paste_Special>Values over the original data, and delete the helper column

Good Luck
Mark Graesse
(e-mail address removed)

----- Mark wrote: ----

Hi, I've got a puzzling one. When importing a data file into Excel I see
square-box character at the end of each line. I've tried copying/cutting on
of these characters to paste into the search/replace dialog, but nothin
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 o
those lines (apart from the last for each record's mailing address) has thi
unwanted character. It's obviously the CrLf character created when I pres
the keyboard's Enter key..

The original file was created in my database and exported in ASCI
tab-delimited format. I did a right-click Open with Microsoft Excel to ge
the file into Excel with the data in the correct columns. (Doing an Exce
File Open command and stepping through the data import wizard left eac
element of the mailing address on a separate line as though it were a ne
record, with first line of mailing address in Column C and subsequent one
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 dBas
formats, always with the square-box character appearing. Only when I save
the data out of the database as HTML was I able to get it into Excel withou
the square-box character, but then again I had each element of the mailin
address on a separate line of the worksheet..

Finally, just to be sure it wasn't my database, I created a simple text fil
in Notepad and right-click Open with Microsoft Excel - and got the squar
box character!

I'm using Excel 2002 SP-2. Thanks for your help

 

[Mark]

Thanks, I tried the CLEAN function and it stripped out all the carriage
return characters so I ended up with all of the mailing address on a single
line! Obviously, that's not what I want!!

Mark,
Try using the CLEAN function in a helper column. Then Copy and

Paste_Special>Values over the original data, and delete the helper column.

 

[Chris O]

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]

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]

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]

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

 

[Keith R]

Joining the thread late;
I had the same problem on data imported from our statistical package- I
ended up parsing the cell(s) on char(10) and I think char(13) and dropped
everything into separate cells (I needed to separate the data for our
purposes, perhaps you don't). You can add a new column and use the =find()
function, or in VBA I believe it would be the instr() command.
Example in cell C2, trying to parse cell B2: (untested)
=Left(B2,find(Char(10))-1) & " " & Right(B2,len(B2)-find(char(10)))
if you need the different lines in different cells, then add another column
and just use the first and second parts of this formula in different cells.

HTH,
Keith

 

[Mark Graesser]

Mike,
The problem you are running into is becuase of the character 13. The end of each line of your data is followed by a new line feed (char 10) and a carriage return (char 13). Excel uses the char 10 to create the line feed within a cell, so you need to remove the char 13.

Use this formula in a helper column:

=SUBSTITUTE(E1,CHAR(13),)

If the resulting values come out in a single line you will need to turn on the wrap text in the alignment format.

I also noticed that some of your data has blank lines in it. If you want to remove these you can use this formula on the results of the previous formula:

=SUBSTITUTE(F1,CHAR(10)&CHAR(10),CHAR(10))

You could also put the two formulas together to get:

=SUBSTITUTE(SUBSTITUTE(E1,CHAR(13),),CHAR(10)&CHAR(10),CHAR(10))

Hopefully this will give you what you need. Let me know how it works out.

Good Luck,
Mark Graesser
(e-mail address removed)

 

[Michael J. Strickland]

It looks like there are extraneous cariage returns (ASCII 13) in the
address.

Assuming A is the column with the addresses:

1. Insert a new column (B)

2. Copy the format of column A to column B
(Copy column A to column B, Select column B, Rt. Click and select "Clear
Contents")

4. Put the following formula in column B cells (replaces all CRs with
nothing):
=SUBSTITUTE(A1,CHAR(13),"")

5. Then convert the column formulas to values
(Copy & Paste Special(Values))

 

[Mark]

Thanks, all. After some digging around, here's how to do it:

As Mark and Michael have said,

Assuming the mailing address with multiple lines terminated in square-box
characters (They are un-needed CHAR(13) carriage return characters) is in
cell A1 then in B1 try a formula

=SUBSTITUTE(A1,CHAR(13),)

THEN format the cell to turn word-wrapping on and set the column width to
accommodate the widest cell. The square boxes will disappear, but the
mailing address will appear correctly spread over several lines.

Have fun!