How to concatenate range with adding/including quotation marks 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 permanently free AI features! 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: