Square-box character in data import

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.


0
jklot (5)
2/18/2004 2:32:28 PM
excel.misc 78881 articles. 5 followers. Follow

10 Replies
971 Views

Similar Articles

[PageSpeed] 1

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
mark_graesser@yahoo.co
    
     ----- 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
    
    
     
0
anonymous (74721)
2/18/2004 2:51:05 PM
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 Graesser" <anonymous@discussions.microsoft.com> wrote in message
news:B2FFF2DA-80F3-415C-8869-9C6CDC8FE1BA@microsoft.com...
> 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 Graesser
> mark_graesser@yahoo.com
>
>      ----- Mark wrote: -----
>
>      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.
>
>
>


0
jklot (5)
2/18/2004 2:59:42 PM
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.
>
>


0
c.nospamo (6)
2/18/2004 3:16:47 PM
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 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.
> >
> >
>
>


0
jklot (5)
2/18/2004 3:35:48 PM
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 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.
> > >
> > >
> >
> >
>
>


0
c.nospamo (6)
2/18/2004 3:50:09 PM
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 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.
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
jklot (5)
2/18/2004 4:06:00 PM
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" <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 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.
> > >
> > >
> >
> >
>
>


0
nospam7515 (2084)
2/18/2004 6:09:56 PM
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
mark_graesser@yahoo.co

0
anonymous (74721)
2/18/2004 6:16:06 PM
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))



-- 
---------------------------------------------------------------
Michael J. Strickland
Quality Services                             qualityser@att.net
703-560-7380
---------------------------------------------------------------
"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.
>
>


0
2/18/2004 7:18:11 PM
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!

"Michael J. Strickland" <qualityser@worldnet.att.net> wrote in message
news:TTOYb.45561$hR.1038578@bgtnsc05-news.ops.worldnet.att.net...
> 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))
>
>
>
> -- 
> ---------------------------------------------------------------
> Michael J. Strickland
> Quality Services                             qualityser@att.net
> 703-560-7380
> ---------------------------------------------------------------
> "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.
> >
> >
>
>


0
jklot (5)
2/18/2004 9:35:25 PM
Reply:

Similar Artilces:

Insert data into Combo
Mistake #1 Thought I'd improve on this process: A user goes to select their name from a combo, but it's not in the list, so I have a button that opens a User entry pop-up form to add their name. They close the entry form and now must select their name from the combo. I want to dump the user entry form name into the combo when they close the user entry form so they don't have to select it. My problem is loading the new ID into the combo so the query can run from the combo data. The row source bound column is MbrID, with the second column being LFName. I've t...

Gappy data in line charts?
I have data series that may have gaps in them. For instance, this series is typical: 1 yr 70 2 yr 87.5 3 yr 110 4 yr 120 5 yr 129 6 yr 7 yr 8 yr 9 yr 10 yr 162.5 For other series the gaps may appear in other places. I would like to chart all of these (up to five series) on a single chart. I can do this, but the gaps make the line "disappear" where there are gaps. For instance, in the case above there is a nice like from 1 to 6, then a single dot at 10. Is there some way to tell it to ignore the gap and draw straight lines over them? Maury If you put "=NA()" in...

can I snap wrap points to a text box
rather than having to add individual wrap points to the edge of a frame, which is never as accurate anyway, can they be set to 'snap' to a frame (eg the ellipse) so that they are perfectly inline, (and which would of course be a lot quicker)? Edit points will not snap. There are options for edit points, select a point, right click. If you hold down control, the cursor will turn into an x, you can delete a point with a click. Truly a good draw program would be preferable. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com &q...

RE: See these important package for Microsoft Internet Explorer
--tmvxmbbrwyph Content-Type: multipart/related; boundary="jvjildjfazdb"; type="multipart/alternative" --jvjildjfazdb Content-Type: multipart/alternative; boundary="fkswystetxtsicfu" --fkswystetxtsicfu Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Customer this is the latest version of security update, the "September 2004, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now...

lookup formula not picking up correct data
I using Excel 2003 to create a "Lookup" formula to calculate grade points and grade point averages. I want to insert the grade and have the grade converted to a grade point then multiply by the number of credits for the class. I created a vector with the grade and a corresponding vector with the grade point. The formula I am using is =LOOKUP(D6, B1:N1, B2:N2) where D6 is the lookup value (the grade); b1:n1 is the lookup vector; and b2:n2 is the result vector. The result is that I get an "#N/A" error message when I input some grades, I get the incorrect gra...

removing squares and lines in squares that really should be paragr
Exported 4000+ records from outlook to Excel - used the default map and it lumped business address into one field. The problem is that at the end of a line there is a bold line in the cell and when you try to edit the cell the formula bar shows it as a square. I've tried search and replace, but excel doesn't have a special character feature and I'm @ wits end - any ideas? hi, if it is just a line that shows up as a square, do a find and replace all. >-----Original Message----- >Exported 4000+ records from outlook to Excel - used the default map and it >lumped bu...

