Add extra spaces as part of the CONCATENATE formula. Without designated spaces between separate text entries, the text entries will run together. Numbers don't need to have quotation marks. For example: Excel will display =CONCATENATE("Hello ""World") as Hello"World with an extra quote mark because a comma between the text arguments was omitted. Use commas to separate adjoining text items. Joins the same items as the previous example, but by using the ampersand ( &) calculation operator instead of the CONCATENATE function. Joins three things: the string in cell B3, a string consisting of a space with ampersand and another space, and the value in cell C3. Joins three things: the string in cell C2, a string with a comma and a space character, and the value in cell B2. Joins three things: the string in cell B2, a space character, and the value in cell C2. The result is Stream population for brook trout species is 32/mile. To use these examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.Ĭreates a sentence by joining the data in column A with other text. You can have up to 255 items, up to a total of 8,192 characters. The item can be a text value, number, or cell reference.Īdditional text items to join. =CONCATENATE("Stream population for ", A2, " ", A3, " is ", A4, "/mile.") This is because CONCATENATE may not be available in future versions of Excel. Although the CONCATENATE function is still available for backward compatibility, you should consider using CONCAT from now on. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.Important: In Excel 2016, Excel Mobile, and Excel for the web, this function has been replaced with the CONCAT function. This Business Analytics certification course teaches you the basic concepts of data analysis and statistics to help data-driven decision making. We have seen several examples of the CONCATENATE function with different syntax.īoost your analytics career with powerful new Microsoft Excel skills by taking the Business Analytics with Excel course, which includes Power BI training
So, in this article, we discussed how to concatenate text in our Excel worksheet. Looking forward to a career as a Business Analyst? Check out the Business Analytics Certification Training with Excel and get certified today. Just enter the formula in the first cell and then copy it down to the other cells by dragging the fill handle (the small square that appears at the lower right corner of the selected cells). To do that, follow the syntax mentioned below.Ĭoncatenating the whole column at once is very easy. In the worksheet, you may require to join the values in a way that will include commas, spaces, or various punctuation marks. The result of the CONCATENATE function is always a text string, even when all the source values are numbers.Ĭoncatenate Cells with Space, Comma, and Other Characters.If the CONCATENATE function's arguments are invalid, the formula returns a #VALUE! Error.There should be at least one “text” argument in the CONCATENATE function for it to work.You should follow some rules to ensure that the CONCATENATE function always delivers the correct result. =CONCATENATE("Today is ",TEXT(TODAY(), "dd-mmm-yy")) You can also concatenate a text string and a formula calculated value.įor example, you can use the following formula to return the current date: =CONCATENATE(A2, " ", B2, " SIMPLILEARN")Ĭoncatenating a Text String and Formula Calculated Value You can use the function to join a cell value and a string as well. The Excel CONCATENATE function is not only limited to joining cell value. To separate the values with space, use “ “.Ĭoncatenating a Text String and a Cell Value The values will be combined without any delimiters. The simple formula to CONCATENATE the values of two cells A2 and B2 are as follows: Concatenating the Values of Several Cells