Using Replace command for replacing a character by an ALT+ENTER

[Guest]

Hi,
I have a large number of cell entries, all separated by a "/", but I need to
replace it by "ALT+ENTER" (the keys sequence one uses for placing several
expressions within the same cell onto separate lines of that same cell).

 

[Guest]

I have a large number of cell entries, all separated by a "/", but I need to
replace it by "ALT+ENTER" (the keys sequence one uses for placing several
expressions within the same cell onto separate lines of that same cell).

Assume source data in A1 down
In B1: =SUBSTITUTE(A1,"/",CHAR(10))
Format B1 to wrap text*, then
Copy B1 down as far as required

*Click Format > Cells > Alignment tab > Check "Wrap text" > OK

---

 

[Bernard Liengme]

Hi Peter,
With the cursor in the Replace With box: hold down the ALT key and type the
digits 010 on the numeric pad (it must be the numeric pad - a pain on a
laptop!) This enters the non-printing character LF which is what is needed
(it will not show in the box)
best wishes

 

[Don Guillett]

One way might be to use an adjacent column =substitute(a2,"/",char(10)) and
format cells to word wrap>then change formula to values and delete the
original column.
or a macro to do it for you.

 

[Don Guillett]

a macro
Sub changetowrap()
For Each c In Selection
c = Application.Substitute(c, "/", Chr(10))
Next c
Selection.WrapText = True
End Sub