View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
KA[_2_] KA[_2_] is offline
external usenet poster
 
Posts: 2
Default Mass find replace - inserting a linebreak

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
------- ---------

+++++++++++++++++++++++++++++++++++++++++++++++