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

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

Mass find replace - inserting a linebreak
 

Select the range and then enter:

Selection.Replace ",", Chr(10)

into the Immediate Window.

--

Vasant



"KA" wrote in message
...
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
------- ---------

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

Mass find replace - inserting a linebreak
 

Thanks much. This works!

-----Original Message-----
Select the range and then enter:

Selection.Replace ",", Chr(10)

into the Immediate Window.

--

Vasant



"KA" wrote in
message
...
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
------- ---------

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


.