How to extract characters from a cell using the LEFT, RIGHT and MID functions.
In this segment you will learn how to use the left right middle excel 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,
For the department number you can see that it appears to be from the RIGHT, and again its three characters,
To extract characters from the middle of a section of text you need to use the MID function,
You are now in a position to
and you are now able to perhaps sort for more reliable information.
Advanced Excel Course – June 2013 in South Africa Our next advanced excel course is running during June 2013 in Johannesburg. During the week of the 10th June we offer the following options. You can either attend the full 5 … Continue reading
A very popular chart is the waterfall chart which shows the impact of various items on a total e.g. the various components that explain the move from budgeted profit to actual profit. A common way to build this is using … Continue reading
Just a quick note to say your blog is brilliant. Full of useful bits of info. After your courses, I am by far the best modeller in our office, and we regularly get comments from our business partners on the quality of our models. Thanks for all the help,– Mike