Concatenation in ExcelWhat is Concatenation?Concatenation is the process of joining two strings together. It combines the text, strings, and data in a series pattern without any gap. There are two ways to combine the data such as,
Merging the cells combines one or more cells and represents them in a single cell. Concatenation in ExcelIn Excel, the data entered sometimes needs to be added and separated. The data is only sometimes present in a structured way; hence, adding or separating the text from one cell to another, rows, and columns is needed. Combining data such as names, addresses, and times is done with the help of the respective Concatenation formula in Excel. Why Concatenate Data?Concatenation helps to represent the data in an organized way, which helps the user to go through the data quickly. If the data contains names of various persons, which is present in column A, and their surnames are present in Column B, the concatenation function combines the name with surnames, making the data organized. Similarly, data like email and address can be combined. The concatenation function is used to combine text and numbers. Data like purchase, order Id, and delivery status of every person can be combined, which helps for reference. Formulas used to Concatenate the dataExcel provides various formulas to concatenate the data. The formulas are explained as follows: 1. Concatenation FunctionThe Concatenation function combines different pieces of strings or text in a single cell. This function supports Excel 2007365. The syntax of the Concatenation function is as follows: CONCATENATION (text 1,[text 2],....) The argument text refers to the text string, the value defined by a formula, or a cell reference. To use the Concatenation function, the steps to be followed are:
Here the string "Hello Google" is combined using the Concatenation function and displayed in cell C1. Points To RememberThe essential points to be noted to get the correct result using the Concatenation function are as follows:
"&" OperatorThe Ampersand (&) operator is used to concatenate cells in Excel. It is one of the easiest and fastest methods. The steps to use Ampersand (&) symbol are as follows: Enter the data in cells A1 and B1 In cell C1 enter the formula as =A1&" "&B1. Press Enter. The result will be displayed as follows, Concatenating the data without a separatorTo combine the data without any delimiter, the formula used is: =CONCATENATE (A1, B1) either the formula used is, =A1&B1 The steps to use these formulas are as follows, Enter the data in cells A1 and B1 In cell C1 enter the formula as, =CONCATENATE (A1, B1).Press Enter, and the result will be displayed as follows, The result is displayed without any delimiter. In D1, enter another formula as=A1&B1. Concatenating the data with a separatorTo combine the data with a delimiter such as commas, spaces, punctuation marks, and other characters such as hyphens or slash, the formula used is: To concatenate the data using space, the formula used is: =CONCATENATE (A1," ", B1) Another formula used is: =A1& " "&B1 To use the formula, the steps to be followed are: Enter the data in the worksheet, namely A1 and B1 In cell C1 enter the formula as, =CONCATENATE (A1, " ", B1).Press Enter, and the result will be displayed as follows, In D1 enter another formula as=A1&" "&B1. To concatenate the data, using commas, the formula used is: =CONCATENATE(A1,",",B1) Or =A1 &","&B1 The steps to be followed are: Enter the data in the worksheet, namely A1 and B1. In cell C1, enter the formula as ,= CONCATENATE(A1,",",B1).Press Enter, and the result will be displayed as follows, Enter another formula in the cell D1 as =A1 &",&B1 To concatenate two cells using a hyphen, the formula used is: =CONCATENATE(A1,"",B1) Or =A1&""&B1 The steps to be followed are: Enter the data in the worksheet, namely A1 and B1. In cell C1, enter the formula as ,= CONCATENATE(A1,"",B1).Press Enter, and the result will be displayed as follows, Enter another formula in the cell D1 as =A1 &""&B1 NotesThe user can use the TEXTJOIN function to concatenate the strings from multiple cells with the required delimiter in Excel version starting from 2019 to Microsoft Excel 365, Combining the cell value and text stringExcel provides a Concatenation formula to combine the cell value and a specified text string to make the result more meaningful and organized. Sometimes the data must concatenate the specific text with the combined cell values. The steps to be followed to concatenate the cell value and text string is: Enter the data in worksheets A1 and B1 In cell C1 , enter the formula as =CONCATENATE(A1," ",B1,"completed") To add space for the cell value, the formula used is: =A1&" "&B1& "completed" Enter the formula in the cell C1 as =A1&" "&B1& "completed". Press Enter. The result will be displayed as, The user can add the text string before the concatenation cell value and string, the formula used to add the text before the text string is as follows: =CONCATENATE("The",A1," ",B1) Another formula used is, =CONCATENATE("The " & A1 &" "&B1) Enter the formula in the cell C1 as =CONCATENATE("The" &A1&" "&B1). Press Enter, and the result will be displayed as follows: Combining the text string with formulasThe user can add text strings with formulas to display the data more attractively based on the data requirement. For example, to display today's date using the CONCATENATION function, the steps to be followed are: Enter the formula in the cell A1 as= CONCATENATE("Today is",TEXT(TODAY(),"mmmm d, yyyy")) The formula concatenates the text with the TODAY() function to display today's date. To change the format of the date, the formula is modified as, =CONCATENATE("Today is"& TEXT(TODAY(),"ddmmmyy") Enter the formula in cell A1, and press the Enter Key. The result will be displayed as follows, Concatenating the ColumnsTo concatenate the selective data present in two different columns, the step to be followed are: Enter the data in the respective cell range, namely A1:B5 In cell C1 , enter the formula as =CONCATENATE(A1," ",B1).Press Enter, and the result will be displayed as, To display the data for the remaining cells, drag the fill handle toward cell C6. Another formula to concatenate the columns is, =CONCATENATE(A1&" "&B1) Enter the formula in cell D1 as =CONCATENATE(A1&" "&B1). Press Enter, and the result will be displayed as follows, The fill handle method is used to get the result for the remaining cells. Combining the range of cells in ExcelTo combine the range of cells or multiple cells in Excel, the steps to be followed are: Enter the data in the cell range A1:D4 In E5 enter the formula as , =CONCATENATE(A1,A2,A3,A4).Press Enter, and the result will be displayed as follows: Another formula to concatenate the data is as follows: =CONCATENATE(A1&B1&C1&D1) Enter the formula in cell B1; the result will be displayed as follows: Combining text and NumbersThe CONCATENATE function can combine the text string with a number based on the data requirement. The combination of number and text can be displayed in both ways; either the format of the number is displayed as it is, or the format of the number is changed. The TEXT function is used to combine text and numbers. The steps to be followed are: To combine TEXT and NUMBERS, where the number is displayed with two decimal places with a $ sign, Enter the data in the cells A1 and B1 In cell C1 enter the formula as , =CONCATENATE(A1&" "&TEXT(B1,"$#,#0.00"). Press Enter, and the result will be displayed as follows, To display numbers without insignificant zero and with the dollar($) sign, the steps to be followed are as follows: Enter the data in the cells A1 and B1 In cell C1 enter the formula as , =CONCATENATE(A2&" "&TEXT(B1,"0.#"). Press Enter, and the result will be displayed as follows, To display fractional numbers, the steps to be followed are: Enter the data in the cells A1 and B1 In cell C1 enter the formula as , =CONCATENATE(A1&" "&TEXT(B1,"#?/???").Press Enter, and the result will be displayed as follows, To combine text and percentages, where the percentage is displayed with two decimal values, the steps to be followed are: Enter the data in the cells A1 and B1 In cell C1 enter the formula as , =CONCATENATE(A1&" "&TEXT(B1,"156.00%").Press Enter, and the result will be displayed as follows, To display the given number as a rounded whole percent value, the steps to be followed are: Enter the data in the cells A1 and B1 In cell C1 enter the formula as , =CONCATENATE(A1&" "&TEXT(B1,"156%").Press Enter, and the result will be displayed as follows, Concatenating Multiple Cells using the CTRL buttonCTRL buttonTo concatenate a small range of cells, the cell values are typed in the formula, but for an extensive cell range, typing each cell value takes more time and sometimes leads to wrong data entry. To make the process easy, the CTRL button is used. The steps to be followed are: Enter the data in the cell range A1:A11 In cell B1, enter the formula as =CONCATENATE(, now select cell A1, press and hold CTRL key, and click on each cell which needs to Concatenate. After selecting all the values, release the CTRL button, enter the closing parenthesis in the formula, and press Enter. The result will be displayed as follows: Note:While applying this method to the selected range of cells, each cell should be clicked individually. If the mouse is used, it creates an array in the formula, which is not applicable in the CONCATENATION formula. Transpose FunctionThe transpose function displays the array of values in the formula. While Concatenating a large data set, the TRANSPOSE function displays the selected data quickly. The steps to be followed to use the TRANSPOSE function are as follows: Enter the data in the cell range, namely A1:A11 Enter the TRANSPOSE formula in the cell, where the result will be displayed. In cell B1, enter the formula =TRANSPOSE(A1:A11). Now in the formula bar, press the F9 key, and the data will be displayed as Delete the curly braces in cell B1, and enter the formula as=CONCATENATE( before the first value, and close the parenthesis after the last value). Press Enter. The Concatenation function returns the combined range of values present in cell A1:A11. Note:The displayed result is static; if source data is changed with a new value, it won't be updated in the result. The process needs to be repeated to update the edited data in the result. CONCAT functionThe CONTACT function is used to combine the selected range of values. This function is used in Excel 361 and Excel 2021 versions. The steps to use the CONCAT function are as follows: Enter the data in the cell range A1:A11 In cell B1, enter the formula as =CONCAT(A1:A11). Press Enter. The CONCAT function combines the selected range of values. "CONCATENATE" Vs. "&" functionBoth Concatenate and Ampersand (&) functions combine the values. The difference between the functions is, The Concatenate function is limited to 255 strings, whereas the ampersand function doesn't contain any limitations. Functions Opposite of Concatenate FunctionExcel provides some of the functions which are opposite to Concatenate function. Some of the functions are as follows: TEXT SPLIT function Text to Columns feature Flash Fill Method available in 2013 and higher Formula to split cells based on a customized format(MID, RIGHT, LEFT, etc.)
Next TopicData Entry in Excel
