Joining Data Strings Using CONCATENATE, & - to be Updated

Advanced Excel Crash Course Section 12: Data Cleaning
4 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€91.73
List Price:  €128.43
You save:  €36.69
£78.50
List Price:  £109.90
You save:  £31.40
CA$135.42
List Price:  CA$189.60
You save:  CA$54.17
A$152.22
List Price:  A$213.12
You save:  A$60.89
S$133.77
List Price:  S$187.28
You save:  S$53.51
HK$781.89
List Price:  HK$1,094.68
You save:  HK$312.79
CHF 88.30
List Price:  CHF 123.63
You save:  CHF 35.32
NOK kr1,058
List Price:  NOK kr1,481.24
You save:  NOK kr423.24
DKK kr684.10
List Price:  DKK kr957.77
You save:  DKK kr273.66
NZ$164.15
List Price:  NZ$229.81
You save:  NZ$65.66
د.إ367.20
List Price:  د.إ514.09
You save:  د.إ146.89
৳10,978.23
List Price:  ৳15,369.96
You save:  ৳4,391.73
₹8,290.53
List Price:  ₹11,607.08
You save:  ₹3,316.54
RM471.80
List Price:  RM660.54
You save:  RM188.74
₦156,534.34
List Price:  ₦219,154.34
You save:  ₦62,620
₨27,950.66
List Price:  ₨39,132.05
You save:  ₨11,181.38
฿3,595.26
List Price:  ฿5,033.51
You save:  ฿1,438.25
₺3,229.78
List Price:  ₺4,521.83
You save:  ₺1,292.04
B$499.40
List Price:  B$699.18
You save:  B$199.78
R1,883.20
List Price:  R2,636.56
You save:  R753.35
Лв179.42
List Price:  Лв251.19
You save:  Лв71.77
₩133,286.20
List Price:  ₩186,606.01
You save:  ₩53,319.81
₪365.03
List Price:  ₪511.06
You save:  ₪146.02
₱5,561.09
List Price:  ₱7,785.75
You save:  ₱2,224.66
¥14,911.76
List Price:  ¥20,877.07
You save:  ¥5,965.30
MX$1,670.32
List Price:  MX$2,338.52
You save:  MX$668.19
QR364.82
List Price:  QR510.77
You save:  QR145.94
P1,359.04
List Price:  P1,902.71
You save:  P543.67
KSh13,398.66
List Price:  KSh18,758.66
You save:  KSh5,360
E£4,718.65
List Price:  E£6,606.31
You save:  E£1,887.65
ብር5,656.15
List Price:  ብር7,918.84
You save:  ብር2,262.68
Kz83,512.74
List Price:  Kz116,921.18
You save:  Kz33,408.44
CLP$93,810.03
List Price:  CLP$131,337.80
You save:  CLP$37,527.76
CN¥710.61
List Price:  CN¥994.89
You save:  CN¥284.27
RD$5,917.87
List Price:  RD$8,285.25
You save:  RD$2,367.38
DA13,435.55
List Price:  DA18,810.31
You save:  DA5,374.76
FJ$226.69
List Price:  FJ$317.37
You save:  FJ$90.68
Q780.94
List Price:  Q1,093.35
You save:  Q312.40
GY$20,949.18
List Price:  GY$29,329.69
You save:  GY$8,380.51
ISK kr13,659.63
List Price:  ISK kr19,124.03
You save:  ISK kr5,464.40
DH1,002.67
List Price:  DH1,403.78
You save:  DH401.11
L1,768.33
List Price:  L2,475.73
You save:  L707.40
ден5,652.42
List Price:  ден7,913.61
You save:  ден2,261.19
MOP$805.88
List Price:  MOP$1,128.27
You save:  MOP$322.38
N$1,866.55
List Price:  N$2,613.25
You save:  N$746.69
C$3,681.10
List Price:  C$5,153.70
You save:  C$1,472.59
रु13,266.49
List Price:  रु18,573.62
You save:  रु5,307.13
S/368.83
List Price:  S/516.39
You save:  S/147.55
K382.06
List Price:  K534.91
You save:  K152.84
SAR375
List Price:  SAR525.02
You save:  SAR150.01
ZK2,508.19
List Price:  ZK3,511.57
You save:  ZK1,003.37
L456.04
List Price:  L638.48
You save:  L182.43
Kč2,310.02
List Price:  Kč3,234.13
You save:  Kč924.10
Ft36,211.46
List Price:  Ft50,697.50
You save:  Ft14,486.03
SEK kr1,038.11
List Price:  SEK kr1,453.39
You save:  SEK kr415.28
ARS$85,066.49
List Price:  ARS$119,096.49
You save:  ARS$34,030
Bs691.23
List Price:  Bs967.75
You save:  Bs276.52
COP$389,029.38
List Price:  COP$544,656.70
You save:  COP$155,627.31
₡50,979.09
List Price:  ₡71,372.77
You save:  ₡20,393.67
L2,470.17
List Price:  L3,458.33
You save:  L988.16
₲730,160.41
List Price:  ₲1,022,253.79
You save:  ₲292,093.37
$U3,841.23
List Price:  $U5,377.88
You save:  $U1,536.64
zł395.31
List Price:  zł553.46
You save:  zł158.14
Already have an account? Log In

Transcript

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

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.