Sort comma separated text - Excel Tips and Tricks - video Dailymotion

ExcelTips247

Learn how to sort, separated text.  Sorting comma-separated text, values, or data is a common task for organizing information in various applications like Excel. When you want to arrange data in ascending or descending order based on specific criteria, sorting helps streamline the process. Whether it's a list of names, numbers, or any other information separated by commas, the sorting function simplifies the task by rearranging the elements accordingly. In Excel, sorting comma-separated values involves selecting the data range, accessing the sort function, and specifying the sorting criteria, ensuring that the information is organized in a structured and accessible manner.  By the end of the video you should be able to answer the following question.  Sort comma separated text Sort comma separated values in excel Sort comma separated values   Here's a breakdown of what it does:  =TEXTJOIN(",",TRUE,SORT(TRIM(TEXTSPLIT(A5,",")),,,TRUE))  TEXTSPLIT(A5, ","): This function splits the text in cell A5 into an array using commas as the delimiter. TRIM(TEXTSPLIT(...)): This trims any leading or trailing spaces from each item in the array. SORT(TRIM(TEXTSPLIT(...)), , , TRUE): This sorts the trimmed array alphabetically in ascending order. TEXTJOIN(",", TRUE, ...): This joins the sorted array back into a single text string, with each item separated by commas. So, if cell A5 contains something like "apple,banana,grape", the formula will sort it alphabetically and return "apple,banana,grape".   Sort comma separated text,Sort comma separated values in excel,Sort comma separated values  Check out my complete suite of Microsoft Excel Tips and Tricks. https://www.youtube.com/@jjnet247/shorts https://www.tiktok.com/@exceltips247 https://www.instagram.com/exceltips247/ https://www.dailymotion.com/ExcelTips247 https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/ https://x.com/ExcelTips247/media https://www.reddit.com/r/Excel247/ https://www.facebook.com/XyberneticsInc/reels/  #microsoft #excel #tips #tipsandtricks #microsoftexcel #accounting #fyp #fypシ #exceltips #exceltricks

Transcript

00:00This is one Excel skill that you have to know how to sort comma-separated text. As you can see

00:06in

00:06this data set, the value in cell B3, which is comma-separated, are not in alphabetical order,

00:14but the same value in cell C3, they are in alphabetical order. And this is the formula

00:20that will help you sort your comma-separated text. We're going to start off by splitting

00:25this comma-separated value using a text split function. The first argument in text split

00:32would be the comma-separated value in cell B4, comma. The second argument is your comma as a

00:40delimiter, and then hit enter. As you can see, it is arranged in an array. That's why they are in

00:46five different cells like this. Now let's do a trim so that you can get rid of that extra spaces

00:54after the comma, like that. As you can see, comma, space. You're just trying to get rid of that.

01:00Next, we'll perform a sort function. The first argument would be the array itself, which is

01:07output from your text split and trim. And the second argument, the sort index, we're not overly

01:14concerned about that, and the sort order, we're not concerned about that too, but we want it by

01:21column. So we're going to put this to true, close parenthesis, and hit enter. At this point, you can

01:27see that all the array elements, they are in alphabetical order. Now all you have to do is to

01:33convert this array into a string using a function called text join, open parenthesis. The first

01:43argument would be your delimiter for the text join. We're going to say comma as a delimiter,

01:49and then the second argument would be asking you if you want to ignore empty cells. We're going to

01:56say this as true. And the third argument is the text itself, which is the output from the sort

02:03function. We're going to go all the way to the end of this formula and close parenthesis and hit enter.

02:09As you can see that the cell values, which is a comma separated in B4, is presented in C4, but

02:18it is sorted

02:19in alphabetical order. Now all you have to do is to double click on this field handler and apply the

02:25same formula for the rest of the row, and then you're done.

Sort comma separated text - Excel Tips and Tricks

Category

Transcript

Comments

Recommended