Joining Data Strings Using CONCATENATE, & - to be Updated

4 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  A$195.60
You save:  A$55.89
List Price:  ৳11,872.36
You save:  ৳3,392.34
List Price:  CA$187.37
You save:  CA$53.54
CHF 91.26
List Price:  CHF 127.77
You save:  CHF 36.51
List Price:  kr884.75
You save:  kr252.80
List Price:  €118.74
You save:  €33.93
List Price:  £107.27
You save:  £30.65
List Price:  HK$1,084.99
You save:  HK$310.02
List Price:  ₹10,502.85
You save:  ₹3,001.03
List Price:  RM586.20
You save:  RM167.50
List Price:  ₦52,986.21
You save:  ₦15,140
List Price:  kr1,265.84
You save:  kr361.69
List Price:  NZ$211.96
You save:  NZ$60.56
List Price:  ₱6,870.27
You save:  ₱1,963.07
List Price:  ₨23,504.32
You save:  ₨6,716
List Price:  S$192.17
You save:  S$54.91
List Price:  ฿4,370.86
You save:  ฿1,248.90
List Price:  ₺1,021.10
You save:  ₺291.76
List Price:  R2,469.09
You save:  R705.50
Already have an account? Log In


Hello everybody. In this video lesson we'll see how to combine different cells content together. So lies before me three columns A, B and C. And I wish to have the output in the fourth cell with censorship valia joined on fourth of December 2013 right. And the data points you have is the name first name, last name and the date of joining, which has been fed in and has been imported by the system. Now, it goes like this equal to I choose the first name, I put an M person and in a hurry, I choose Puglia as well. When I press enter, or I noticed the do get joined, but there is a space which would have been required in between the first and the last name.

So, what do we do? I go back, I deleted and write afresh equal to the cell containing the first name. I put an person, I put a double quote Please note, it's not a single code written twice No. It is precisely double quote. You put a space again close the double quote. And then again ampersand so ampersand acts like a glue.

It makes the text stick together then I choose to gallia Okay, and then again I put an ampersand and why would I do that because I wish to added group of text which says joined on. Okay joined on. So in double quote, I'm putting a space and writing joined on putting a space and closing the double coat, enter. So I've been able to successfully complete the sentence till the point of on and I'm person being there acting as a glue, and that glue binds these together. Now when it comes to date, I might choose this and choose date for December 30, enter. Notice what I get is 41612. reason why I get so is because internally every valid date is a number.

If I use general, I get this number. If you wish to know more about the logic of this number, please refer to one of our videos on why every date is a number from access point of view. Now, in this case, I wish to have the format just like this. Okay. Now in this case, what I'll be using is a magic formula. What is that?

I'll be using the formula text. Okay. It asks me the date value which I gladly choose, comma, what is the format you are looking for? So the format must be given in a pair of double quotes. I understand the formula is not complete, and I want to finish it off now. So then I write PD putting a space mmm m m, putting a space and y y, y y into super.

So you can put hyphen, you can put slash in between those DS and the M's and the y's. And you can also reduce one m so that this becomes a shorter version of the initial sentence. So my intention was to show you how the ampersand works to help you get the concatenated data the join the string. In fact, a quick clone of this formula is called concatenate. Let me show you how this works. concatenate.

Of course, there is no difference between them in terms of output. I choose this, I put an ampersand double quote with space. Again, an ampersand choosing the last name again and I'm person. Now I'm going to initiate that sentence with says joined. On space, double quotation closed, again an ampersand and I'll reserve the For the text formula within text, I am going to choose the date and comma in double quotes. I am going to put the same combination DD space.

Mm hmm, space yy, yy. So notice the formula. Now what if I say that the formula is asking me to put a comma? Will it work? Well, it does. So either you put an ampersand inside or you put a comma, it will still work.

You may check it out. Enter. Aha. So both the combinations work actually, but I prefer to use the first instance where it just uses the ampersand. simple and straight. Thank you for watching and hope this helps you somewhere down the line in Excel.

Sign Up