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.

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