Tracy van der Schyff

#Office365Challenge The next couple of posts will be all about the various functions available in Excel to help you clean data you’ve received from other sources etc. Today we’ll take a look at the Concatenate Formula.

Concatenate is used to join two or more text strings into one string. In this example, I want to Concatenate 4 different cells into one text string. When I start typing the formula, Excel brings up the different options.

Very important to make sure you know the separator to use in Excel, refer to a previous blog about this:  Around the Office in 365 Days: Day 44 – Excel / SharePoint Formulas using Commas or Semicolons. Excel will give you a preview of what the formula will look like – this is where you can see it whether it uses a semicolon or comma:

By selecting the cells with semicolons in between (or commas, based on your settings), it will Concatenate the string with no spaces etc. between the text (words):

To add text or spaces in between the text (words) you need to do the following. Select the cell, separate with the semicolon (comma), type “xxx”, then add another semicolon (comma) before selecting the next cell. The 3 x’s being the text/space you would like to add in between. I just used a space in the example below:

And there you go!

If you suck at formulas (like me), you are gonna be ok. I promise. Excel gives you a preview of the formula as you start typing with “tips” – which helps a lot. Just take your time, read it – try to figure out what you need to do.

Overview of my challenge: As an absolute lover of all things Microsoft, I’ve decided to undertake the challenge, of writing a blog every single day, for the next 365 days. Crazy, I know. And I’ll try my best, but if I cannot find something good to say about Office 365 and the Tools it includes for 365 days, I’m changing my profession. So let’s write this epic tale of “Around the Office in 365 Days”. My ode to Microsoft Office 365.

Keep in mind that these tips and tricks do not only apply to Office 365 – but where applicable, to the overall Microsoft Office Suite and SharePoint.