How do I insert a carriage return in a text formula?

[fearless613]

I know I can do it manually with alt+enter, but how can I insert a carriage return in a formula? Using char(10) or char(13) just gives me the little black box.

Thanks,

Jon
This message was edited by fearless613 on 2002-05-30 09:44

 

[tinkywinky]

posted a summary here that should explain all:

http://www.mrexcel.com/forum/showpost.php?p=2574002&postcount=7

 

[prbailey]

Try TEXT(CHAR(ASC(10)). For example: =B3&TEXT(CHAR(ASC(10))&"-"&B4

 

[prbailey]

Update: Try TEXT(CHAR(ASC(10)),""). For example: =B3&TEXT(CHAR(ASC(10)),"")&"-"&B4. Then wrap-text the cell.

 

[shg]

Or just =B3 & CHAR(10) & "-" & B4

 

[Greg_Glynn]

I know I can do it manually with alt+enter, but how can I insert a carriage return in a formula? Using char(10) or char(13) just gives me the little black box.

Thanks,

Jon

[SIZE=-1][ This Message was edited by: fearless613 on 2002-05-30 09:44 ][/SIZE]

This worked for me. Highlight the cells you want to convert first.

Sub Replace_Soft()

Dim c As Variant


For Each c In Selection
c.Value = Replace(c.Value, " | ", Chr(10))
Next c


End Sub

 

[Greg_Glynn]

... Note: I was replacing | characters with soft returns.