Concatenate strings in MS-Excel

Here we will learn how we can easily concatenate string in MS-Excel which could be useful in many situations. I am going to describe a situation which I encountered once.

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

 Thanks,

Hemant