Batch combine multiple cells/rows/columns without losing data and date/number format in Excel

This method will introduce the most common way – the CONCATENATE function to concatenate a range with adding quotation marks in Excel.

1. Select the blank cell you will put the concatenation result into, and enter the formula =CONCATENATE(CHAR(34),A2,CHAR(34)," ", CHAR(34),B2,CHAR(34)," ",CHAR(34),C2,CHAR(34)," ",CHAR(34),D2,CHAR(34)) into it, and press the Enter key.

2. Keep selecting the concatenation result cell, and drag its AutoFill handle to the range as you need.

And then you will see each row has been concatenated and each cell content has been enclosed by the quotation marks. See screenshot:

Note: In above formula, A2, B2, C2, and D2 are cells you will concatenate, Char(34) means quotation mark. If you need to concatenate more cells (such as Cell E2) with adding quotation marks, you can add ," ", CHAR(34),E2,CHAR(34) before the last right parenthesis, such as =CONCATENATE(CHAR(34),A2,CHAR(34)," ", CHAR(34),B2,CHAR(34)," ",CHAR(34),C2,CHAR(34)," ",CHAR(34),D2,CHAR(34)," ", CHAR(34),E2,CHAR(34)).

You might be confused by adding CHAR(34) repeatedly in the above formula. This method will introduce Kutools for Excel’s Add Text utility to add quotation marks to enclose each cell content before concatenating, and then concatenate these cells by formula.

1. Select the cells you will concatenate, and click Kutools > Text > Add Text. See screenshot:

2. In the opening Add Text dialog box, please do as follows:

(1) Type quotation mark “ into the Text box;

(2) Check the Before first character option, and click the Apply button;

(3) Check the After last character option, and click the Ok button.

Note: The Skip non-text cells option will prevent from adding quotation marks into non-text cells, such as date cells, number cells, formula cells, etc.

 

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

And then you will see each cell content has been enclosed by quotation marks. See screenshot:

3. Select a blank cell you will put the concatenation result into, type the formula =CONCATENATE(A2:H2&" "), select the A2:H2&" " in the formula and press the F9 key to replace cell references with cell contents, next remove the curly braces { and } from the formula, and at last press the Enter key. See screenshot:

Demo: Concatenate with adding quotation marks by Add Text utility

This method will introduce Kutools for Excel’s Combine utility to quickly concatenate a range with adding quotation marks in Excel.

1. Select the range you will concatenate, and click Kutools > Combine.

2. In the opening Combine Columns or Rows dialog box, please do as follows:

(1) Specify a combination option in the To combine selected cells according to following options section. In our case we check the Combine columns option;

(2) In the Specify a separator section, check the Other separator option, and type “ “ into following box;

(3) Specify the cell you will put the combination result into from the Place the results to drop down list;

(4) In the Options section, check one of options based on your need. In our case, we check the Delete contents of combined Cells option.

(5) Click the Ok button.

Note: The Use formatted values option will remain any date format and number format, such as currency, percentage, decimal places, etc. after combination.

And then you will see each row has been concatenated and each cell content has been enclosed by quotation marks. However, the left curly brace for the first cell content and right curly brace for the last cell content have not been added. See screenshot:

3. Keep selecting the combination results, and click Kutools > Text > Add Text to open the Add Text dialog box, and then do as follows:

(1) Type quotation mark “ into the Text box;

(2) Check the Before first character option, and click the Apply button;

(3) Check the After last character option, and click the Ok button.

 

Free Download Kutools for Excel Now

And then you will see the lost quotation marks are added into the combination results as below screenshot shown:

Demo: Concatenate with quotation marks by Combine utility

Concatenate keeping number/date formatting in Excel

Concatenate range/cells with comma, space, line break in Excel

Concatenate first letters of names/cells in Excel

Concatenate cells from another sheet/different sheets in Excel