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:
-
Suppose we have a list in column-A
-
On cell B-1, type “,”
-
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
-
Now we
could apply our concatenation in column-D. Got to Cell D1.
-
Concatenate the string either using string
built-in concatenate function
=CONCATENATE("'", A1, "'",B1)
Or we could use “&” symbol for concatenation as
="'"&A1&"'"&B1
-
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.
-
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', |