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 1good bye =Concatenate(A1,B1) The Result A B C 1good 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 1John Smith =Concatenate(A1," ",B1) The Result A B C 1John Smith John Smith Advertisement -
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] X Research source
Entering the Formula A B C 1Monday Friday =Concatenate(A1," - ",B1, ", closed weekends.") The Result A B C 1Monday 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] X Research source
Entering the Formula A B C 101/14/2013 06/17/2013 =Concatenate(Text(A1,"MM/DD/YYYY")," - ",Text(B1,"MM/DD/YYYY")) The Result A B C 101/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 1John Smith =A1&" "&B1 The Result A B C 1John 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".
wikiHow 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!
