Can't get rid of funny little boxes in imported data

  • Thread starter Thread starter Lee Jeffery
  • Start date Start date
L

Lee Jeffery

Using Excel 97 on WINNT (but not for much longer she grinne
hopefully!)

Evening All,

Can anybody help me with the correct character for my problem, please?

Customers create their orders through a web form and the information i
downloaded into a csv file once a week. Unfortunately, where customer
are entering their delivery address, they can hit the Enter button t
type further details on a new line. This appears as funny little boxe
in the csv file cells.

I've tried a find and replace but I'm obviously not using the righ
character for the find. :confused: Please help.

TIA.

Lee Jeffery :
 
I bet they actually hit the alt-enter to force the newline in the cell.

You could get rid of the box character by formatting those cells to allow
wordwrap. Or you could change those characters to a space character.

Select the cells
format|cells|alignment tab|Check Wrap Text.

Or ctrl-a (twice when you get to xl2003!) to select all the cells.

then edit|replace
what: alt-0010 (hit an hold the altkey and type 0010 from the numeric
keypad--not the keys above QWERTY)
with: (spacebar)
replace all

=======
It may not look like anything got entered into that "what" box, but try it.

If that doesn't work, then those characters may not be alt-enters (Char(10)'s).

You can use Chip Pearson's addin:
http://www.cpearson.com/excel/CellView.htm
to find those funny characters.

Then you could use a macro to clean them up:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(yy), Chr(zz))

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next iCtr

End Sub

Change the yy/zz to what Chip shows (and you can drop ", chr(zz) if you only
have one offending character).

(And I changed them to space characters.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Hi Dave,

Thank you for this. I'll try it as soon as I get to work and can ge
hold of a file with the offending characters.
I'll keep you posted!

Lee Jeffery :
 
Dave,

I finally found time to follow your suggestion and I am very gratefu
to you for your assistance and to Chip Pearson for making his Cellvie
add-in available.

Your Cleanemup macro works like a dream and I have added it to anothe
macro I use to format the spreadsheet as an order form .

I really need to learn the terminology as I found your original pos
with this suggestion in the Microsoft newsgroup and could have save
everyone some bother had I known that "funny little boxes" shoul
really be called non-printing characters.

Many thanks, again.

Lee Jeffery :
 
Back
Top