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












13















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.










share|improve this question




















  • 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
    Feb 27 at 2:15








  • 1





    Personally, 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
    Feb 27 at 15:04
















13















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.










share|improve this question




















  • 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
    Feb 27 at 2:15








  • 1





    Personally, 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
    Feb 27 at 15:04














13












13








13


1






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.










share|improve this 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.







microsoft-excel worksheet-function google-spreadsheets






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Feb 27 at 13:12









Peter Mortensen

8,376166185




8,376166185










asked Feb 26 at 22:02









KeyadunKeyadun

715




715








  • 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
    Feb 27 at 2:15








  • 1





    Personally, 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
    Feb 27 at 15:04














  • 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
    Feb 27 at 2:15








  • 1





    Personally, 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
    Feb 27 at 15:04








6




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
Feb 27 at 2:15







(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
Feb 27 at 2:15






1




1





Personally, 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
Feb 27 at 15:04





Personally, 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
Feb 27 at 15:04










3 Answers
3






active

oldest

votes


















32














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



enter image description here






share|improve this answer


























  • Is there also a similar way for adding the char at the beginning instead at the end?

    – undefined
    Feb 27 at 7:38











  • =";" & SUBSTITUTE(A1,CHAR(10),CHAR(10) & ";") should work. I did not try it.

    – aloisdg
    Feb 27 at 10:00






  • 9





    Note 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
    Feb 27 at 10:52











  • 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
    Feb 28 at 4:06











  • @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
    Feb 28 at 8:21



















4














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






share|improve this answer


























  • line by line WITHIN a cell quoting OP

    – aloisdg
    Feb 27 at 10:00











  • Ah yes thank you, I was stuck on the first line. Should I delete ?

    – Stormweaker
    Feb 27 at 10:02






  • 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
    Feb 27 at 12:18





















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 @","



Enter image description here



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.






share|improve this answer


























  • This will only apply the semi colon to the end of the text, not to each line as the OP requested.

    – psubsee2003
    Feb 27 at 12:50











  • Ah ok I thought he meant "In the cell" no that the list was in the cell

    – Steven Martin
    Feb 27 at 12:56











  • @StevenMartin, this one is rare to find ,,, perfect 10 ☺

    – Rajesh S
    Feb 28 at 9:24












Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1409705%2fhow-do-i-append-a-character-to-the-end-of-every-line-in-an-excel-cell%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























3 Answers
3






active

oldest

votes








3 Answers
3






active

oldest

votes









active

oldest

votes






active

oldest

votes









32














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



enter image description here






share|improve this answer


























  • Is there also a similar way for adding the char at the beginning instead at the end?

    – undefined
    Feb 27 at 7:38











  • =";" & SUBSTITUTE(A1,CHAR(10),CHAR(10) & ";") should work. I did not try it.

    – aloisdg
    Feb 27 at 10:00






  • 9





    Note 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
    Feb 27 at 10:52











  • 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
    Feb 28 at 4:06











  • @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
    Feb 28 at 8:21
















32














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



enter image description here






share|improve this answer


























  • Is there also a similar way for adding the char at the beginning instead at the end?

    – undefined
    Feb 27 at 7:38











  • =";" & SUBSTITUTE(A1,CHAR(10),CHAR(10) & ";") should work. I did not try it.

    – aloisdg
    Feb 27 at 10:00






  • 9





    Note 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
    Feb 27 at 10:52











  • 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
    Feb 28 at 4:06











  • @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
    Feb 28 at 8:21














32












32








32







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



enter image description here






share|improve this answer















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



enter image description here







share|improve this answer














share|improve this answer



share|improve this answer








edited Feb 26 at 22:20

























answered Feb 26 at 22:04









Scott CranerScott Craner

12.5k11318




12.5k11318













  • Is there also a similar way for adding the char at the beginning instead at the end?

    – undefined
    Feb 27 at 7:38











  • =";" & SUBSTITUTE(A1,CHAR(10),CHAR(10) & ";") should work. I did not try it.

    – aloisdg
    Feb 27 at 10:00






  • 9





    Note 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
    Feb 27 at 10:52











  • 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
    Feb 28 at 4:06











  • @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
    Feb 28 at 8:21



















  • Is there also a similar way for adding the char at the beginning instead at the end?

    – undefined
    Feb 27 at 7:38











  • =";" & SUBSTITUTE(A1,CHAR(10),CHAR(10) & ";") should work. I did not try it.

    – aloisdg
    Feb 27 at 10:00






  • 9





    Note 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
    Feb 27 at 10:52











  • 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
    Feb 28 at 4:06











  • @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
    Feb 28 at 8:21

















Is there also a similar way for adding the char at the beginning instead at the end?

– undefined
Feb 27 at 7:38





Is there also a similar way for adding the char at the beginning instead at the end?

– undefined
Feb 27 at 7:38













=";" & SUBSTITUTE(A1,CHAR(10),CHAR(10) & ";") should work. I did not try it.

– aloisdg
Feb 27 at 10:00





=";" & SUBSTITUTE(A1,CHAR(10),CHAR(10) & ";") should work. I did not try it.

– aloisdg
Feb 27 at 10:00




9




9





Note 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
Feb 27 at 10:52





Note 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
Feb 27 at 10:52













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
Feb 28 at 4:06





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
Feb 28 at 4:06













@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
Feb 28 at 8:21





@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
Feb 28 at 8:21













4














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






share|improve this answer


























  • line by line WITHIN a cell quoting OP

    – aloisdg
    Feb 27 at 10:00











  • Ah yes thank you, I was stuck on the first line. Should I delete ?

    – Stormweaker
    Feb 27 at 10:02






  • 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
    Feb 27 at 12:18


















4














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






share|improve this answer


























  • line by line WITHIN a cell quoting OP

    – aloisdg
    Feb 27 at 10:00











  • Ah yes thank you, I was stuck on the first line. Should I delete ?

    – Stormweaker
    Feb 27 at 10:02






  • 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
    Feb 27 at 12:18
















4












4








4







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






share|improve this answer















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







share|improve this answer














share|improve this answer



share|improve this answer








edited Feb 27 at 13:13









Peter Mortensen

8,376166185




8,376166185










answered Feb 27 at 10:00









StormweakerStormweaker

965




965













  • line by line WITHIN a cell quoting OP

    – aloisdg
    Feb 27 at 10:00











  • Ah yes thank you, I was stuck on the first line. Should I delete ?

    – Stormweaker
    Feb 27 at 10:02






  • 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
    Feb 27 at 12:18





















  • line by line WITHIN a cell quoting OP

    – aloisdg
    Feb 27 at 10:00











  • Ah yes thank you, I was stuck on the first line. Should I delete ?

    – Stormweaker
    Feb 27 at 10:02






  • 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
    Feb 27 at 12:18



















line by line WITHIN a cell quoting OP

– aloisdg
Feb 27 at 10:00





line by line WITHIN a cell quoting OP

– aloisdg
Feb 27 at 10:00













Ah yes thank you, I was stuck on the first line. Should I delete ?

– Stormweaker
Feb 27 at 10:02





Ah yes thank you, I was stuck on the first line. Should I delete ?

– Stormweaker
Feb 27 at 10:02




2




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
Feb 27 at 12:18







@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
Feb 27 at 12:18













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 @","



Enter image description here



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.






share|improve this answer


























  • This will only apply the semi colon to the end of the text, not to each line as the OP requested.

    – psubsee2003
    Feb 27 at 12:50











  • Ah ok I thought he meant "In the cell" no that the list was in the cell

    – Steven Martin
    Feb 27 at 12:56











  • @StevenMartin, this one is rare to find ,,, perfect 10 ☺

    – Rajesh S
    Feb 28 at 9:24
















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 @","



Enter image description here



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.






share|improve this answer


























  • This will only apply the semi colon to the end of the text, not to each line as the OP requested.

    – psubsee2003
    Feb 27 at 12:50











  • Ah ok I thought he meant "In the cell" no that the list was in the cell

    – Steven Martin
    Feb 27 at 12:56











  • @StevenMartin, this one is rare to find ,,, perfect 10 ☺

    – Rajesh S
    Feb 28 at 9:24














3












3








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 @","



Enter image description here



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.






share|improve this answer















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 @","



Enter image description here



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.







share|improve this answer














share|improve this answer



share|improve this answer








edited Feb 28 at 9:25

























answered Feb 27 at 12:05









Steven MartinSteven Martin

1614




1614













  • This will only apply the semi colon to the end of the text, not to each line as the OP requested.

    – psubsee2003
    Feb 27 at 12:50











  • Ah ok I thought he meant "In the cell" no that the list was in the cell

    – Steven Martin
    Feb 27 at 12:56











  • @StevenMartin, this one is rare to find ,,, perfect 10 ☺

    – Rajesh S
    Feb 28 at 9:24



















  • This will only apply the semi colon to the end of the text, not to each line as the OP requested.

    – psubsee2003
    Feb 27 at 12:50











  • Ah ok I thought he meant "In the cell" no that the list was in the cell

    – Steven Martin
    Feb 27 at 12:56











  • @StevenMartin, this one is rare to find ,,, perfect 10 ☺

    – Rajesh S
    Feb 28 at 9:24

















This will only apply the semi colon to the end of the text, not to each line as the OP requested.

– psubsee2003
Feb 27 at 12:50





This will only apply the semi colon to the end of the text, not to each line as the OP requested.

– psubsee2003
Feb 27 at 12:50













Ah ok I thought he meant "In the cell" no that the list was in the cell

– Steven Martin
Feb 27 at 12:56





Ah ok I thought he meant "In the cell" no that the list was in the cell

– Steven Martin
Feb 27 at 12:56













@StevenMartin, this one is rare to find ,,, perfect 10 ☺

– Rajesh S
Feb 28 at 9:24





@StevenMartin, this one is rare to find ,,, perfect 10 ☺

– Rajesh S
Feb 28 at 9:24


















draft saved

draft discarded




















































Thanks for contributing an answer to Super User!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1409705%2fhow-do-i-append-a-character-to-the-end-of-every-line-in-an-excel-cell%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Probability when a professor distributes a quiz and homework assignment to a class of n students.

Aardman Animations

Are they similar matrix