removing squares and lines in squares that really should be paragr

F

finnadat

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

finnadat

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

Myrna Larson

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

Dave Peterson

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
 
Top