Showing Percentage Value Data Label in a 100% Stacked Column Chart
I'm using Excel 2004 for Mac. I have created a 100% Stacked Column Chart and I want to show the percentage data label on each data series but the tick box is greyed out under 'Format data Series', sub-tab labels. Can anyone tell me how to enable this feature? ...

weighted least square regression
How can weighted least square regression be done in Excel? See http://www.mathworks.com/access/helpdesk/help/toolbox/curvefit/ch_fitt5 ..html Assume that the actual x-values range is named XVals, the X values range including the column of all 1s is named X, the weights range is W and the Y values range is Y. Then select the same number of consecutive cells in a column as the number of columns in name X, and enter the *array* formula =MMULT(MINVERSE(MMULT(TRANSPOSE(X),W*X)),MMULT(TRANSPOSE(X),W*Y)). The above formula assumes that all the necessary computations can be done safely. Alter...

how to add text box
Is it possible to add a text box to a document, but without the black border around the text box? It is. Double-click the border of your textbox, then, under "Line", select the colour you'd like to use, or simply use "No line" to get rid of the lines altogether. On 29/11/06 9:45, in article 1164789931.183526.112550@h54g2000cwb.googlegroups.com, "lawengin" <skho@iinet.net.au> wrote: > Is it possible to add a text box to a document, but without the black > border around the text box? > -- Michel Bintener Microsoft MVP Office:Mac (Entourage...

Complicated data entry ?
I would like to have a spreadsheet on which I can enter services automatically. Problem is each differrent service has different numbers of elements which make it up. For example: FRAME PORT LINE PVCa PVCb ROUTER ATM PORT LINE SCR SMTP SUBS TRAFFIC AV SCAN And I want, on another sheet, to choose (from a drop down), one of the services (in this case, FRAME or ATM or SMTP) and get the service elements automatically added so the result looks like FRAME PORT FRAME LINE FRAME PVCa FRAME PVCb FRAME ROUTER SMTP SUBS SMTP TRAFFIC SMTP AV SCAN I'...

how to detect mouse double click on edit box
I was wondering if some could tell me how to detect whether the mouse has been double clicked on an edit box. I tried the standard mouse double click function,but that only works on the form itself. Z.K. Subclass CEdit and put an OnLButtonDblClk handler in it. joe On Sun, 03 Jul 2005 20:00:58 -0700, "Z.K." <nospam@nospam.net> wrote: >I was wondering if some could tell me how to detect whether the mouse >has been double clicked on an edit box. I tried the standard mouse >double click function,but that only works on the form itself. > > Z.K. Joseph ...

Invisible square parentheses
Some of the sentences in my document appear in square parentheses which are not printable or visible in print layout. What are those? I suppose it is a kind of formatting, but couldn't find it out. Thanks for your help Probably bookmarks, if by "print layout" you mean Print Preview. Bookmark brackets don't print; they can be toggled at Tools | Options | View or the corresponding section of the Word Options in Word 2007. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "gattaccio" <gattac...

Pass data to new form
When the below code opens a new form I want it to pass the Me.CUSTOMER_NUM = stCustId. When the new form is open the Customer_num field is blank. Private Sub cmdNote_Click() On Error GoTo Err_cmdNote_Click Dim stDocName As String Dim stLinkCriteria As String Dim stCustId As String stDocName = "frmNotes" stCustId = Me.CUSTOMER_NUM If [NotesID] >= 1 Then stLinkCriteria = "[NotesID]=" & Me![NotesID] 'stLink = "[GroupID]=" & Me![GroupID] DoCmd.OpenForm stDocName, , , stLinkCriteria ...

Check off boxes
Publisher 2003 in use. I am creating a questionnaire that will be included in a newsletter. I am using the publisher software to make this form. Somewhere, in the past, I saw a keyboard shortcut to inset checkoff boxes, however, I cannot remember the key strokes. Any help along this line will be greatly appreciated. Thank you and enjoy your day. Mike Mike Reshetar wrote: > Somewhere, in the past, I saw a keyboard shortcut to inset checkoff boxes, > however, I cannot remember the key strokes. Use Wingdings and the � or q characters. -- Ed Bennett - MVP Microsoft Publisher h...

CRM Import Wizard 11-29-05
I have a user who is getting a message that "Microsoft Outlook Cannot be Started". I have double checked that the ActiveX settings are all set to allow the plugin to run. Any ideas? Try getting the user to open the the following link:- http://crmserver/viewer/activexviewer/en/npviewer.exe Replacing "crmserver" with name of your CRM server Note:- the ActiveX control for the Import Wizard is installed as part of the same install as the Crystal Viewer ActiveX. Worth a try anyway -- Peter ...

