Get 20-hours of Excel 2016 training here ► https://www.simonsezit.com/course-category/excel/

During this Microsoft Excel 2016 advanced training tutorial video, we will demonstrate an exercise using a range of text functions such as the PROPER, FIND, LEN, LEFT, and RIGHT functions in order to tidy up and reveal the structure of data.

Check out some of our other training on YouTube:

Excel 2016 advanced training: https://www.youtube.com/playlist?list=PLzj7TwUeMQ3h5ZVxzp_a4UHRQBtXXgnxf

Excel 2016 beginner training: https://www.youtube.com/playlist?list=PLzj7TwUeMQ3hFUdlWMkTLZL-Fr3oRGN35

VBA for Excel tutorials: https://www.youtube.com/playlist?list=PLzj7TwUeMQ3hWRi0mgxdyWkT0QaYKuBGZ

Project 2016 beginner training: https://www.youtube.com/playlist?list=PLzj7TwUeMQ3g_ABHdUU7RoGJJm-YFr4_Y

Visio 2013 for beginners: https://www.youtube.com/playlist?list=PLzj7TwUeMQ3jpAwkTGD6hGNxF8ML_FfsF

SharePoint 2013 training: https://www.youtube.com/playlist?list=PLzj7TwUeMQ3jloGuvewOe0Iv1EE2POKge

Stay in touch:
SimonSezIT.com: https://www.SimonSezIT.com/
StreamSkill.com: https://StreamSkill.com/
YouTube Channel: https://www.youtube.com/user/simonsezittraining
Facebook: https://www.facebook.com/SimonSezIT/

If you enjoyed the video, please give a "thumbs up" and subscribe to the channel ;-)

Hello again and welcome back to our course on Excel 2016 Advanced.

In this section we’re going to start to look at Text functions. But instead of going through the Text functions one at a time and demonstrating what each of them does I’m going to turn everything on the head this time and do a very specific exercise using a range of Text functions.

I’m going to assume that you have used basic Text functions. So for example, I’m going to assume that you’re familiar with the use of Concatenation either using the ampersand symbol or the Concat function. But some of the functions that I’m going to be using in this section will be some of the ones that you may well not have used before.

Now the basis of the exercise that I’m going to do is that I’ve been given a long list of employee details and for each employee I’ve got five pieces of information. And in a text file I’ve got about 20 employees information just as a sort of test sample. And I’m going to see if I can use Excel Text functions to basically tidy this data up and to reveal the structure in the data.

I’ve opened the data here in Notepad. And if you look at the very first row in Notepad there is an employee name which is Juarez, Jose. There is a number, 41297. I believe that is the start date for the employee. And in fact if you look at the next row, Abraham, Johnny, you’ll see there’s a date there, March 30th 2013. So it’s probably a start date. And carrying on with Jose’s details, the next figure is Jose’s salary, then what I believe is the department that Jose works in, the Admin department, and then finally Jose’s job title.

Now those five pieces of information are separated by semicolons and what I want to do is to use Text functions to split that data up for each employee. And I want to finish up with the data for this test sample neatly structured in a workbook.

Now one thing that is an obvious possibility is that you could just say well why mess around with Text functions? Why not just type it all in using this Notepad file? As I said before, this is basically a sample of about 20 people. Now let’s assume I’ve got thousands of people and what I want to do is to put together a set of formulae and functions that will work as well as possible for the thousands of employees for whom I have this data.
So the first thing I’m going to do is to close this text file and open up in Excel.

Sorry, we couldn't fit the entire video transcription here since YouTube only allows 5000 characters.