Hi again, it’s Formula Friday yet again. This time, let’s look at CONCATENATE in Excel. Now, I always say to users who ask me about concatenate, do not be put off by the long word. It, in essence, simply means to join the contents of two cells. However, there is another way to achieve the same result. So, I will discuss this as an alternative at the end of this post.
Concatenate Syntax. A Reminder.
The syntax of this formula is
=CONCATENATE(TEXT1,TEXT2…)
Up to 255 text entries can be added to the function and each one of them should be separated by a comma.
One point to note, CONCATENATE in Excel does NOT add in extra spaces between text, in order to do this you need to accommodate these extra spaces within the formula. Let’s take a look at an example.
The First Name and Surname are in Columns B and C in Excel. The aim is to join them so both names in are in one Column- D.
=CONCATENATE(B2,C2)
In most cases with names you will want to show a space between the first and last name. All we need to do is change the concatenate formula to include space character is quotation marks like this.
=CONCATENATE(B2,” “,C2)
Bit of a long winded way to join a few pieces of text isn’t it. Well, yes I agree and you can achieve the same result with waaaay less typing, always a good thing in my world. All we need to do is use the & sign. Really? Yes really easy as that.
Let’s give the same example
=B2&C2
Again if you want to include spaces between your joined text then all you need to do is amend your formula to also include a space character in quotes. See below.
=B2&” “&C2
What About Combining Text and a Date?
Yes we can do that too. Not a problem, well one small mini problem-the number and dates lose their formatting so if I add in a date column to add to the Fist Name and Surname , it ends up looking like this using the same formula as before but just adding in ell D2. Excel does not take into account the formatting of a date here – see below.
But as ever we can put this right in Excel just by including the TEXT function into our formula to re format our dates Let’s give it a go.
=B2& ” ” &C2&” “& TEXT(D2,”dd-mmm-yyyy”)
Job Done! It is that easy to use concatenate in Excel.
Do you want even more Excel tips?.
