CHAR(10)

[Guest]

I have a spreadsheet that someone else created. This spreadsheet has some
sort of line break I am not familiar with. I thought it was char(10) at
first, but it will not replace. It shows two boxes when the cell format is
unwrapped, however when it wraps, it still shows one box. I have tried to
replace by alt 010, but it will not. The way it appears in the cell is like
when you get the box before you turn on wrap text when using char(10), but it
shows two instead of one. When wrap text is turned on the second string
drops, however one box is still appearing....any suggestions?

 

[Bruno Vermeersch]

Maybe it is the complete one, i.e. CR+LF
So Char(10) + Char(13)
(or the other way round, I don't remember ...)

Bruno

 

[Peo Sjoblom]

Copy the box to an empty cell, assume you copy it to B4, now use a formula
like

=CODE(B4)

that should give you the char
(I bet it is char 13)

 

[Guest]

You were correct, the code is alt 013, but I am trying to replace that and
the edit/replace function does not work. I am holding down alt and typing
013, but nothing happens?

 

[Jason Morin]

While holding down the ALT key, key in:

0013

on your numeric keypad.

HTH
Jason
Atlanta, GA

 

[Dave Peterson]

I've never had any success with alt-0013 in the Edit|Replace dialog. (Alt-0010
works fine--as does ctrl-j <equivalent to alt-0010!>).


If you want to replace these characters with something else (space or
nothing???), you could use a macro to do the edit|replace's:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13))

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

End Sub

Should it be:
replacement:=""
or
replacement:=" "
???

Chip Pearson has a very nice addin that can help identify those funny
characters.

http://www.cpearson.com/excel/CellView.htm

Just keep adding those Hex codes you found using Chip's addin.

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