VBA: How To Replace Commas With Newlines In Cells in range In Excel and vice versa

[jiobi]

VBA: How To Replace Commas With Newlines In Cells in range In Excel and vice versa.
Data in cells in the range may be missing and may contain empty rows or columns.

Input
How, To, Replace, Commas, With, Newlines, In, Cells

Output
How
To
Replace
Commas
With
Newlines
In
Cells

 

[kokano90]

VBA: How To Replace Commas With Newlines In Cells in range In Excel and vice versa.
Data in cells in the range may be missing and may contain empty rows or columns.

Input
How, To, Replace, Commas, With, Newlines, In, Cells

Output
How
To
Replace
Commas
With
Newlines
In
Cells

Try using the Text to column

 

[Rick Rothstein]

Assuming every comma will be followed by a space character...

Sub ToggleCommaToNewline()
  Dim Cell As Range
  For Each Cell In Selection
    If InStr(Cell.Value, ",") Then
      Cell.Value = Replace(Cell.Value, ", ", vbLf)
    Else
      Cell.Value = Replace(Cell.Value, vbLf, ", ")
    End If
  Next
End Sub

 

[Rick Rothstein]

Here is a better way than what I posted earlier (no loops)...

Sub ToggleCommaToNewline()
  If Selection.Find(",", , xlValues, xlPart, , , , , False) Is Nothing Then
    Selection.Replace vbLf, ", ", xlPart, , , , False, False
  Else
    Selection.Replace ", ", vbLf, xlPart, , , , False, False
  End If
  Selection.Rows.AutoFit
End Sub

 

[jiobi]

The VBA works only on the first cell.
Also, I want to reverse the process in another VBA.

 

[Rick Rothstein]

For me, either macro works on all cells in the range (one or more cells) that you select.

 

[jiobi]

"that you select" ! got it.
Now worked for me.
How to do the reverse?

 

[Rick Rothstein]

For my code, just run the same macro again... it is a toggle macro.