Combining Excel Cells

Combining cells in excel implies joining the content of two or more cells into a single cell. This allows viewing united values rather than split values. To combine cells, one can use either the ampersand operator (&) or the CONCATENATE function of Excel.

For example, a bank manager wants to view the branch location and the number of employees working there in a single cell. The formula “=A2&”,”&B2” (exclude the beginning and ending double quotation marks) combines cells A2 and B2 containing “location X” and “570” respectively.

Therefore, the output is “location X,570.” The comma of this output serves as a separator (delimiter) between the two cell values. This is shown in the following image.

The purpose of combining excel cells is to arrange the data in the desired format. Moreover, it improves the presentation of data, thereby making the dataset suitable for further processing.

It must be noted that combining excel cells is different from merging cellsMerge CellsMerging a cell in excel refers to combining two or more adjacent cells either vertically, horizontally or both ways. Merging excel cells is specifically required when a heading or title has to be centered over an area of a worksheet.read more. The latter retains the data of the upper-leftmost cell only, while there is no data loss in case of the former. This article discusses the different techniques of combining cells containing diverse data values in Excel.

How to Combine Cells in Excel?

Let us consider a few examples to understand the process of combining cells in Excel.

Example #1–Combine a Text String and Number with the Ampersand Operator

The succeeding image shows the product name and code in columns A and B respectively. We want to combine the text string (column A) and the numeric value (column B) of each row in a single cell (in column D). Use the ampersand operator.

The steps to combine two excel values using the ampersand operator are listed as follows:

Step 1: Enter the formula “=A4&B4” in cell C4, as shown in the succeeding image.

Note: Notice that in the formula bar of the following image, there is an apostrophe at the beginning of the formula. This apostrophe indicates that this formula is treated as text by Excel. Once the apostrophe is removed, the formula is processed by Excel. Hence, ignore the leading apostrophe of the formula.

Step 2: Press the “Enter” key. The output for the first row is “A8,” as shown in the succeeding image. To obtain the outputs for all the rows, drag the formula to the remaining cells of the column.

For simplicity, we have retained the formulas in column C and given the outputs in column D. Hence, in the first output, the letter “A” and the number 8 have been combined to form “A8.”

Example #2–Combine two Text Strings with the Ampersand and CONCATENATE

The succeeding image shows the first and last names in columns A and B respectively. We want to obtain the full names separated by a space within a single column. Use the ampersand operator and the CONCATENATE function to combine the excel cells of each row.

The steps for combining two cell values using the given techniques are listed as follows:

Step 1: Enter the following formulas in cells C16 and E16 respectively.

  • “=A16&” “&B16”“=CONCATENATE(A16,” “,B16)”

Step 2: Press the “Enter” key after entering each of the preceding formulas. Next, drag the formula to the remaining cells of the column. The outputs are shown in the succeeding image.

For simplicity, we have retained the formulas using the ampersand and the CONCATENATE in columns C and E respectively. The outputs have been given in columns D and F. Hence, in the first output (D16 and F16), the names “Tanuj” and “Rajput” have been combined to form the full name “Tanuj Rajput.”

Note: In both the formulas, we have enclosed a single space between cells A16 and B16. So, the output also has this space between the first and the last names. However, just to make this space visible, additional spaces have been inserted in the outputs. Ignore these extra spaces and remember that the output will have as many spaces as that of the formula.

Hence, had the formula contained three spaces within the double quotation marks [like “=CONCATENATE(A16,”   “,B16)”], the output also would have had three spaces between the first and the last names [like “Tanuj   Rajput”].

Example #3–Combine two Text Strings and add a Line Break with the Ampersand and CHAR

The succeeding image shows the first and the last names in columns F and G respectively. We want to combine the two names of each row in a single cell (column I). Further, add a line break between the combined names. Use the ampersand operator and the CHAR function.

The steps to combine the names and insert a line break in excel are listed as follows:

Step 1: Enter the following formula in cell H4.

“=F4&CHAR(10)&G4”

The ampersand combines the first name and the last name. The “CHAR(10)” inserts a line break after the first name and before the last name.

Note 1: “CHAR(10)” returns a line feed, which implies that the data value following it goes to the next line.

Note 2: Ignore the leading apostrophe of the formula, which is displayed in the formula bar. For more details related to this apostrophe, refer to the note (under step 1) of the first example of this article.

Step 2: Press the “Enter” key. Next, select the output cell and click “wrap text”Wrap TextWrap text in Excel belongs to the “Formatting” class of excel function that does not make any changes to the value of the cell but just change the way a sentence is displayed in the cell. This means that a sentence that is formatted as warp text is always the same as that sentence that is not formatted as a wrap text.read more from the “alignment” group of the Home tab. The final output is shown in cell I4 of the following image.

Hence, in the first output, the names “Tanuj” and “Rajput” have been combined (with a line break in between) to form “Tanuj Rajput.”

To obtain the outputs for the entire column, drag the formula of the preceding step (step 1) to the remaining cells. Next, select the output column and click “wrap text.” For simplicity, we have retained the formulas in column H and given the outputs in column I.

