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',