title
Data cleaning in Excel - 10 tricks *PROs* use all the time

description
Good data is the cornerstone of great analysis. In this video, learn 10 powerful yet EASY ways for data cleaning in Excel that not many know. The tricks you will learn are, 0:00 - Introduction 00:18 - Extracting first name with Flash fill feature of Excel 1:47 - Converting numbers to percentages 3:18 - Remove blank cells or rows using Goto special 4:43 - Find common values between two lists 5:32 - Clean-up ugly formats with one click 6:15 - Bonus trick #1 - Remove extra spaces 7:30 - Unstack data easily with simple formulas 9:44 - Removing duplicate combinations in your data 10:37 - Deleting yellow or any colour cells 12:09 - Extract numbers from text values easily 13:35 - Get file names from full path with Find Replace in Excel 15:36 - Bonus trick #2 - Dealing with numbers formatted as text Example file for practice: ==================== Please download example file with dirty data to practice here. https://chandoo.org/wp/wp-content/uploads/2021/03/10-powerful-ways-to-clean-data-in-Excel-demo.xlsx More on Data Cleaning with Excel: ============================ 10 ways to clean data in Excel (2022 techniques) - https://youtu.be/q7EpoOwBcnM How to use Power Query to clean data - https://youtu.be/PiFAa_jjaEI Combine Excel Files Automatically - https://youtu.be/SGzegma9bdY #ExcelTricks #DataCleanup

