replacing commas with hard returns

[Dobo]

Using Excel X for Mac, I'd like to find all commas in a column and
replace them with hard returns/carriage returns (not soft returns).
Since Excel doesn't recognize the Cntrl-Option-Return combo when I use
the Replace function, I'm guessing I'll have to use a formula or a
macro. I've seen several great suggestions for PC users, but none for
Mac users.

Note: I've already tried a Substitute formula, but it won't recognize
the key-combo or the ASCII Chr(13) to represent the hard return.

Thanks.

 

[JE McGimpsey]

Using Excel X for Mac, I'd like to find all commas in a column and
replace them with hard returns/carriage returns (not soft returns).
Since Excel doesn't recognize the Cntrl-Option-Return combo when I use
the Replace function, I'm guessing I'll have to use a formula or a
macro. I've seen several great suggestions for PC users, but none for
Mac users.

Note: I've already tried a Substitute formula, but it won't recognize
the key-combo or the ASCII Chr(13) to represent the hard return.

Hmmm...

=SUBSTITUTE(A1,",",CHAR(13))

works for me. Make sure the cell(s) are set to wrap text.

 

[JE McGimpsey]

Using Excel X for Mac, I'd like to find all commas in a column and
replace them with hard returns/carriage returns (not soft returns).
Since Excel doesn't recognize the Cntrl-Option-Return combo when I use
the Replace function, I'm guessing I'll have to use a formula or a
macro. I've seen several great suggestions for PC users, but none for
Mac users.

This macro would also work:

Public Sub ReplaceCommaWithReturn()
Dim rText As Range
Dim rCell As Range
On Error Resume Next
Set rText = ActiveSheet.Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
On Error GoTo 0
If Not rText Is Nothing Then
For Each rCell In rText
With rCell
If InStr(.Text, ",") Then
.Value = Application.Substitute( _
.Text, ",", Chr(13))
.WrapText = True
End If
End With
Next rCell
End If
End Sub

 

[Dobo]

That did it!! Our formulas were the same -- except I was using Chr(13)
instead of CHAR(13).

Thank you, JE Gimpsey!!

 

[Dobo]

Even though your formula worked, I'll keep this macro for reference.

Thanks again, JE.