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 continue looking at text functions and show you how to use VALUE, DOLLAR, TRIM, and SUBSTITUTE functions.

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 ;-)

Welcome back to our course on Excel 2016 Advanced.
In this section we’re going to continue looking at Text functions and I want to start by reviewing one or two aspects of what we did in the preceding section.

We’ve started to split up the employee data that we had into five fields. The first field is here in column E and the balance of the information about each employee is in column F. Now in a moment I am going to split what’s in column F further and finish up in a situation where we actually do have five fields per employee. But before we do I’d like to review an aspect of what we’ve been doing so far. And in order to do that I’m going to look at E2.

Now in E2 we have a formula, =LEFT(B2,C2-1). And B2 is the text string, Juarez, Jose etcetera, all the information. And C2 is the value 14. C2-1 is 13. Now if I click on C2 I can see that it is actually itself a formula. It’s Find and it’s Find semicolon in the string B2.

Now if I take that Find, if I copy that to the clipboard from the entry bar and now let me select E2, what I can do is to replace C2 with the function that I just copied to the clipboard. So what I’ve done is to replace C2 with Find semicolon in B2. So what I’ve done there is to eliminate any reference to column C from column E. Now I realize I’ve only done it in the case of row 2 but I could of course fill that down to cover the other rows as well. Let’s just check it’s correct. Of course it is.

Note also that within that formula, =LEFT(B2,) and then Find which is embedded within there I have two references to B2. Now what is B2? B2 is proper A2. What I could do, let me copy proper A2 to the clipboard. Now I’m going to go into E2 and wherever I’ve got B2 I’m going to put proper A2.

Now if I continued to do this and if I did the same to the formulae in column F I could eventually get to the point where I have no references to columns B, C or D. And I could in fact find the columns B, C and D are completely unnecessary because what I’ve done there is to create formulae with nested functions and each function within its arguments may have references to other functions with arguments and so on.

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