detail
{'title': 'Data cleaning in Excel - 10 tricks *PROs* use all the time', 'heatmap': [{'end': 295.668, 'start': 230.989, 'weight': 0.802}, {'end': 345.28, 'start': 309.739, 'weight': 0.734}, {'end': 373.567, 'start': 352.306, 'weight': 0.778}, {'end': 574.326, 'start': 509.281, 'weight': 0.73}, {'end': 728.123, 'start': 701.55, 'weight': 0.711}, {'end': 809.528, 'start': 786.131, 'weight': 0.736}, {'end': 934.559, 'start': 888.112, 'weight': 0.956}, {'end': 1005.52, 'start': 959.795, 'weight': 0.8}], 'summary': 'Learn 10 powerful ways to clean data in excel with 2 bonus methods, including using flash fill, percentage conversion, removing blank rows, finding duplicates, using trim function, unstacking data, and extracting numbers and file names from full paths.', 'chapters': [{'end': 81.031, 'segs': [{'end': 81.031, 'src': 'embed', 'start': 32.561, 'weight': 0, 'content': [{'end': 38.664, 'text': 'Just go to the adjacent cell and type the first name of the first few items.', 'start': 32.561, 'duration': 6.103}, {'end': 47.348, 'text': 'As you start typing, Excel will guess what you are doing and automatically suggest that remaining first names.', 'start': 39.304, 'duration': 8.044}, {'end': 49.491, 'text': 'this feature is called flash fill.', 'start': 47.348, 'duration': 2.143}, {'end': 60.067, 'text': 'now, when you see this kind of highlighted values like in dull color, just press enter and excel will automatically do the extraction for you.', 'start': 49.491, 'duration': 10.576}, {'end': 67.056, 'text': "you can undo this if you're not happy, especially if you would like to tweak the way this needs to happen.", 'start': 60.848, 'duration': 6.208}, {'end': 72.082, 'text': "for example, i'm going to show you how the same can be done for last name.", 'start': 67.056, 'duration': 5.026}, {'end': 81.031, 'text': "now i'll say f-a-u-g-h-n-y, phony cross white, and, as I'm doing it, already showed me the last name as well.", 'start': 72.082, 'duration': 8.949}], 'summary': "Excel's flash fill feature automatically suggests and extracts data, enhancing efficiency.", 'duration': 48.47, 'max_score': 32.561, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw32561.jpg'}], 'start': 0.329, 'title': 'Excel data cleaning tips', 'summary': 'Demonstrates 10 powerful ways to clean data in excel, with an additional 2 bonus methods, including using flash fill to extract first and last names with ease.', 'chapters': [{'end': 81.031, 'start': 0.329, 'title': 'Excel data cleaning tips', 'summary': 'Demonstrates 10 powerful ways to clean data in excel, with an additional 2 bonus methods, including using flash fill to extract first and last names with ease.', 'duration': 80.702, 'highlights': ['Using Flash Fill to automatically extract first names by typing a few examples and letting Excel suggest the remaining names, which can be undone if necessary.', 'Illustrating how the same can be done for last names, with the process automatically suggesting the last name as well.']}], 'duration': 80.702, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw329.jpg', 'highlights': ['Illustrating how the same can be done for last names, with the process automatically suggesting the last name as well.', 'Using Flash Fill to automatically extract first names by typing a few examples and letting Excel suggest the remaining names, which can be undone if necessary.']}, {'end': 448.507, 'segs': [{'end': 110.151, 'src': 'embed', 'start': 81.391, 'weight': 0, 'content': [{'end': 87.656, 'text': 'But if there are some middle names somewhere you can go and tweak that and Excel will pick up the pattern and show this.', 'start': 81.391, 'duration': 6.265}, {'end': 96.062, 'text': "You can also if the suggestion doesn't come up type the value and press control E to trigger the flash fill.", 'start': 88.236, 'duration': 7.826}, {'end': 102.968, 'text': 'You can find more about flash fill from the data section, data ribbon and flash fill button.', 'start': 96.583, 'duration': 6.385}, {'end': 104.609, 'text': "So that's number one.", 'start': 103.768, 'duration': 0.841}, {'end': 110.151, 'text': 'number two number to percentage.', 'start': 106.63, 'duration': 3.521}], 'summary': "Excel's flash fill feature automates data entry and pattern recognition.", 'duration': 28.76, 'max_score': 81.391, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw81391.jpg'}, {'end': 228.747, 'src': 'embed', 'start': 170.998, 'weight': 1, 'content': [{'end': 177.443, 'text': 'now, at this point, if you apply percentage formatting, you will get the percentage thing done.', 'start': 170.998, 'duration': 6.445}, {'end': 179.744, 'text': 'but this requires two steps.', 'start': 177.443, 'duration': 2.301}, {'end': 182.446, 'text': "let's show another technique.", 'start': 179.744, 'duration': 2.702}, {'end': 190.23, 'text': 'you can go to a cell and type one percent in there, copy that one percent and then select these.', 'start': 182.446, 'duration': 7.784}, {'end': 195.213, 'text': 'paste special and do a multiplication instead.', 'start': 190.23, 'duration': 4.983}, {'end': 198.454, 'text': 'this will apply the percentage format.', 'start': 195.213, 'duration': 3.241}, {'end': 201.136, 'text': 'number three remove blank rows.', 'start': 198.454, 'duration': 2.682}, {'end': 211.467, 'text': 'Let us say you have copied some data from another system or you have done an import and you have noticed that some of the values are blank,', 'start': 202.304, 'duration': 9.163}, {'end': 219.009, 'text': "and wherever there is a blank value, you want to remove that entire row because it's irrelevant to data for your analysis.", 'start': 211.467, 'duration': 7.542}, {'end': 228.747, 'text': 'You can manually select and delete them but this is gonna take a while especially if you have multiple columns and hundreds of rows.', 'start': 220.478, 'duration': 8.269}], 'summary': 'Apply percentage formatting and remove blank rows in excel efficiently.', 'duration': 57.749, 'max_score': 170.998, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw170998.jpg'}, {'end': 295.668, 'src': 'heatmap', 'start': 230.989, 'weight': 0.802, 'content': [{'end': 236.275, 'text': 'Select all your data and now press F5, function key 5 key.', 'start': 230.989, 'duration': 5.286}, {'end': 241.04, 'text': 'This opens the goto box and from here click on special.', 'start': 236.735, 'duration': 4.305}, {'end': 249.231, 'text': 'Now using the go to special area, you can select items that are meeting a specific criteria.', 'start': 242.765, 'duration': 6.466}, {'end': 252.613, 'text': 'In our case, we would like to select all the blank cells.', 'start': 249.791, 'duration': 2.822}, {'end': 255.616, 'text': 'So click on blanks and OK.', 'start': 252.834, 'duration': 2.782}, {'end': 263.282, 'text': 'Now what Excel will do is in the highlighted range, it will find all the blank cells and it will select them for you.', 'start': 256.255, 'duration': 7.027}, {'end': 268.627, 'text': 'Right Once you have selected, press the shortcut control minus.', 'start': 263.943, 'duration': 4.684}, {'end': 270.308, 'text': 'This is for deleting cells.', 'start': 268.827, 'duration': 1.481}, {'end': 275.052, 'text': 'and wherever there is a blank cell, we would like to remove the entire row.', 'start': 271.249, 'duration': 3.803}, {'end': 280.437, 'text': 'so just say entire row and click ok, your data is cleaned up.', 'start': 275.052, 'duration': 5.385}, {'end': 282.999, 'text': 'all the blanks are gone.', 'start': 280.437, 'duration': 2.562}, {'end': 285.181, 'text': 'number four find duplicates.', 'start': 282.999, 'duration': 2.182}, {'end': 295.668, 'text': 'Let us say you have a set of invoice numbers that are provided by Ross and another set of invoice numbers provided by Monica,', 'start': 286.161, 'duration': 9.507}], 'summary': "Using excel's 'go to special' function, you can easily select and delete all blank cells to clean up the data.", 'duration': 64.679, 'max_score': 230.989, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw230989.jpg'}, {'end': 282.999, 'src': 'embed', 'start': 256.255, 'weight': 3, 'content': [{'end': 263.282, 'text': 'Now what Excel will do is in the highlighted range, it will find all the blank cells and it will select them for you.', 'start': 256.255, 'duration': 7.027}, {'end': 268.627, 'text': 'Right Once you have selected, press the shortcut control minus.', 'start': 263.943, 'duration': 4.684}, {'end': 270.308, 'text': 'This is for deleting cells.', 'start': 268.827, 'duration': 1.481}, {'end': 275.052, 'text': 'and wherever there is a blank cell, we would like to remove the entire row.', 'start': 271.249, 'duration': 3.803}, {'end': 280.437, 'text': 'so just say entire row and click ok, your data is cleaned up.', 'start': 275.052, 'duration': 5.385}, {'end': 282.999, 'text': 'all the blanks are gone.', 'start': 280.437, 'duration': 2.562}], 'summary': "Excel can delete all blank cells in a range and entire rows using 'control minus', cleaning up the data.", 'duration': 26.744, 'max_score': 256.255, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw256255.jpg'}, {'end': 373.567, 'src': 'heatmap', 'start': 309.739, 'weight': 4, 'content': [{'end': 319.947, 'text': 'Now that you have highlighted both lists, just go to home, conditional formatting, highlight cell rules and click on duplicate values.', 'start': 309.739, 'duration': 10.208}, {'end': 327.293, 'text': 'This will instantly show you all the invoice numbers that are appearing in both lists in a different color.', 'start': 320.668, 'duration': 6.625}, {'end': 331.376, 'text': 'A great way to find any duplicates between two lists.', 'start': 327.753, 'duration': 3.623}, {'end': 335.78, 'text': 'Number five, clean up formats.', 'start': 333.598, 'duration': 2.182}, {'end': 345.28, 'text': 'Let us say you have some data that you have downloaded or a colleague provided to you, and all the information is there,', 'start': 336.693, 'duration': 8.587}, {'end': 347.742, 'text': 'but the formatting is all messed up.', 'start': 345.28, 'duration': 2.462}, {'end': 351.686, 'text': "You don't have to go and manually clean any of this up.", 'start': 348.263, 'duration': 3.423}, {'end': 358.671, 'text': 'You can simply select all your data and from the home ribbon there is a clear button.', 'start': 352.306, 'duration': 6.365}, {'end': 363.656, 'text': 'Just click on that little down arrow and say clear formats.', 'start': 359.372, 'duration': 4.284}, {'end': 370.123, 'text': 'This will instantly tidy up your data and set it back to the original raw formatting.', 'start': 364.237, 'duration': 5.886}, {'end': 373.567, 'text': 'From here, you can format it in the way that you like.', 'start': 370.564, 'duration': 3.003}], 'summary': 'Identify duplicate values in both lists and clear up formats instantly.', 'duration': 53.917, 'max_score': 309.739, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw309739.jpg'}, {'end': 448.507, 'src': 'embed', 'start': 421.515, 'weight': 5, 'content': [{'end': 430.02, 'text': 'What trim function does is it will remove any spaces from the end as well as beginning, and if there is any extra spaces in the middle,', 'start': 421.515, 'duration': 8.505}, {'end': 432.362, 'text': 'more than one space, that will also be removed.', 'start': 430.02, 'duration': 2.342}, {'end': 440.923, 'text': 'Once you have that formula, just drag it down or go to the corner of the first cell and double click,', 'start': 433.559, 'duration': 7.364}, {'end': 448.507, 'text': 'and it will automatically clean up the values and give you data without those spaces.', 'start': 440.923, 'duration': 7.584}], 'summary': 'The trim function removes spaces from start, end, and middle, cleaning up data.', 'duration': 26.992, 'max_score': 421.515, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw421515.jpg'}], 'start': 81.391, 'title': 'Excel tips and data cleaning', 'summary': 'Covers excel tips such as flash fill, percentage conversion, and removing blank rows, as well as data cleaning tips like removing blank cells, finding duplicates, and using the trim function.', 'chapters': [{'end': 201.136, 'start': 81.391, 'title': 'Excel tips: flash fill, percentage conversion, and removing blank rows', 'summary': 'Covers excel tips including using flash fill for pattern recognition, converting numbers to percentages, and removing blank rows from a dataset.', 'duration': 119.745, 'highlights': ['Using flash fill for pattern recognition Excel can use flash fill to recognize patterns and provide suggestions for data manipulation, improving efficiency in data processing.', 'Converting numbers to percentages By using a simple technique of dividing by 100 or using multiplication, Excel can efficiently convert numbers to percentages, streamlining the process of data transformation.', 'Removing blank rows Excel allows for the removal of blank rows in a dataset, enhancing data cleanliness and organization for analysis and presentation purposes.']}, {'end': 448.507, 'start': 202.304, 'title': 'Excel data cleaning tips', 'summary': 'Provides quick and efficient excel tips, including removing blank cells, finding duplicates, cleaning up formats, and using the trim function to clean inconsistent spacing.', 'duration': 246.203, 'highlights': ["Removing Blank Cells By using the 'Go To Special' feature and 'Control Minus' shortcut, Excel can quickly remove all blank cells in the selected range, saving time and effort in data cleaning.", "Finding Duplicates The process of identifying duplicate values between two lists by using the 'Conditional Formatting' feature, which efficiently highlights the duplicate invoice numbers in different colors.", "Cleaning Up Formats The 'Clear Formats' option in Excel's home ribbon provides a simple way to instantly tidy up the data and reset it to its original raw formatting, offering a time-saving solution for data formatting issues.", "Using Trim Function for Inconsistent Spacing Demonstrating the use of the 'Trim' function to efficiently remove extra spaces from the beginning and end of values, as well as any inconsistent spacing within the data, providing a quick solution for cleaning up inconsistent data."]}], 'duration': 367.116, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw81391.jpg', 'highlights': ['Using flash fill for pattern recognition Excel can use flash fill to recognize patterns and provide suggestions for data manipulation, improving efficiency in data processing.', 'Converting numbers to percentages By using a simple technique of dividing by 100 or using multiplication, Excel can efficiently convert numbers to percentages, streamlining the process of data transformation.', 'Removing blank rows Excel allows for the removal of blank rows in a dataset, enhancing data cleanliness and organization for analysis and presentation purposes.', "Removing Blank Cells By using the 'Go To Special' feature and 'Control Minus' shortcut, Excel can quickly remove all blank cells in the selected range, saving time and effort in data cleaning.", "Finding Duplicates The process of identifying duplicate values between two lists by using the 'Conditional Formatting' feature, which efficiently highlights the duplicate invoice numbers in different colors.", "Using Trim Function for Inconsistent Spacing Demonstrating the use of the 'Trim' function to efficiently remove extra spaces from the beginning and end of values, as well as any inconsistent spacing within the data, providing a quick solution for cleaning up inconsistent data.", "Cleaning Up Formats The 'Clear Formats' option in Excel's home ribbon provides a simple way to instantly tidy up the data and reset it to its original raw formatting, offering a time-saving solution for data formatting issues."]}, {'end': 1020.352, 'segs': [{'end': 498.297, 'src': 'embed', 'start': 448.747, 'weight': 6, 'content': [{'end': 450.128, 'text': "That's the first bonus trick.", 'start': 448.747, 'duration': 1.381}, {'end': 454.891, 'text': 'Number six, unstack data.', 'start': 452.55, 'duration': 2.341}, {'end': 462.902, 'text': 'Imagine you have copied or someone provided you some data where the data is not in the proper order.', 'start': 455.976, 'duration': 6.926}, {'end': 470.328, 'text': 'So you have addresses where the street address, city and state are in different cells and they go like this.', 'start': 463.002, 'duration': 7.326}, {'end': 473.991, 'text': 'And this kind of format is called stack data,', 'start': 470.989, 'duration': 3.002}, {'end': 480.397, 'text': 'because everything sits one on top of another and you would like to unstack it so that you get it like in this format.', 'start': 473.991, 'duration': 6.406}, {'end': 488.453, 'text': 'you may think this requires a bit of complex formulas or anything, but this is really simple.', 'start': 481.69, 'duration': 6.763}, {'end': 493.035, 'text': 'all you have to do is note down how many values are there in each set.', 'start': 488.453, 'duration': 4.582}, {'end': 498.297, 'text': 'so we have three values street, city and state and set up your structure accordingly.', 'start': 493.035, 'duration': 5.262}], 'summary': 'Unstack data by noting down values in each set and structuring accordingly.', 'duration': 49.55, 'max_score': 448.747, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw448747.jpg'}, {'end': 574.326, 'src': 'heatmap', 'start': 509.281, 'weight': 0.73, 'content': [{'end': 514.783, 'text': 'enter, go to city, say equal to and then select the city.', 'start': 509.281, 'duration': 5.502}, {'end': 520.044, 'text': 'go to state, select west virginia and then now all of these are there.', 'start': 514.783, 'duration': 5.261}, {'end': 524.285, 'text': 'you simply just drag this down.', 'start': 520.044, 'duration': 4.241}, {'end': 529.483, 'text': 'you will get everything filled like this', 'start': 524.285, 'duration': 5.198}, {'end': 538.654, 'text': 'So once this is there, you select all of this, Ctrl C, copy and then right click and paste them as values.', 'start': 530.084, 'duration': 8.57}, {'end': 546.162, 'text': 'So now what we did is we have taken the values and we have kind of staggered them here.', 'start': 539.234, 'duration': 6.928}, {'end': 549.286, 'text': "but there is no more formulas, it's just values.", 'start': 546.162, 'duration': 3.124}, {'end': 557.035, 'text': 'once this step is done, while keeping it selected, use our delete blank cells trick from the earlier.', 'start': 549.286, 'duration': 7.749}, {'end': 562.3, 'text': 'so press f5 special blanks and click ok.', 'start': 557.035, 'duration': 5.265}, {'end': 567.702, 'text': 'now, in the earlier trick we used control minus and then we said entire row.', 'start': 562.3, 'duration': 5.402}, {'end': 574.326, 'text': 'but this time we will say shift cells up, because if i delete entire row i will use, i will lose this data as well.', 'start': 567.702, 'duration': 6.624}], 'summary': 'Data manipulation and formatting: drag, copy, paste, and delete cells in excel.', 'duration': 65.045, 'max_score': 509.281, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw509281.jpg'}, {'end': 630.757, 'src': 'embed', 'start': 605.686, 'weight': 1, 'content': [{'end': 613.588, 'text': 'just select all of this data, go to data and you can use the remove duplicates option to remove duplicate combinations too.', 'start': 605.686, 'duration': 7.902}, {'end': 619.33, 'text': 'this is not something many people know, so let me demonstrate that select all of your data,', 'start': 613.588, 'duration': 5.742}, {'end': 624.591, 'text': 'click on remove duplicates and from here keep both options selected.', 'start': 619.33, 'duration': 5.261}, {'end': 630.757, 'text': "This means you're looking for a combination of name and department and click OK.", 'start': 625.251, 'duration': 5.506}], 'summary': 'Demonstrate removing duplicate combinations by selecting data and using remove duplicates option.', 'duration': 25.071, 'max_score': 605.686, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw605686.jpg'}, {'end': 728.123, 'src': 'heatmap', 'start': 682.645, 'weight': 2, 'content': [{'end': 690.851, 'text': 'now what we are asking excel to do is find all the cells that have yellow color as their fill color in them and then click on find all.', 'start': 682.645, 'duration': 8.206}, {'end': 695.644, 'text': 'This will identify all the six cells.', 'start': 692.461, 'duration': 3.183}, {'end': 700.909, 'text': 'So it will say six cells find or how many of our yellow color cells are there in your data.', 'start': 696.104, 'duration': 4.805}, {'end': 706.394, 'text': 'Now select the first one, hold down the shift key and select the last one.', 'start': 701.55, 'duration': 4.844}, {'end': 710.258, 'text': 'This way you are now able to select all the six cells.', 'start': 707.035, 'duration': 3.223}, {'end': 721.381, 'text': 'So, once you have selected, you can click on the Excel window and press control minus, which is used to delete cells,', 'start': 711.618, 'duration': 9.763}, {'end': 723.942, 'text': 'and simply say delete the entire row.', 'start': 721.381, 'duration': 2.561}, {'end': 728.123, 'text': 'Click OK and your yellow cells are gone.', 'start': 725.062, 'duration': 3.061}], 'summary': 'Identify and delete 6 yellow cells in excel data.', 'duration': 27.613, 'max_score': 682.645, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw682645.jpg'}, {'end': 809.528, 'src': 'heatmap', 'start': 786.131, 'weight': 4, 'content': [{'end': 794.578, 'text': 'So what flash will at this point is trying to do is it is thinking oh you want the number I will get you number and from there on everything.', 'start': 786.131, 'duration': 8.447}, {'end': 795.699, 'text': "So it's giving me that.", 'start': 794.638, 'duration': 1.061}, {'end': 803.544, 'text': 'So I can go to the third cell and then I can simply edit that criteria and once I teach that new behavior,', 'start': 796.239, 'duration': 7.305}, {'end': 806.206, 'text': 'Flash Fill will then go and extract what I want.', 'start': 803.544, 'duration': 2.662}, {'end': 809.528, 'text': 'So this is how I can extract numbers from text.', 'start': 806.726, 'duration': 2.802}], 'summary': 'Flash fill extracts numbers from text by learning new behavior.', 'duration': 23.397, 'max_score': 786.131, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw786131.jpg'}, {'end': 934.559, 'src': 'heatmap', 'start': 888.112, 'weight': 0.956, 'content': [{'end': 892.714, 'text': "so it's going to find all of that things and it will replace them with nothing.", 'start': 888.112, 'duration': 4.602}, {'end': 896.315, 'text': 'and when you say replace all, see the magic happen.', 'start': 892.714, 'duration': 3.601}, {'end': 901.358, 'text': 'it will instantly replace the entire full path and just leave the file name there.', 'start': 896.315, 'duration': 5.043}, {'end': 904.598, 'text': 'so this is what it is now from here.', 'start': 902.358, 'duration': 2.24}, {'end': 912.1, 'text': 'if you would just like to extract only the file name without the extension the extension is something that appears after the dot.', 'start': 904.598, 'duration': 7.502}, {'end': 914.02, 'text': 'you can again use the same technique.', 'start': 912.1, 'duration': 1.92}, {'end': 918.161, 'text': 'we can say i want to delete anything after a dot.', 'start': 914.02, 'duration': 4.141}, {'end': 927.423, 'text': 'so dot, star, replace r, and then that will remove the file name, the the dot thing as well, and then leave the file name.', 'start': 918.161, 'duration': 9.262}, {'end': 934.559, 'text': "Keep in mind that if your file name also has some dots in it, then it's going to mess up, but otherwise it will work beautifully.", 'start': 928.154, 'duration': 6.405}], 'summary': 'Text processing tool can remove file paths and extract file names using specific techniques.', 'duration': 46.447, 'max_score': 888.112, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw888112.jpg'}, {'end': 927.423, 'src': 'embed', 'start': 904.598, 'weight': 3, 'content': [{'end': 912.1, 'text': 'if you would just like to extract only the file name without the extension the extension is something that appears after the dot.', 'start': 904.598, 'duration': 7.502}, {'end': 914.02, 'text': 'you can again use the same technique.', 'start': 912.1, 'duration': 1.92}, {'end': 918.161, 'text': 'we can say i want to delete anything after a dot.', 'start': 914.02, 'duration': 4.141}, {'end': 927.423, 'text': 'so dot, star, replace r, and then that will remove the file name, the the dot thing as well, and then leave the file name.', 'start': 918.161, 'duration': 9.262}], 'summary': 'Extract file name without extension using dot and star replace method.', 'duration': 22.825, 'max_score': 904.598, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw904598.jpg'}, {'end': 1018.05, 'src': 'heatmap', 'start': 959.795, 'weight': 0, 'content': [{'end': 963.118, 'text': 'Here is a dead simple way to convert those into numbers.', 'start': 959.795, 'duration': 3.323}, {'end': 969.061, 'text': 'Just select any blank cell, copy that and select your values.', 'start': 963.738, 'duration': 5.323}, {'end': 976.444, 'text': 'Right click, paste special and from here just add the blank cell to these values.', 'start': 970.061, 'duration': 6.383}, {'end': 980.506, 'text': 'Now by definition a blank cell in Excel has zero value.', 'start': 976.904, 'duration': 3.602}, {'end': 988.17, 'text': 'So when you add zero to a text, Excel will see, oh you are trying to add zero, let me see if I can convert this into a number.', 'start': 980.846, 'duration': 7.324}, {'end': 993.412, 'text': "So it will convert and then it will add the zero, which won't change the value.", 'start': 989.249, 'duration': 4.163}, {'end': 998.735, 'text': 'But now you have your values converted into numbers instead of text.', 'start': 993.472, 'duration': 5.263}, {'end': 1000.276, 'text': 'So there you go.', 'start': 999.496, 'duration': 0.78}, {'end': 1005.52, 'text': 'The 10 powerful and easy ways to clean dirty data in Excel.', 'start': 1000.437, 'duration': 5.083}, {'end': 1007.481, 'text': 'Thank you so much for watching this video.', 'start': 1005.72, 'duration': 1.761}, {'end': 1008.582, 'text': 'I hope you enjoyed it.', 'start': 1007.722, 'duration': 0.86}, {'end': 1013.546, 'text': 'If you liked this video, please give it a thumbs up and share it with some of your friends.', 'start': 1008.943, 'duration': 4.603}, {'end': 1018.05, 'text': 'My name is Chandu and my mission is to make you awesome in Excel.', 'start': 1014.367, 'duration': 3.683}], 'summary': 'Chandu shares a simple technique to convert text into numbers in excel, part of 10 powerful ways to clean dirty data.', 'duration': 47.989, 'max_score': 959.795, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw959795.jpg'}], 'start': 448.747, 'title': 'Unstacking data and cleaning dirty data in excel', 'summary': 'Explains how to unstack data by rearranging values into a proper format and introduces 10 powerful ways to clean dirty data in excel, including removing duplicates, yellow cells, extracting numbers from text, and extracting file names from full paths.', 'chapters': [{'end': 529.483, 'start': 448.747, 'title': 'Unstacking data', 'summary': 'Explains how to unstack data by rearranging values in different cells into a proper format, using a simple method of noting down the number of values and setting up the structure accordingly, demonstrated with an example of rearranging street address, city, and state into separate cells.', 'duration': 80.736, 'highlights': ['By noting down the number of values in each set and setting up the structure accordingly, the data can be unstacked into a proper format, as demonstrated by rearranging street address, city, and state into separate cells.', 'The process of unstacking data can be achieved using a simple method without the need for complex formulas, as shown in the example of rearranging the stacked data of street address, city, and state into a proper format.', 'Demonstrated example involves unstacking data by rearranging street address, city, and state into separate cells, showcasing the ease of the process without the requirement of complex formulas.']}, {'end': 1020.352, 'start': 530.084, 'title': '10 ways to clean dirty data in excel', 'summary': 'Introduces 10 powerful and easy ways to clean dirty data in excel, including removing duplicate combinations, removing yellow cells, extracting numbers from text, and extracting file names from full paths.', 'duration': 490.268, 'highlights': ["Remove duplicate combinations: Demonstrates using the 'remove duplicates' option in Excel to remove duplicate combinations of data, keeping both options selected and clicking OK to remove all duplicate combinations.", 'Remove yellow cells: Explains the process of finding and deleting yellow highlighted cells in Excel by using the find function to identify and delete cells with the yellow fill color, effectively removing the yellow cells from the data.', "Extract numbers from text: Introduces the use of Flash Fill in Excel to extract numbers from text, demonstrating how Excel guesses the user's criteria and allows for editing to extract the desired numbers from the text.", 'File name from full path: Describes the technique of using find and replace in Excel to extract file names from full paths, first by finding and replacing the path with nothing, and then deleting anything after a dot to extract only the file name.', 'Numbers formatted as text: Demonstrates a simple method to convert text values into numbers in Excel by adding a blank cell to the values using paste special, which allows Excel to convert the text values into numbers.']}], 'duration': 571.605, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/SNVjndgWBlw/pics/SNVjndgWBlw448747.jpg', 'highlights': ['Introduces 10 powerful ways to clean dirty data in excel, including removing duplicates, yellow cells, extracting numbers from text, and extracting file names from full paths.', "Demonstrates using the 'remove duplicates' option in Excel to remove duplicate combinations of data, keeping both options selected and clicking OK to remove all duplicate combinations.", 'Explains the process of finding and deleting yellow highlighted cells in Excel by using the find function to identify and delete cells with the yellow fill color, effectively removing the yellow cells from the data.', 'Describes the technique of using find and replace in Excel to extract file names from full paths, first by finding and replacing the path with nothing, and then deleting anything after a dot to extract only the file name.', "Introduces the use of Flash Fill in Excel to extract numbers from text, demonstrating how Excel guesses the user's criteria and allows for editing to extract the desired numbers from the text.", 'Demonstrates a simple method to convert text values into numbers in Excel by adding a blank cell to the values using paste special, which allows Excel to convert the text values into numbers.', 'By noting down the number of values in each set and setting up the structure accordingly, the data can be unstacked into a proper format, as demonstrated by rearranging street address, city, and state into separate cells.', 'The process of unstacking data can be achieved using a simple method without the need for complex formulas, as shown in the example of rearranging the stacked data of street address, city, and state into a proper format.', 'Demonstrated example involves unstacking data by rearranging street address, city, and state into separate cells, showcasing the ease of the process without the requirement of complex formulas.']}], 'highlights': ['Using Flash Fill to automatically extract first names by typing a few examples and letting Excel suggest the remaining names, which can be undone if necessary.', 'Illustrating how the same can be done for last names, with the process automatically suggesting the last name as well.', 'Using flash fill for pattern recognition Excel can use flash fill to recognize patterns and provide suggestions for data manipulation, improving efficiency in data processing.', 'Converting numbers to percentages By using a simple technique of dividing by 100 or using multiplication, Excel can efficiently convert numbers to percentages, streamlining the process of data transformation.', 'Removing blank rows Excel allows for the removal of blank rows in a dataset, enhancing data cleanliness and organization for analysis and presentation purposes.', "Removing Blank Cells By using the 'Go To Special' feature and 'Control Minus' shortcut, Excel can quickly remove all blank cells in the selected range, saving time and effort in data cleaning.", "Finding Duplicates The process of identifying duplicate values between two lists by using the 'Conditional Formatting' feature, which efficiently highlights the duplicate invoice numbers in different colors.", "Using Trim Function for Inconsistent Spacing Demonstrating the use of the 'Trim' function to efficiently remove extra spaces from the beginning and end of values, as well as any inconsistent spacing within the data, providing a quick solution for cleaning up inconsistent data.", "Cleaning Up Formats The 'Clear Formats' option in Excel's home ribbon provides a simple way to instantly tidy up the data and reset it to its original raw formatting, offering a time-saving solution for data formatting issues.", 'Introduces 10 powerful ways to clean dirty data in excel, including removing duplicates, yellow cells, extracting numbers from text, and extracting file names from full paths.', "Demonstrates using the 'remove duplicates' option in Excel to remove duplicate combinations of data, keeping both options selected and clicking OK to remove all duplicate combinations.", 'Explains the process of finding and deleting yellow highlighted cells in Excel by using the find function to identify and delete cells with the yellow fill color, effectively removing the yellow cells from the data.', 'Describes the technique of using find and replace in Excel to extract file names from full paths, first by finding and replacing the path with nothing, and then deleting anything after a dot to extract only the file name.', "Introduces the use of Flash Fill in Excel to extract numbers from text, demonstrating how Excel guesses the user's criteria and allows for editing to extract the desired numbers from the text.", 'Demonstrates a simple method to convert text values into numbers in Excel by adding a blank cell to the values using paste special, which allows Excel to convert the text values into numbers.', 'By noting down the number of values in each set and setting up the structure accordingly, the data can be unstacked into a proper format, as demonstrated by rearranging street address, city, and state into separate cells.', 'The process of unstacking data can be achieved using a simple method without the need for complex formulas, as shown in the example of rearranging the stacked data of street address, city, and state into a proper format.', 'Demonstrated example involves unstacking data by rearranging street address, city, and state into separate cells, showcasing the ease of the process without the requirement of complex formulas.']}