Advance Excel Learning - Concatenate Formula - Software Applications - MS Excel - TIK Share

Advance Excel Learning - Concatenate Formula


CA Pulkit Sharma

2013-05-09

=CONCATENATE (text1,text2,...)                                                                                                                                                                          

=CONCATENATE(G1(Cell you want to join),H1(Cell you want to join),I1(Cell you want to join),…...)                                     

 

Concatenate does the opposite of Text to Column, Concatenate is used to Join the content of two or more Cells.          

Concatenate is very useful for everyone who handles lot of Data.                                                                                                         

 

A fine example of concatenate is Date formatting. Sometime when we export data from Accounting system, date format differs from our requirement. You can use concatenate formula along with other functions to convert the date in your required format.

 

Example:

 

Ra

m wen

t t

o sch

ool

yestr

day

 

You can see above that a text is written in different cells and you want to join these cells in one cell to form a meaningful sentence. We can do it using Concatenate formula.

 

=CONCATENATE(A9,B9,C9,D9,E9,F9,G9)

 

Ram went to school yestrday

 

Now consider a bigger example                                                                                                                                                                                                           

Example:                                                                                                                                                                                                                                                         

Suppose you have exported the Bank statement from bank website to Excel. And want to compare the difference between date as per Bank and Date as per Your Accounts Book. And Data is Huge.

 

Doing this task manually will take your full day, but you can do it using excel in less than 2 minutes?

 

Date as per Bank

Date as per your Books

3/13/2012

12-03-2012

4/23/2012

23-04-2012

5/20/2012

19-05-2012

2/28/2012

26-02-2012

06/14/2012

13-06-2012

 

Now you can see that date format as per Bank statement (mm/dd/yyyy) is different from Date format as per your Books (dd/mm/yyyy).

It’s not possible to find the difference between two dates as subtraction is not possible between different formats.

So we will change the format of Bank dates as per Books using Concatenate function along with Text to Column.

First copy the content of Bank column in separate cells.

 

3/13/2012

4/23/2012

5/20/2012

2/28/2012

06/14/2012

 

Select the cells and separate the number using Text to Columns, separate by / in Delimited.

 

3

13

2012

 

 

4

23

2012

 

 

5

20

2012

 

/

2

28

2012

 

 

 6

14

2012

 

 

 

 

once separated now rejoin the numbers in taking Date cell first, Month Cell second and year cell third. But insert the / before each number. / can be inserted by typing / in a different cell. And then adding / after each number.

 

=CONCATENATE(B36,$E$38,A36,$E$38,C36)

 

23/4/2012

20/5/2012

28/2/2012

14/6/2012

 

So you can notice that we have changed the date formate and now you can find out the difference between Bank date and your Books date.

 

Date as per Bank

Date as per your Books

difference

13-03-2012

12-03-2012

 =A51-B51

23/4/2012

23-04-2012

                 -  

20/5/2012

19-05-2012

                   1

28/2/2012

26-02-2012

                   2

14/6/2012

13-06-2012

                   1

 

 

So you have seen how easily you can reduce a day's work in 2 minutes work.

Excercise

 

Student Name

Marks

 Amar

                                          23

 Akbar

                                          45

 Anthony

                                             3

 Ram

                                             5

 Shyam

                                             6

 Geeta

                                        554

join the above word in such manner that we get a sentence like this

   " Student name" got "marks."

 

Download the file for offline practice and reading from the link  concatenate.zip 

 

 

Thank you