Note: Enabling the “wrap text” option makes the line break of the output visible. If “wrap text” is not applied, the first and the last names are joined together in one line (within a single cell) even though CHAR(10) has been used. Hence, to see the impact of CHAR(10), it is essential to apply “wrap text.”

Example #4–Prefix and Suffix Text Strings to a Number with the Ampersand

The succeeding image shows the number of days in column A. To this number, we want to prefix the string “due in” and suffix the string “days.” Use the ampersand operator.

The steps for the given task are listed as follows:

Step 1: Enter the following formula in cell B34.

=”Due in “&A34&” days”

Note: Notice that the formula contains spaces following the prefix and preceding the suffix. This ensures that spaces are inserted at their respective places in the output.

Step 2: Press the “Enter” key. The output is shown in cell C34 of the succeeding image. Drag the formula entered in the preceding step to the remaining range.

For simplicity, we have retained the formulas in column B and given the outputs in column C.

Hence, the given prefix and suffix have been added to all the numeric values of column A. The outputs of column C represent the terms of a payment schedule.

Example #5–Prefix Text Strings to the Current Date with the Ampersand

We want to prefix the text string “today is” to today’s date. The output should contain the date in the following formats:

  • As a serial number of ExcelAs a text string in the format mm-dd-yyyy

For both the preceding pointers, use the ampersand operator and the TODAY function. Use the TEXT function as well for the second pointer.

Note that the date of creation of this article was December 1, 2018. So, all the following calculations take into account the given date.

Step 1: Enter the following formulas in cells H36 and H37 respectively.

  • “=”Today is “&TODAY()”“=”Today is “&TEXT(TODAY(),”mm-dd-yyyy”)”

These formulas are shown in the succeeding image.

Note 1: The TODAY function returns the current date. It updates automatically each time the workbook is opened. It does not take any arguments.

Note 2: The TEXTUse Text Formula In ExcelTEXT function in excel is a string function used to change a given input to the text provided in a specified number format. It is used when we large data sets from multiple users and the formats are different.read more function converts a numeric value to text in the format specified by the user. The syntax is “TEXT(value,format_text).” The “value” is the number to be converted to text. The “format_text” is the format to be applied to the number.

Note 3: Notice that spaces are inserted at the relevant places in both formulas. This inserts spaces in the output.

Step 2: Press the “Enter” key after entering the two preceding formulas. The outputs are displayed in the following image.

Hence, in the first output (cell H36), the TODAY function has returned a serial number (43435). This number corresponds to the date December 1, 2018.

In the second output (cell H37), the date returned by the TODAY function is converted to text (in mm-dd-yyyy format) by the TEXT function. Had we not used the TEXT function, the date would have been displayed as a number.

Therefore, the prefix “today is” has been added to the two date formats with the help of the ampersand operator.

Example #6–Combine Cell Values in VBA with the Ampersand

We want to combine the values of cells A4 and B4 in a single excel cell, C4. Write the VBA code for the same.

The code for combining the cell values (without any data loss) is given as follows:

Sub Mergecells()

Range(“C4”).Value = Range(“A4”).Value & Range(“B4”).Value

End Sub

Frequently Asked Questions

This has been a guide to combining cells in Excel. Here we discuss how to combine cells in Excel using the ampersand operator (&) and CONCATENATE along with examples and a downloadable template. You may also look at these useful articles of Excel –

The delimiter required in the combined output should be enclosed within double quotation marks in the formula entered initially. To combine values of cells A1 and B1, the Excel formulas containing the given delimiters are stated as follows:a. Comma: “=CONCATENATE(A1,”,”,B1)” or “=A1&”,”&B1”b. Space: “=CONCATENATE(A1,” “,B1)” or “=A1&” “&B1”c. Semicolon: “=CONCATENATE(A1,”;”,B1)” or “=A1&”;”&B1”d. Hyphen: “=CONCATENATE(A1,”-“,B1)” or “=A1&”-“&B1”Note 1: A delimiter is a character or symbol that separates two values.Note 2: While entering the preceding formulas in Excel, ensure that the beginning and ending double quotation marks are excluded.

• Three cell values with the space as the delimiter• Three cell values with the space as the delimiter and a date (in dddd/mm/yyyy format) in the center of the combined outputTo combine the values of cells A1, B1, and C1 with spaces in between, use either of the following formulas in Excel:a. “=CONCATENATE(A1,” “,B1,” “,C1)”b. “=A1&” “&B1&” “&C1”To combine the values of cells A1, B1, and C1 where B1 contains a date (12/01/2021) and the space is the delimiter, use either of the following formulas in Excel:a. “=CONCATENATE(A1,” “,TEXT(B1,”dddd/mm/yyyy”),” “,C1)”b. “=A1&” “&TEXT(B1,”dddd/mm/yyyy”)&” “&C1”Note: Exclude the beginning and the ending double quotation marks while entering the preceding formulas in Excel.