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

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.

Keyadun

Posted 2019-02-26T22:02:56.433

Reputation: 141

6(Just FYI, if you can, you probably don't want to return this all in a single cell. It'll be very tricky/annoying to do anything with the data after you have it. I can't think of a reason to return the items in a single cell, instead of one cell per item.) – BruceWayne – 2019-02-27T02:15:26.843

1Personally, when I need to do this kind of thing, I always have a standard text editor open side-by-side with Excel (you just want any editor that can handle special characters such as newlines, and also do block selection - Notepad++ is a common one, but many others can do this.) and I'll just copy/paste the text into the text editor, do the operations there (where it's generally much easier) and then copy/paste back into Excel. – Darrel Hoffman – 2019-02-27T15:04:53.800

Answers

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

Scott Craner

Posted 2019-02-26T22:02:56.433

Reputation: 16 128

Is there also a similar way for adding the char at the beginning instead at the end? – undefined – 2019-02-27T07:38:26.457

=";" & SUBSTITUTE(A1,CHAR(10),CHAR(10) & ";") should work. I did not try it. – aloisdg moving to codidact.com – 2019-02-27T10:00:16.417

9Note that if the content uses CR LF as a line separator (which is standard on Windows), you're inserting the semicolon between the carriage return and the line feed. This can cause all sorts of weirdness, and I certainly don't recommend it. – Erik A – 2019-02-27T10:52:23.860

Wouldn't it be better to find the line separator and insert relative to that instead? As in, find it, insert at pos -1? – Mast – 2019-02-28T04:06:41.720

@Mast That would let you insert one semicolon, unless you used VBA to have a loop that goes through the text. This lets you insert as many as needed, all at the same time – Chronocidal – 2019-02-28T08:21:36.040

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

The formula would be:

=A1&";"

Application example

Stormweaker

Posted 2019-02-26T22:02:56.433

Reputation: 116

line by line WITHIN a cell quoting OP – aloisdg moving to codidact.com – 2019-02-27T10:00:42.407

Ah yes thank you, I was stuck on the first line. Should I delete ? – Stormweaker – 2019-02-27T10:02:41.193

2@Stormweaker: Sounds like it could be useful for future readers who find the question via Google. As long as you don't get downvotes, I'd just leave it. Oh, and welcome to SO! – Heinzi – 2019-02-27T12:18:58.310

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.

Steven Martin

Posted 2019-02-26T22:02:56.433

Reputation: 161

This will only apply the semi colon to the end of the text, not to each line as the OP requested. – psubsee2003 – 2019-02-27T12:50:34.633

Ah ok I thought he meant "In the cell" no that the list was in the cell – Steven Martin – 2019-02-27T12:56:06.317

@StevenMartin, this one is rare to find ,,, perfect 10 ☺ – Rajesh S – 2019-02-28T09:24:36.927