Advance Excel Learning - Concatenate Formula - Software Applications - MS Excel - TIK Share
0 Likes 846 Views

# 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."

Thank you

#### Top users of the month (December)

CA Pulkit Sharma, 50 Points
ICube Icube, 40 Points
R Shetty, 30 Points