Mass find replace - inserting a linebreak

  • Thread starter Thread starter KA
  • Start date Start date
K

KA

I have a small spreadsheet with a variable list of values
in a column ( separated by a comma ). Is there a way to
insert a linebreak character for every instance of a
comma to avoid manual keying of "alt + enter" ( there are
about 500 instances that have to be changed ).

I tried using SUBSTITUTE(A2,",",CHAR(10)). This results
in a small square box in place of the comma.

Any help is appreciated.
+++++++++++++++++++++++++++++++++++++++++++++++
Sample raw data :

Col 1 Col 2
------- ---------
row 1 |Key-A Det A1,Det A2
------- ---------
row 2 |Key-B Det B1
------- ---------
row 3 |Key-C Det C1,Det C2,Det C3
+++++++++++++++++++++++++++++++++++++++++++++++
Expected result

Col 1 Col 2
------- ---------
row 1 |Key-A Det A1
| Det A2
------- ---------
row 2 |Key-B Det B1
------- ---------
row 3 |Key-C Det C1
| Det C2
| Det C3
------- ---------

+++++++++++++++++++++++++++++++++++++++++++++++
 
Select the range and then enter:

Selection.Replace ",", Chr(10)

into the Immediate Window.
 
Back
Top