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

ok on this thread I found someone with a similar problem -

http://www.microsoft.com/communities...816&sloc=en-us

I took the column over to word and replaced the two soft returns with one
soft return (didn't work - excel thought that the single soft return was a
cell break) and then a tab, but:
A. it should have a soft return, these are international addresses
B. it's a rather cheesy hack

"finnadat" wrote:

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

tried, but they aren't physical characters - excel doesn't seem to understand
two soft returns.

"[email protected]" wrote:

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

The character that Excel uses to indicate a new line within a cell is
CHAR(10).


On Thu, 10 Feb 2005 12:45:03 -0800, "finnadat"
<[email protected]> wrote:

>ok on this thread I found someone with a similar problem -
>
>http://www.microsoft.com/communities...816&sloc=en-us
>
>I took the column over to word and replaced the two soft returns with one
>soft return (didn't work - excel thought that the single soft return was a
>cell break) and then a tab, but:
>A. it should have a soft return, these are international addresses
>B. it's a rather cheesy hack
>
>"finnadat" wrote:
>
>> 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?

If you format the cell to wraptext (format|cells|alignment tab), does the box go
away and the text wrap nicely?

If yes, maybe just keep it that way???

If no (from a previous post):

Chip Pearson has an addin that can help you find out what is exactly in that
cell.
http://www.cpearson.com/excel/CellView.htm

If it turns out to be "nice", you can use Edit|Replace
what: alt-xxxx (use the numbers on the number keypad--not above the
QWERTY keys)
with: (spacebar) or whatever you want.

This can work nicely with alt-enters (alt-0010), but will fail with other
characters (alt-0013 for example).

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

finnadat wrote:
>
> 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?

--

Dave Peterson