How to Concatenate Columns in Calculated Column in Microsoft Lists - How to use Microsoft Lists

hey nerds Amy here and today we are going to learn two ways that you can concatenate or combine columns within Microsoft list as I do with any calculated field I start with the basics and then I build on it so that you can learn how to use this to its potential and as a little bonus for you at the end I have shown you how we can add a date column into these formulas and some different formatting options as well so that you can customize it to your needs so with that being said let's ner it out at Amy's animal barn and Petco we have this suppliers list and we are going to create a new column here called address where we are going to combine the city the state The Province and Country and these two first Fields here are both single lines of text and then the country as you can see is a choice column so I just wanted to demonst demate that this process can be used for different column types the first thing that we need to do is head on up to add column and then we need to scroll on down to see all column types from here we will add a name which is going to be address and then we need to select what type of column this is going to be and in this instance we are going to do a calculated column so we will click that and then once again we will scroll on down and here is where we entered the formula so the first example we are going to use is concatenate which is a bit of a mouthful and we are going to just doubleclick the columns that we want and then separate them by a comma so we're going to go city state Prov and then country and we just need to ensure that we pop that closing bracket down here we will enter the output which in this case we're going to to do a single line of text and then we can click on okay all right look at that so we can see here that those fields have fed over appropriately and they are all now combined but it's a little bit difficult to read and I actually want to add a comma in a space between each item so in order to do that we just head on up to this gear icon and then click on list settings then we need to scroll on down and find the column that we just created which in this case it's the address column so now down here I just want to highlight that this is one item so city is one item State Prov is one item and country is one item and using this formula within SharePoint list we can have up to 30 items and these items are separated by commas so now what we want to do is add a comma and a space between these items which is going to be an additional item so we're going to have City comma and then we need to start off with quotation marks and because I want to have a comma first we're going to go comma and then space quotation marks and then now I need to put another comma to close out that item so that we can go into the next area I'm going to do that once again here between State Province and country and we're going to start off with quotations comma space quotations comma to close out that item now we can scroll on down and click okay so to get back to our list we can just click on this pet suppliers which is the list name it takes you to the SharePoint page here we can see the address in action and look at that we have a beautiful comma in space between each of these address items before heading on to my favorite way to combine columns within lists I just want to show you first how Dynamic this concatenate formula is here we are in that formula area once again and so far we have shown you how you can add a space or a comma between these items so let's now say that we want to pop in an an sign for example and then over here if you are enjoying this video then please give my video a thumbs up as it would really mean a lot to me here we are back in the list and we can just see how Dynamic that formula is and how it all works together with the different text items now on to my favorite way of combining cells we will once again go add column and then scroll on down to see all column types to add that calculated field I'm going to call this one addresses because we've already used address and then we are going to select the calculated column type down here in the formula we are simply going to go equals and then now we will just double click on the column and then here we are going to go and and this is just going to Simply combine the two columns so let's just take a look at how this works before we build onto anything else here we can see how that Ampersand symbol is combining these two cells and in my opinion that's just a little bit easier rather than trying to remember how to spell concatenate and then you know adding that closing bracket at the end which can sometimes throw you off now let's say that we want to once again add that comma in a space between these items we are going to head on back to you guessed it list settings and then we are going to scroll on down and select the column that we want to edit so here we have that formula and in order to add anything between these we need to ensure that we have that Ampersand symbol then a quotation mark so you're kind of seeing this go through again and now we are going to go comma space quotations andand and when I first started learning Excel my teacher suggested that we can easily remember this as think about a hamburger I know it's funny but every time that I use this formula in Excel I think about a hamburger and the Amper stand symbols are the buns and then the quotations are the bacon strips and then whatever you want to put in your burger will go between those two quotation marks so here we have our burger and the additional items that we're adding here is a comma in a space it's not as tasty as you know maybe some guacamole but hey that's okay we're just using Microsoft list so now we're going to click okay and here we are back in that list and you can see how my hamburger method has now added a nice beautiful comma into space between these two items now I want to show you how we can build on this formula so once again we are going to go Ampersand and then if you are enjoying this video then please hit that subscribe button it would mean a lot to me and it also means that you will get notified on future videos and content releases so we are going to put that subscribe between quotation marks and then we are going to now do an ampersand symbol once again and include that country so now we are going to see how all of this pulls through into the list here we are back in that list and we can see how that has pulled through so that formula I find is a lot simpler than using the concatenate as you can see but hey which one works for you is going to depend on your needs and your preferences so let me know which one you prefer in the comments below are you ready things are about to get pretty real here because I am now going to show you how we can combine this country field with the S date so you guessed it we are now going to add another calculated column it is so much fun we are going to give this a column name and it's going to be called supplier since then we are going to head to calculated and scrolling on down to that formula field we are going to go equals and then now we are just going to double click the country and then we need to do our hamburger so we are going to go bun bacon I would like to include a space between these items and then we are going to go bacon bun once again this is where it gets fun so we are going to add the text formula so we will type text and then just ensure that you add that opening bracket now this is where we Define the column that we want so we are going to double click on this since date and then now we have to add a comma and this is where we Define the format of that date so we can do a simple format of month month day day year year year year I guess this would depend on which country you're in and whether you'd like to have the month or the day first but you can play around and customize it as you see fit so now we are going to add the closing quotations and then that closing bracket do not forget about that because we did that text here with that opening bracket we need to now close off that formula all right let's take a look and see how this is working and here we are look at that it has pulled through and now combined those two Fields I'm going to show you one last little formatting here for the date column before we wrap up so just stick around because it will show you how you can customize things to your needs when you are you know combining the date and another column within list so once again we are just going to go to supplier s to edit it and then now say we want it to show month but as in the spelling of it and using that shorter version so the first three letters of the month we can just do that and you know what I'm going to add a dash here as well as a space just to you know add a little bit more of breathing room between those two items here we are back in list and you can see how that date formatting change has now been updated within the list I did just want to add that mine did not update right away so I'm not too sure if there's a bit of a leg in the calculated column updating World um but I did just want to add that there if you did have any issues with that as well if your formula did not work you would get a warning sign so if you didn't get that warning sign and it did click through then it might just take a little bit of time to update in the back end all right so that wraps up this video and I do hope that I've helped you discover your inner nerd please let me know which of these formulas worked better for you or which one you prefer in the comments below I would really love to know all right thanks for nering out we'll see you again

Channel: Office Skills with Amy

Views: 2,437

Keywords: office skills, technology skills, how-to video, learn computer skills, Microsoft 365, Microsoft Office, 365 apps, Microsoft Outlook, Microsoft Teams, Microsoft Excel, Microsoft PowerPoint, Microsoft OneNote, CRM, Lead Management, Administrative Skills, 2023, Computer Programs, How-to be efficient at work, How-to save time at work, Office Skills with Amy, OSWA, concatenate, combine, columns, calculated column, sharepoint lists, concatenate date, date format, Microsoft Lists

Id: 3ZLcC-s0ytM

Length: 11min 16sec (676 seconds)

Published: Tue Oct 03 2023