Join Dennis Taylor for an in-depth discussion in this video Replace data with the Replace command and SUBSTITUTE function, part of Excel 2016: Cleaning Up Your Data.
- [Instructor] We're looking at the worksheet called,…Substitute in our Chapter 2 file,…and we need to make a change in Column B.…Company's restructuring it's HR categories,…it eventually will get rid of hourly.…It wants to change Half-Time to Part-Time.…We want to change all these entries,…wherever we see Half-Time, change 'em to Part-Time.…Easiest way to do this, select Column B,…on the Home tab, the far right editing group,…go to Find & Select, Replace.…We simply want to replace that phrase Half-Time,…it's got a dash in it, tab, Part-Time,…and we went to Replace all of them…so it will tell us how many it has replaced,…in this case 96.…
So we see that's happened over there in cell B6,…it's also down here in B13,…and of course, a lot of others too.…Now, we can also use this feature in the different way.…We've got a code number, a part number in Column H here,…and sometimes these are positional,…sometimes actual numbers mean something…regardless of where they are.…We want to change these and simply get rid of the eights…
Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Moving and inserting rows and columns of data with a simple drag
- Replacing data at the character level
- Converting dates with text functions
- Converting text data to values
- Using the CONCAT and TEXTJOIN functions to combine data
- Splitting data into columns via the Text to Columns feature
- Using Flash Fill for faster combining and splitting
- Checking and correcting spelling mistakes