Replace all semicolons with line breaks

[mikec82]

I have a spreadsheet that looks like this:

Student	Course
John Smith	English;Math;Science;History
Jane Smith	Math;History;Art

<tbody>
</tbody>

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

Student	Course
John Smith	English Math Science History
Jane Smith	Math History Art

<tbody>
</tbody>

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:

Student	Course
John Smith	English;Math;Science;History
Jane Smith	Math;History;Art

<tbody>
</tbody>

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

Student	Course
John Smith	English Math Science History
Jane Smith	Math History Art

<tbody>
</tbody>

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.