Replace all semicolons with line breaks

[mikec82]

I have a spreadsheet that looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Student[/TD]
[TD]Course[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]English;Math;Science;History[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Math;History;Art[/TD]
[/TR]
</tbody>[/TABLE]

I would like to replace all semicolons with line breaks if possible, so that it would appear like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Student[/TD]
[TD]Course[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]English
Math
Science
History[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Math
History
Art[/TD]
[/TR]
</tbody>[/TABLE]


Is this possible?

 

[jtakw]

Hi,

If you're looking for a formula solution:

Excel 2010
	A	B	C
1	Student	Course
2	John Smith	English;Math;Science;History	English Math Science History
3	Jane Smith	Math;History;Art	Math History Art
Sheet18

Cell Formulas
Range	Formula
C2	=SUBSTITUTE(B2,";",CHAR(10))

C2 formula copied down, don't forget to "Wrap Text"

 

[Rick Rothstein]

I have a spreadsheet that looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Student[/TD]
[TD]Course[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]English;Math;Science;History[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Math;History;Art[/TD]
[/TR]
</tbody>[/TABLE]

I would like to replace all semicolons with line breaks if possible, so that it would appear like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Student[/TD]
[TD]Course[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]English
Math
Science
History[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Math
History
Art[/TD]
[/TR]
</tbody>[/TABLE]

Is this possible?

Select the entire column with your semi-colons, press CTRL+H to bring up Excel's Replace dialog box, put a single semi-colon in the "Find what" field, select the "Replace with" field and key in CTRL+J (it will look like nothing happened but it did), then click the "Options>>" button and make sure none of the checkboxes are checked, then click the "Replace All" button.

 

[mikec82]

Thank you both. This is perfect!

 

[jtakw]

You're welcome.