How.com.vn is a “wiki,” similar to Wikipedia, which means that many of our articles are co-written by multiple authors. To create this article, 13 people, some anonymous, worked to edit and improve it over time.
The How.com.vn Tech Team also followed the article's instructions and verified that they work.
This article has been viewed 402,304 times.
Are you pulling your hair out trying to manage a large spreadsheet full of disjointed names or dates? Do you want to create form sentences that can be automatically filled in with the data from your spreadsheet? The Concatenate function is here to save the day! Follow this guide to quickly join the values of multiple cells in your Excel spreadsheet.
Steps
Use Concatenate to join two cells. The basic function of concatenate is to join two or more text strings together. You can join up to 255 different strings together using one concatenate command. Take the following example:Entering the Formula A B C 1 good bye =Concatenate(A1,B1) The Result A B C 1 good bye goodbye 
Insert spaces between your joined text. If you want to join text but leave a space in between, you can add a space to the formula with quotation marks around a single space. This is especially useful for data such as first and last names. For example:Entering the Formula A B C 1 John Smith =Concatenate(A1," ",B1) The Result A B C 1 John Smith John Smith 
Insert punctuation and other text between concatenated strings. As seen above, you can add spaces by placing quotation marks around a blank space in the formula. You can expand this and use quotation marks to insert any text into your concatenation. Take note of the spacing left in the quotation marks to result in a readable sentence.[1]Entering the Formula A B C 1 Monday Friday =Concatenate(A1," - ",B1, ", closed weekends.") The Result A B C 1 Monday Friday Monday – Friday, closed weekends. 
Concatenate a range of dates. If you have a date range that you want to join, you will need to use the TEXT function to prevent Excel from treating the dates as mathematical formulas:[2]Entering the Formula A B C 1 01/14/2013 06/17/2013 =Concatenate(Text(A1,"MM/DD/YYYY")," - ",Text(B1,"MM/DD/YYYY")) The Result A B C 1 01/14/2013 06/17/2013 01/14/2013 - 06/17/2013 
Use the “&” symbol as a replacement for Concatenate. The “&” performs the same function as concatenate. It can be useful for shorter formulas, but can quickly become cluttered for longer ones. Note the space in quotation marks. You must have an “&” between each value that you want to concatenate.Entering the Formula A B C 1 John Smith =A1&" "&B1 The Result A B C 1 John Smith John Smith Advertisement
Community Q&A
- QuestionI have a column full of email addresses. I want to concatenate them with semicolons between them so that I can copy them all into my email and send them all bcc. Is there a way to do this without having to treat each one individually?
Community AnswerAssume all your addresses are in column A. In B2, add the first two (=(A1&";"&A2). Then in B3, add A3 to B2 and then fill that formula down to the bottom of the list and you will have a cell with all that data. - QuestionHow do I create a formula in excel 2010 to place a "yes" if the condition is met and "no" if the condition is not met?Community AnswerWhat you are looking for is the IF formula. Example: =IF(A1=5,Yes,No). This formula checks if the value in cell A1 is 5. If it is, the formula will output "Yes". If A1 is any value other than 5 the formula will output "No".
- QuestionCan you use concatenate with an IF statement in excel?Community AnswerYes. Below is an example of a formula I put together using nested IF statements for a spreadsheet where the output depended on 2 input cells. =IF(ISBLANK(B9),IF(ISBLANK(B10),"",B10),IF(ISBLANK(B10),B9,CONCATENATE(B9," & ",B10))). Result: if B9 and B10 are blank the output cell will show nothing; if B9 = "word" and B10 is blank; output = "word"; if B9 is blank and B10 = "text"; output = "text"; if B9 = "word" and B10 = "text"; output = "word & text".
How.com.vn Video: How to Concatenate Text in Microsoft Excel
Tips
- The CONCATENATE within INDIRECT is used extensively in How to Create a Browse Tab in ExcelThanks
References
About this article
Concatenate in Microsoft Excel can help you join two or more text strings together. To use concatenate, first type =Concatenate in the cell where you want the combined strings of text to appear. Then, add the cells you want to concatenate in parenthesis separated by a comma, like =Concatenate(A1,B1). If you want there to be a space between the joined text, add one between the two cells surrounded by quotation marks, like =Concatenate(A1," ",B1). If you're trying to concatenate a range of dates, you'll need to use the TEXT function so the dates aren't treated as mathematical formulas, like =Concatenate(Text(A1,"MM/DD/YYYY")," - ",Text(B1,"MM/DD/YYYY")). To learn how to insert punctuation and other text between concatenated strings, check out the full article below!
Is this article up to date?
⚠️ Disclaimer:
Content from Wiki How English language website. Text is available under the Creative Commons Attribution-Share Alike License; additional terms may apply.
Wiki How does not encourage the violation of any laws, and cannot be responsible for any violations of such laws, should you link to this domain, or use, reproduce, or republish the information contained herein.
- - A few of these subjects are frequently censored by educational, governmental, corporate, parental and other filtering schemes.
- - Some articles may contain names, images, artworks or descriptions of events that some cultures restrict access to
- - Please note: Wiki How does not give you opinion about the law, or advice about medical. If you need specific advice (for example, medical, legal, financial or risk management), please seek a professional who is licensed or knowledgeable in that area.
- - Readers should not judge the importance of topics based on their coverage on Wiki How, nor think a topic is important just because it is the subject of a Wiki article.
