• Formulas
  • Excel Tips & Tricks
  • Excel Charting
  • Tutorials
  • VBA
  • Book Store
  • More  
    • Blog
    • Download Area
    • Excel Video Tutorials
Home » Formula Friday- Using Concatenate

Formula Friday- Using Concatenate

October 2, 2021 by Barbara

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.

concatenate in Excel

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)

Excel concatenate Pin it! Share on Facebook

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)

Concatenate cells in Excel Pin it! Share on Facebook

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

Pin it! Share on Facebook

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.

Pin it! Share on Facebook

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”)

Pin it! Share on Facebook

Job Done! It is that easy to use concatenate in Excel.

Do you want even more Excel tips?.

  • Concatenate A Range Of Cells With VBA
  • How To Concatenate Numbers And Percentages – Formula Friday
  • Excel Tip – Insert The Contents Of A Cell Into A Text String – Formula Friday
  • A Formula To Check If You Have Extra Spaces In Your Data – Formula Friday
  • Formula Friday – How To Use The DAY Function In Excel With Examples.
Excel Macro

Filed Under: Blog, Formulas Tagged With: Excel, Excel Tips, Formula

Tags

Blog Cells Excel Excel Charting Excel Excel Tips Excel Formulas excel tip Excel Tip Category Excel Tips Excel Tips& Tricks excel tutorials Excel Videos Excel Video Tutorials formatting Formula formula friday Formulas macro Macro Mondays Macros Pivot Table Pivot Tables Power BI Power Query Tutorials vb VBA Worksheets

Recommended Excel Resources

Free Excel Dashboard Webinar

Copyright © 2023 · Enterprise Pro Theme on Genesis Framework · WordPress · Log in

SHARE

Additional Info

Want FREE Excel Tips?

Click on my FREE eBook, its my bonus for joining thousands of others who receive my 3 xFREE Excel tips every month in my Excel Newsletter. Join Us!.

Excel Jobs

Excel Charting

Self Expanding Charts
One Click Charts
Create Quick Dynamic Charts
Easy Combination Charts

privacy policy