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

Ebook Download
View all
Learn
View all