Tip:
Highlight text to annotate it
X
The LEFT, RIGHT and MID functions allow you to cut up the contents of a cell to use for
other purposes.
In this segment you will learn how to use each of these functions.
In this example we have a list of Account numbers and what we’d like to do is split
them up, because if you look at the account number, you’ll see that the first three
letters appear to indicate the City, the next five numbers is a Client number of sorts,
and the last three numbers is the Department. And we’d like to split it up so that perhaps
you can compare it to other reports which are not necessarily in this format.
In order to do this, we can make use of the LEFT, RIGHT and MID functions in Excel. So
for example City, the city code seems to be on the left hand side of the text, so we’re
going to make use of the LEFT function,
so we click on the Function Wizard and find LEFT,
we say ok, and what you’re asked is, it asks you for
the reference to the cell that contains the text. So that would be there
and then is says tell me how many characters from the LEFT you want to be extracted, and
in this case you can see it appears to be three
and if you type in a 3 and say ok,
only that part of the cell will be extracted. For the department number you can see that
it appears to be from the RIGHT, and again its three characters,
so we can click in this cell activate the Function Wizard
find the RIGHT function click ok,
again it asks for a reference to the text, we click on that cell
and it asks for the number of characters from the RIGHT that it must extract and as you
can see it is three as we don’t want the dashes
so we say 3 and when we click ok
you’ll see that you’ve now extracted just the last three characters from the text.
To extract characters from the middle of a section of text you need to use the MID function,
so you click on the cell activate the Function Wizard
and find the MID function, click ok
again you are asked for a reference to the cell that contains the text, so its over here,
this criteria says what is the position of the first character you want to extract, and
in this case you’ll see we don’t want those three, and we don’t want the dash,
so it’s the fifth character so we put a 5 in here
and the number of characters is how many you want to extract from that fifth position and
in that case it is 1,2,3,4,5, we put in 5 here,
and when we click ok you’ll see it extracts only the numbers
in the MIDDLE of the text You are now in a position to
highlight these three and copy it all the way down
and automatically you have split these codes into their components being
the City Client number
And Department and you are now able to perhaps sort for more
reliable information.