How to Concatenate Strings in MS-Excel

Here we will learn how we can concatenate string in MS-Excel.

Let us assume a situation, we have a huge list of numbers (say Roll number of students in a university) saved in MS-Excel sheet and we want to write a SQL query that updates the section of all the students in that huge list. This we could do easily with a update SQL query using “IN” operator but problem is that we need to have our Student's Roll number list in a format like ‘RollNum1', ‘RollNum2', ‘RollNum3 and in Excel sheet, we have a just a list of Roll numbers. Just for sake of understanding the scenario, a sample update query is given below:

UPDATE  TSTUDENTS

SET STUDNT_SECTN ='C2'

WHERE ROLL_NUM IN (a comma separated list in which each element is apostrophe enclosed)

So what's next? Let's go for the concatenation of strings in MS-Excel. We could do concatenation in Excel by following ways:

1. Suppose we have a list in column-A

2. On cell B-1, type “,”

3. Then Fill the entire column –B using AutoFill handle or select the column-B and use Ctrl+D option to auto fill rest of the cells in column-B

4.  Now we could apply our concatenation in column-D. Got to Cell D1.

5. Concatenate the string either using string built-in concatenate function                
   
   =CONCATENATE("'", A1, "'",B1)  
   
   Or we could use “&” symbol for concatenation as   ="'"&A1&"'"&B1

6. Then Fill the entire column –D using AutoFill handle or select the column-B and use Ctrl+D option to auto fill rest of the cells in column-D.

7. That's it, we are done.

A                     B                C                 D

B12 9	,		'B12 9',
B13 0	,		'B13 0',
B13 8	,		'B13 8',
B13 9	,		'B13 9',
B14  6	,		'B14  6',
B14 4	,		'B14 4',
B14 5	,		'B14 5',
B45 0	,		'B45 0',
B45 9	,		'B45 9',
B46 1	,		'B46 1',
B46 2	,		'B46 2',
B46 3	,		'B46 3',
B47 5	,		'B47 5',
B47 6	,		'B47 6',
B48 7	,		'B48 7',
B49 5	,		'B49 5',
B49 6	,		'B49 6',
B5 4	,		'B5 4',
B5 5	,		'B5 5',
B5 6	,		'B5 6',
B5 7	,		'B5 7',
B50 4	,		'B50 4',
B6 4	,		'B6 4',
B6 5	,		'B6 5',
B6 6	,		'B6 6',