How do I append a character to the end of every line in an Excel cell?

Question

I have a column of Excel cells that's similar to a grocery shopping list and looks as follows:

3 Days Ingredients
2 Apples
1 Jug of milk
3 Bags of mixed vegetables
5 Potatoes

Is there a way to append a character such as a comma or semicolon at the end of every line within the cell so that it appears as follows?

3 Days Ingredients;
2 Apples;
1 Jug of milk;
3 Bags of mixed vegetables;
5 Potatoes;

I know that there are ways to append to the end of a cell, but I was wondering if there's a way to do it line by line within a cell.

Answer 1

Use Substitute:

=SUBSTITUTE(A1,CHAR(10),";" & CHAR(10)) &";"

CHAR(10) is the Line Return.

Make sure the wrap text is on for the target cell

Answer 2

Assuming that every item of the list is in a different row, you can use the concatenation character, '&'.

The formula would be:

=A1&";"

Answer 3

If you want to do it directly in the cell without using a second column and formulas, you can use custom formatting to achieve it.

In custom cell formats, enter @","

Then you can apply that format across the full range of cells.

It won't actually exist in the cell contents, but it will be displayed on the screen, and if you copy to another application it will be there.