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. So I could finish up in a situation
where ultimately each of my five columns with the individual fields in is really just a
formula which only refers to what’s in column A. So I would find that all of those other
columns that I’ve introduced for the purposes of developing this system step-by-step I could
finish up in a situation where I can just do the whole thing with those five formulae
and no other sort of intermediate columns. Now it’s very much a matter of personal
preference which way you like to do things. Certainly when I’m showing people how to
do these things for the first time I always break it down into these steps and I very
often leave the columns there, columns such as B and C and D, although I may hide them
as well. But if you can understand the use of formulae and functions and if long formulae
such as the one as you can see there in E2 don’t cause you a problem, you can look
at that and you can work out what it’s doing, then clearly not having these intermediate
columns can be an advantage. You only have if you like the five columns with the answers
in. If you find all of this quite confusing, and bear in mind that when we get to column
F we’ve got yet more things to do to split these up and change formats and so on, if
you find it’s all quite confusing it’s probably best to stick with the approach I’m
using here of building the whole thing up column by column. But as I say, it is a matter
of personal preference and for the moment I’m going to stick with this approach of
using these intermediate working columns throughout and just hiding them.
So I’ve put E2 back to the way that it was. So the formula in there is LEFT(B2,C2-1) and
what I’m now going to do is to develop column by column a breakdown into the four remaining
fields of the contents of column F. Now I really don’t think there’s a lot of point
in you watching while I do this. In fact what will be a very good idea here is I’m going
to save this version of Text Functions 01 and if you want to try doing the next bit
yourself, this isn’t one of the exercises but you might like to try this anyway. We’ll
then pick up immediately after this with Text Functions 02. And in Text Functions 02 you’ll
see the breakdown that I’ve done. So we’re now looking at Text Functions 02
and what I’ve done here is to continue to split up the information about each of the
employees and I’ve put headers on the five columns that have the five pieces of information
about each employee. So I’ve got Name in column E, Start Date in I, Salary in M, Department
in Q, Position and R. You may well have used a different approach to splitting these up.
You may of course have made some really quite complex formulae with functions embedded within
functions and so on. Perhaps even the position at the end there in my column R instead of
being a formula like the one that I’ve got RIGHT(N2,P2-O2), you may have that all done
in terms of column A. And if that works you’ve done a pretty good job really.
So what I’m going to do now is to hide the working columns, bring up column A, and talk
about some of the other issues with the work that we’ve done here.
Now if you consider our starting point you may think we’ve made pretty good progress
here because we’ve split up each of those pieces of information into five columns. It
all looks fine. But in fact there are still quite a few things that need attention. And
the first thing to note is that in the case of start date and salary both of those are
coming through as text. You can normally tell that by the fact that they’re left justified
in the cell. So starting with start date it’s clear that
Excel 2016 hasn’t realized that it’s a date field that we’re dealing with and hasn’t
realized that these are numbers. Now there are various ways of dealing with this. One
of them if I click in the formula there is =LEFT. If I want to convert text to a number
I can use the Value function. And if I click on that and say =VALUE and keep my LEFT(F2,G2-1)
embedded in there and tick it, it becomes a number. And I can then fill that down to
all of them and then I can format that particular column as a date column. So let me select
the whole column and there we are. That looks much better.
Now it looks as though the contents of the salary column are also text so let’s turn
those text values into numbers. Now in order to format that as a currency, and I’m going
to use U.S. dollars, I can do it pretty much the same way that I did the start date column
by selecting the whole column and formatting the cells. There is an alternative here. There
is a dollar function that you can use. Again it’s one worth looking up. But if I said
here Dollar, it has two arguments. The value you want to turn into a dollar amount. This
needs to be a number. And then the second argument is the number of decimal places.
So if I say comma zero at the end, with zero decimal places, it will turn it into a dollar
amount. But note that when I do that I finish up with a dollar amount as a text value. So
what I’m going to do is to actually undo that one and I’m going to fill down the
value function and I’m going to format the column as currency with zero decimal places.
Okay. Now something that you may already have noticed
is that the names of these employees in many cases have additional trailing spaces after
them. For instance, if you look at cell A2, Juarez, Jose, if you remember the position
of the semicolon character is position 14 in the string, which implies there are 13
characters before it. But if you add up the characters in Juarez, comma, space, Jose that
only comes to 12. The 13th character is the space that you can see between the E in Jose
and the semicolon character. Now generally speaking trailing leading spaces, double spaces,
etcetera in many situations can cause a problem but there is an Excel function, the Trim function
that trims leading and trailing spaces and removes multiple spaces. So where there is
supposed to be a space within a string reduces it to single space. And you could apply that
here, for example, in cell E2. Pretty straightforward. We’ll change it from LEFT(B2,C2-1) to TRIM.
Now of course there won’t be any visible effect on what you can see in column E but
those trailing spaces will all have been removed once I fill the use of this function down.
Again, it’s an important one to bear in mind if trailing or leading spaces or multiple
spaces are likely to cause you a problem. And I’d like to look at one more Text function
in this section. If you look at the Department column, department names include Mktg for
Marketing, R&D for Research and Development. Let’s suppose that some of the data that
you’re processing has abbreviations such as Mktg that you’d like to make into Marketing.
Now one way that you can do this is to use the Substitute function. Now I’m going to
demonstrate the Substitution function here. It’s not necessarily a particularly good
way of doing this here for reasons that will become apparent in just a moment. Q2, the
formula is the left most O2-1 characters of the string N2. Let’s introduce the Substitute
function. Now notice what it says there about the Substitute
function. Replaces existing text with new text in a text string. So what we’re going
to look at here is the text, the old text and the new text. And then as an option we
can specify which instance of that old text we want to replace. Now the text I’m replacing
in is LEFT(N2,O2-1) so that’s fine. Then what I’ve got is the old text. Well I’m
looking for Mktg. You may think it rather strange that I’m looking for that in the
name of Jose’s department but you’ll see what I’m doing in just a moment I hope.
And I want to replace it with. Now of course in this particular case that has no effect
whatsoever. But if I fill down, just keep an eye on row 8. You see that in the one instance
where it found Mktg it did the substitution. Now of course that’s absolutely fine but
it only did one substitution of marketing. And if I wanted to do a substitution for R&D
as well I’d actually have to use the Substitute function twice within each formula in column
Q and that’s really starting to get rather messy and there is a much better way of doing
that that we’re going to look at later on in the course. But be aware of the Substitute
function because that can be very useful as well.
Now the example that I’ve just shown you where I’ve replaced Mktg by Marketing you
would probably not do that that way if you only had one instance of something you’d
more or less treat it as correcting an error. So for instance something like in row 7 the
name of Terryy Boateng which is clearly misspelt with two Y’s at the end of Terry. That’s
an error. You wouldn’t set up a Substitute to replace all instance of Terry with two
Y’s with Terry with one Y. So you will sometimes need to make decisions about what to automate
and what to not automate and to do manually. There are one or two other Text functions
that we’re going to come across later on in the course. We’ll cover those. I suggest
you do go through the Help on Text functions. That’s it for this section. Please join
me in the next one.