Removing Hard Returns

[John]

Is there any way to do a find and replace on hard returns
in a text cell. I need to separate out address blocks into
columns and the addresses are typed in

thanks so much

 

[Peo Sjoblom]

Do edit>replace, place cursor in the [what] box, hold down alt key
while typing 010 on the numpad, leave [with] blank or put a space there.

 

[Ron de Bruin]

Hi John

Gord Dibben posted this solution John

Select your range of data.
Edit>Replace

What: ALT + 0010(from the number pad keys)
With: enter a space or leave this blank

Replace All.
You may have to enter ALT + 0013 instead of 0010.
Note: when you type in the 0010 you won't see anything, but it is there.



Dave Peterson this

I've never had real good luck using alt-0013 in the Replace dialog. (alt-0010
works fine, though.)

I've always used an extra helper cell and a worksheet formula:
=substitute(a1,char(13),"")

or a macro:
Option Explicit
Sub testme()
With worksheets("sheet1")
.Cells.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub


===
Char(10) in an excel cell is used for linewrap. If your box character is
char(10), maybe all you'll want to do is enable wordwrap.
(Format|Cells|Alignment Tab|WordWrap)

===
And to determine what the character really is:
=CODE(RIGHT(A1,1))
(since it's the last character.)

or a formula like:
=code(mid(a1,x,1))
(vary x = 1 to the len of a1)

And if you do this a lot, you'll want a copy of Chip Pearson's CellView addin.
http://www.cpearson.com/excel/CellView.htm

(In fact, it might be even better for you if you don't do it a lot!)

 

[John]

Thanks so much!

John

-----Original Message-----
Hi John

Gord Dibben posted this solution John

Select your range of data.
Edit>Replace

What: ALT + 0010(from the number pad keys)
With: enter a space or leave this blank

Replace All.
You may have to enter ALT + 0013 instead of 0010.
Note: when you type in the 0010 you won't see anything, but it is there.



Dave Peterson this

I've never had real good luck using alt-0013 in the Replace dialog. (alt-0010
works fine, though.)

I've always used an extra helper cell and a worksheet formula:
=substitute(a1,char(13),"")

or a macro:
Option Explicit
Sub testme()
With worksheets("sheet1")
.Cells.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub


===
Char(10) in an excel cell is used for linewrap. If your box character is
char(10), maybe all you'll want to do is enable wordwrap.
(Format|Cells|Alignment Tab|WordWrap)

===
And to determine what the character really is:
=CODE(RIGHT(A1,1))
(since it's the last character.)

or a formula like:
=code(mid(a1,x,1))
(vary x = 1 to the len of a1)

And if you do this a lot, you'll want a copy of Chip Pearson's CellView addin.
http://www.cpearson.com/excel/CellView.htm

(In fact, it might be even better for you if you don't do it a lot!)


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)







.