Finding leading characters
My spreadsheet looks like this: A 1 000000 2 182068 3 198506 4 200081 5 368010 6 580905 7 680001 8 686850 9 690060 10 700012 11 701068 12 802054 13 920211 14 968902 Using EDIT/FIND, when I enter '68' in the "Find What:" box, Excel finds A2, A5, A7, A8, A11 and A14 because their values contain '68'. What do I enter in the "Find What:" box to find the cells whose values BEGIN with '68'? (In this example, it would find only A7 and A...

Where are the round rotation handles at each corner of a text box?
In Visio 2003, I'm trying to rotate text so that it aligns PERFECTLY (EVENLY) along the slanting right side of a triangle. According to the manual I'm reading, after I choose the text block (rotating) tool, I'm supposed to see "green selection handles...along with ROUND ROTATION HANDLES AT EACH CORNER OF THE TEXT BOX FRAME." This works in Visio 2002...but in Visio 2003 I see ONLY the rotation handle on top of the text box frame, which (in the case of a triangle) only rotates the text box in 15 degree increments, so that I cannot align my text evenly along the slan...

One data series and two y-axes in an Excel chart?
I would like to produce graph of a single data series such that the left y-axis would represent the absolute value and the right axis the relative values (in percents) of the data points. An example of such a chart is the last graph on the page <http://www.abs.gov.au/websitedbs/D3310116.NSF/0/c7e40ae1fa39e31e4a2567ac001ffb61?OpenDocument> with cumulative frequency on the left axis and cumulative percentage on the right axis. Is it possible to create charts like these with Excel? kkarvinen, Yes, you can create a chart similar to your example chart by using an XY-Scatter Chart. As a...

External data properties
I have imported data into Excel 2007. The headings are the field names from teh table I imported. In Excel 2003 I could remove the table headings in "External Data Properties" by ticking a box. That option doesn't seem to be there in 2007. Any suggestions how I can do this? ...

Move a table with LOB data from one filegroup to another.
Okay, I have determined that the method I have found for moving a table from one Filegroup to another (drop and recreate the primary key on the filegroup) does not move LOB data in SQL Server 2005. According to BOL the method for setting this storage is in CREATE TABLE (TEXTIMAGE_ON). So, I am assuming I will have to rename the existing table (I was planning on using sp_rename) and it's index, and then re-creating it on the new filegroup, and then copying the data using insert [tablename] select * from [renamedtable] drop table [renamedtable Which has 'worked...

Combo Box Inactive
After opening my Excel file a Combo Box that I created is inactive and won't respond to my mouse click. I can only get it to work by going to the forms menu, click to edit the combo box, and then exit form editing to make it work. How do I get it to work automatically? ...

Font letter & numbers turning in to empty square boxes
Pub 2003 started to change numbers into empty square boxes. The problem is I have 16 pages and to create or carry over the info would take 2 days to fix. It appears it happens in the present file but the same info in a new pub file works. There are no viruses. XP OS 1 What causes this? 2 Can I import or copy the present file into a new pub2003 filw w/o bringing over the corruption? The troublesome font is Arial Narrow / Garamond but it works fine in all other programs. 3 Can I repair the present pub 2003 file? I have twiddled with this and I can't find anything on the web. It could be a ...

Data Validation #33
I'm trying to use excel for use in a golf draft. What I want to do i use data validation to form a drop down list where people pick thie golfers. You have to pick four different golfers out of a group o eight. But what I want to do is not have the golfers already picke show up on the list of the 2nd ,3rd and 4th golfers. So if you pic Tiger Woods first he does not show up again on the list when you mak your second pick, and so on. Does anyone have any solutions. thank very much. jory Dimon -- riderfa ----------------------------------------------------------------------- riderfan&...

How to draw line between to boxes
I am very new to Visio. I am trying to draw a flow chart. I have drawn the boxes and now I want to connect them by lines. There is an "Arrow" tab in left side pane for shpaes but it seams that it is something different. Where is the draw line command? -- Rasoul Khoshravan Azar Civil Engineer Osaka, Japan On Mon, 27 Mar 2006 16:37:01 -0800, Khoshravan <khoshravan@discussions.microsoft.com> wrote: >I am very new to Visio. I am trying to draw a flow chart. I have drawn the >boxes and now I want to connect them by lines. There is an "Arrow" tab in >left s...

pasting text into a text box
For some reason I am unable to paste text into a chart text box, (with excel 2007). Is there a trick to this? Gary K. Five minutes with Excel 2007 show that there is no obvious way to paste copied text into the embedded text box. Aren't you glad you upgraded? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Gklass" <gmklass@ilstu.edu> wrote in message news:1180231963.336906.112430@m36g2000hse.googlegroups.com... > For some reason I am unable to paste text into a chart ...